Slow Query On loading Categories Page (Root)

[rene@ntoni.com]

Hi
Setting    Value

PHP Built On    Linux webserver
Database Version    5.1.73-log
PHP Version    5.6.11
Web Server    Apache/2.2.15 (CentOS)
WebServer to PHP Interface    apache2handler
Joomla! Version    Joomla! 3.4.3 Stable [ Ember ] 2-July-2015 16:00 GMT
Joomla! Platform Version    Joomla Platform 13.1.0 Stable [ Curiosity ] 24-Apr-2013 00:00 GMT


I am busy uploading 300 000 documents using cron. i've used the previous version with a few issues, but I was able to sort it out -
1. make the cron upload faster
2. create an ajax jstree viewer

I updated to latest joomal and jdowloads :
Issues:
1. Slow query generated in components/com_jdownloads/helpers/categories/

Time: 150805 15:39:39
# User@Host: vzradm[vzradm] @ localhost []
# Query_time: 22.718012  Lock_time: 0.000163 Rows_sent: 5907  Rows_examined: 18027704
SET timestamp=1438781979;
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 vzr01_jdownloads_categories as c
LEFT JOIN `vzr01_jdownloads_files` AS files ON files.`cat_id` = c.id AND files.published = 1
LEFT JOIN vzr01_users AS u on u.id = files.created_id
LEFT JOIN vzr01_users AS u2 on u2.id = files.modified_id
LEFT JOIN vzr01_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,3,4,5)
WHERE c.access IN (1,1,2,3,4,5) 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;

2. Categories page and treeview takes too long to load, since traversal throught ALL the tree nodes.

I've written a JStree mod/component for the previous version where i loaded the tree items asyncronously and which worked very well.
It seems that you build the full tree when opening the category root page and the tree view and populate the screen with things the user might never use.

Im not sure you would be able to help me sort out the issues, but i thought that it would be nice if i dont have to mod jdownloads everytime i do an update.

Also, just to inform users who would like to use your system, to not bother spending about 30 hours setting up the system and importing 300 000 documents to just find out that the first page takes about 30-40 seconds to load. The previous version could, but not this one.

Thank you for a great system though (obviously if you have less that 300000 documents!)
René

[rene@ntoni.com]

Hi Colin

Sorry to be a pain, but i need to decide what to do. I really want to continue the use of jdownloads, since it does everything i need it to do so far and also - i have a fully working system set  up.

But i would have to decide to
1. Use the current system and hope for faster page loads in later updates
2. change the tree/category views to use ajax and just reaply changes to your updates later (painful)
3. Use another system (redo everything I've allready done)

Any advice from you will be appreciated.

Regards,

[Arno]

Hi Rene,
can you give me more information what exactly you have modified on the old jD series?
Or not better, what exasctly i can do to get it faster (not only in your case)? 

I have used in the categories helpers class (nearly) the same technics as Joomla self in his source code.

Edit:
have you tried to deactivate the category select box in the header (in jD configuration)?
How many categories exists?

[rene@ntoni.com]

Hi Arno

Thank you for your reply:

1. The category select box is not activated
2. I have 5907 categories

This is the Explain query and in SQLYog.
1   "SIMPLE"   "c"   "ALL"   "idx_access,ix_published"   \N   \N   \N   5907   "Using where; Using temporary; Using filesort"
1   "SIMPLE"   "files"   "ref"   "idx_published,idx_cat_id"   "idx_cat_id"   "5"   "xxx.c.id,const"   65   ""
1   "SIMPLE"   "u"   "eq_ref"   "PRIMARY"   "PRIMARY"   "4"   "xxx.files.created_id"   1   ""
1   "SIMPLE"   "u2"   "eq_ref"   "PRIMARY"   "PRIMARY"   "4"   "xxx.files.modified_id"   1   ""
1   "SIMPLE"   "menu"   "ALL"   "ix_access"   \N   \N   \N   117   ""


If i run this query it takes :52728ms to list 5907 Categories
If i remove the link to the menu table, the query takes 78ms to list 5907 Categories

Also, I only modified the "cron job" and the TreeView on the previous system  - the category page loaded perfectly fast.

for the Treeview i only showed the needed nodes and fetched subnodes when clicked on a parent. I also called the "content section" (category listing on the right) using ajax so that i did not have to reload the tree the whole time.

I can send you links and passwords to both front ends for you to check - I think its great to have your system manage so many documents - and i know that is not this issue is not something you can be prepared for unless tested on a big database.

Thank you very much Arno.

[Arno]

Hm... to get also the menu link is an important part. 
I will see what i can do.