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

[Dan Osipov]

I modified the patch a bit to work better within the structure of query 
generation. Now its a bit more logical - at least to me.

While at it, I also implemented the subtraction for categories in the 
selections - it was marked as TODO, and with the new query its now possible.

Please review

Dan Osipov
Calkins Media
http://danosipov.com/blog/

Peter Kuehn [wmdb] wrote:
> 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 --------------
A non-text attachment was scrubbed...
Name: 9830_v2.patch
Type: text/x-diff
Size: 5892 bytes
Desc: not available
Url : http://lists.netfielders.de/pipermail/typo3-team-dam/attachments/20090413/64849ead/attachment.patch