[TYPO3-dam-devel] RFC #9830: Not responing queries using category selections

[Peter Kuehn [wmdb]]

Hi DAM team,

This is an SVN patch request.

Type: Bugfix

Bugtracker references:
http://bugs.typo3.org/view.php?id=9830

Branches:
trunk


Problem:
If an editor uses the += links in the category tree to select assets 
based on assigned categories tx_dam_mm_cat is left joined one time per 
selected category, fx.:

SELECT COUNT(DISTINCT tx_dam.uid) as count
FROM tx_dam
LEFT JOIN tx_dam_mm_cat AS tx_dam_mm_cat_a ON
tx_dam.uid=tx_dam_mm_cat_a.uid_local
LEFT JOIN tx_dam_mm_cat AS tx_dam_mm_cat_b ON
tx_dam.uid=tx_dam_mm_cat_b.uid_local
LEFT JOIN tx_dam_mm_cat AS tx_dam_mm_cat_c ON
tx_dam.uid=tx_dam_mm_cat_c.uid_local
LEFT JOIN tx_dam_mm_cat AS tx_dam_mm_cat_d ON
tx_dam.uid=tx_dam_mm_cat_d.uid_local , tx_dam_cat
WHERE 1
AND tx_dam.pid IN (1) AND tx_dam.sys_language_uid=0
AND tx_dam.file_status!=255 AND tx_dam_mm_cat_d.uid_foreign IN (48)
AND (tx_dam_mm_cat_a.uid_foreign IN (33) OR tx_dam_mm_cat_b.uid_foreign IN
(34) OR tx_dam_mm_cat_c.uid_foreign IN (220))
AND tx_dam.deleted=0 AND tx_dam_cat.deleted=0

On larger instances the above query will not respond: having 18.000 
assets, 200 categories and 20.000+ MM-relations the runtime was 18 minutes.

Solution:
rewriting the above query to

SELECT tx_dam.*
FROM
   tx_dam
   LEFT JOIN tx_dam_mm_cat ON tx_dam_mm_cat.uid_local = tx_dam.uid
   LEFT JOIN tx_dam_cat ON tx_dam_mm_cat.uid_foreign = tx_dam_cat.uid
WHERE
   tx_dam.deleted = 0
   AND tx_dam.pid IN (1)
GROUP BY tx_dam.uid
having find_in_set(63,GROUP_CONCAT(tx_dam_cat.uid)) and 
(find_in_set(62,GROUP_CONCAT(tx_dam_cat.uid)) or 
find_in_set(40,GROUP_CONCAT(tx_dam_cat.uid)) or 
find_in_set(199,GROUP_CONCAT(tx_dam_cat.uid)))

returns in 0,06 seconds on the same database.

Note:
as discussed earlier the patch may or may not work on other DBMS than 
mysql. Thnx to Mattes for the basic idea for the query. Thnx to Ingo for 
the sql_num_rows()-hack.

Regards,
pekue
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: dam_9830.patch
Url: http://lists.netfielders.de/pipermail/typo3-team-dam/attachments/20081124/2153a83f/attachment.txt