@Temus
Hi, first sorry that I’m writing in english, it is easier for me. You can answer in Russian or English, I’m reading well in both languages, but the writing is different story
I have a gallery with about 100 folders under the main. The load of page with [!easy2!] call was terribly slow. I localized the problem in get_folder_img function in the snippet.
The SQL it creates for 1 folder, which looks like
SELECT DISTINCT F.* FROM modx_easy2_dirs A, modx_easy2_dirs B, modx_easy2_files F
WHERE
(B.cat_id=102 AND
A.cat_left >= B.cat_left AND
A.cat_right <= B.cat_right AND
A.cat_level > B.cat_level AND
A.cat_visible = 1 AND
F.dir_id = A.cat_id) OR
F.dir_id = 102
ORDER BY A.cat_level ASC, F.id DESC ;
(in this case 102 is the ID of the first folder)
needs about 10 seconds to execute.
You can imagine the time needed to execute this SQL for 100 folders.
Changing the SQL to
SELECT DISTINCT F.* FROM modx_easy2_files F WHERE F.dir_id = 102 ORDER BY F.id DESC ;
reduces the time to 0.01 second
I understand that the changed SQL will be problematic for a folder with subfolders only, that’s why I would like to ask for your help for optimizing the SQL without loosing functionality.
As a first step, short explanation of cat_left and cat_right columns will be helpful.
Best Regards,
Dimitar
-----------------------------
P.S. Proposal for proper SQL
I’ve spend some more time and I have SQL, which in the case I managed to test is doing the same job and is very fast:
'SELECT F.* FROM '.$modx->db->config['table_prefix'].'easy2_files F WHERE F.dir_id in (SELECT A.cat_id FROM '
.$modx->db->config['table_prefix'].'easy2_dirs A, '.$modx->db->config['table_prefix'].'easy2_dirs B WHERE (B.cat_id='
.$gid.' AND A.cat_left >= B.cat_left AND A.cat_right <= B.cat_right AND A.cat_level >= B.cat_level AND A.cat_visible = 1) ORDER BY A.cat_level ASC ) ORDER BY F.id DESC ';
Could you please verify it and comment
