[TYPO3-dam] Help improve a DAM query

[Lorenz Ulrich]

Hi everyone

Maybe there is someone in this list who can help us speed up a DAM SQL 
Query. See also this [1] issue in the DAM bugtracker.

The following query selects 20 records from the DAM table (set 
tx_dam_pid IN 19 to your media folder PID) with a certain category (set 
FIND_IN_SET(10,GROUP... to the uid of the DAM category) and orders it by 
the title field.

SELECT DISTINCT tx_dam.title, tx_dam.uid, tx_dam.pid, tx_dam.media_type, 
tx_dam.tstamp, tx_dam.crdate, tx_dam.cruser_id, tx_dam.deleted, 
tx_dam.sys_language_uid, tx_dam.l18n_parent, tx_dam.hidden, 
tx_dam.starttime, tx_dam.endtime, tx_dam.fe_group, tx_dam.file_name, 
tx_dam.file_dl_name, tx_dam.file_path, tx_dam.file_size, 
tx_dam.file_type, tx_dam.file_ctime, tx_dam.file_mtime, 
tx_dam.file_hash, tx_dam.file_mime_type, tx_dam.file_mime_subtype, 
tx_dam.file_status, tx_dam.index_type, tx_dam.parent_id FROM tx_dam LEFT 
JOIN tx_dam_mm_cat ON tx_dam.uid=tx_dam_mm_cat.uid_local LEFT JOIN 
tx_dam_cat ON tx_dam_mm_cat.uid_foreign = tx_dam_cat.uid WHERE 1=1 AND 
tx_dam.pid IN (19) AND tx_dam.sys_language_uid IN (0,-1) AND 
tx_dam.file_status!=255 AND tx_dam.deleted=0 GROUP BY tx_dam.uid HAVING 
1 AND (FIND_IN_SET(10,GROUP_CONCAT(tx_dam_cat.uid))) ORDER BY 
tx_dam.title LIMIT 20

It is very slow (my server: 0.5 sec) because there's a lot of data that 
needs to be copied to a temporary table.

The following, improved query is much faster (my server: 0.015 sec) but 
not DBAL compatible:

SELECT * FROM (SELECT DISTINCT tx_dam.title, tx_dam.uid, tx_dam.pid, 
tx_dam.media_type, tx_dam.tstamp, tx_dam.crdate, tx_dam.cruser_id, 
tx_dam.deleted, tx_dam.sys_language_uid, tx_dam.l18n_parent, 
tx_dam.hidden, tx_dam.starttime, tx_dam.endtime, tx_dam.fe_group, 
tx_dam.file_name, tx_dam.file_dl_name, tx_dam.file_path, 
tx_dam.file_size, tx_dam.file_type, tx_dam.file_ctime, 
tx_dam.file_mtime, tx_dam.file_hash, tx_dam.file_mime_type, 
tx_dam.file_mime_subtype, tx_dam.file_status, tx_dam.index_type, 
tx_dam.parent_id FROM tx_dam LEFT JOIN tx_dam_mm_cat ON 
tx_dam.uid=tx_dam_mm_cat.uid_local LEFT JOIN tx_dam_cat ON 
tx_dam_mm_cat.uid_foreign = tx_dam_cat.uid WHERE 1=1 AND tx_dam.pid IN 
(19) AND tx_dam.sys_language_uid IN (0,-1) AND tx_dam.file_status!=255 
AND tx_dam.deleted=0 GROUP BY tx_dam.uid HAVING 1 AND 
(FIND_IN_SET(10,GROUP_CONCAT(tx_dam_cat.uid))) LIMIT 20 ) as results 
ORDER BY results.title

Is there a way to improve the query without losing DBAL compatibility?


Thanks in advance and best regards,


Lorenz

[1] http://bugs.typo3.org/view.php?id=17867