jDownloads frontend is very slow

[Makulia]

Hello, Arno!

I have faced very poor jDownloads frontend performance.

There is 3,741 files and 754 categories. I have enabled joomla debug console and fetched some data.
There is over 2262 sql queris on jdownloads root page with 9 main categories page loads about 11 sec or more.
21 repeated sql queris.

I have tried different jdownloads version, including latest one on Joomla 3.4.5 and php 5.6.
I guess that it is not a big amount of data, because I have joomgallery with more then 30k of photos and it run's smoothly.

May be you can help find this bottle neck!

Some very slow places:
702.63 ms

Code Select Expand



SELECT c.*, 
  CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(':', c.id, c.alias) ELSE c.id END as slug,COUNT(files.`file_id`) AS numitems,u.name AS creator,u2.name AS modifier,menu.id AS menu_itemid




  FROM kport_jdownloads_categories as c




  LEFT JOIN `kport_jdownloads_files` AS files 
  ON files.`cat_id` = c.id 
  AND files.published = 1




  LEFT JOIN kport_users AS u 
  on u.id = files.created_id




  LEFT JOIN kport_users AS u2 
  on u2.id = files.modified_id




  LEFT JOIN kport_menu AS menu 
  on menu.link LIKE CONCAT('index.php?option=com_jdownloads&view=category&catid=',c.id,'%') 
  AND menu.published = 1 
  AND menu.access IN (1,1,2,7,8,11,21,22,23,24,25,26,28,29,32,34)




  WHERE c.access IN (1,1,2,7,8,11,21,22,23,24,25,26,28,29,32,34) 
  AND c.published = 1




  GROUP BY c.id, c.cat_dir, c.cat_dir_parent, c.parent_id, c.lft, c.rgt, c.level, c.title, c.alias, c.description, c.pic, c.access, c.metakey, c.metadesc, c.robots,
                       c.created_user_id, c.created_time, c.modified_user_id, c.modified_time, c.language, c.notes, c.views, c.params, c.password, c.password_md5, c.ordering, c.published, c.checked_out, c.checked_out_time, c.asset_id




  ORDER BY c.title ASC



[Arno]

Hi,

There is 3,741 files and 754 categories. I have enabled joomla debug console and fetched some data.
There is over 2262 sql queris on jdownloads root page with 9 main categories page loads about 11 sec or more.

I know some users which have more Downloads and categories as you. 
But nobody has posted a problem like yours. So it can be that your server settings are not the best.

Please send me a link to the problem via PM.

You should also deactivate the 'Categories select box' in the header.

I think you know that a database handling is a very tricky and not so easy. And a slowly connection must not be really a 'bug'. Because the results are correct. But maybe it is possible to optimize the queries.

[Makulia]

Thank you, Arno!

Categories select box is already deactivated.
Yes, I think, that problem lays in queries. Web server seems pretty solid: 8 cores Xeon with 24GB Ram, SSD, Ubuntu 15.10 on board. Apache 2.4+php 5.6+mysql 5.6. I have noticed that while i am requesting my jDownloads root page, CPU load rises to 100% due to mysql process.

I do not like to compare jDownloads with other products, but only to clarify that it is not DB server issue - Joomgallery with 30k photos and around 270 categories performs with no problems (debugged and can confirm that number of queries and their execution time is much less). Basic idea is that both product is kind of "directory" software with category nesting option, able to handle big amount of data.

I would be awesome if we could improve performance of jDownloads! If you need any information from me, just let me know.

Disabled:
Downloads logging
Directory monitoring

I have sent PM to you.
Any help will be more then wellcome. It you need additional info, just let me know.

Related topics:
http://www.jdownloads.com/index.php/support-forum.html?topic=7671.0;nowap
http://www.jdownloads.com/index.php/support-forum.html?topic=8365.0;nowap