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

[Dan Osipov]

Attached is a version that handles the +/= combinations better.

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

Dan Osipov wrote:
> 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_v3.patch
Type: text/x-diff
Size: 26619 bytes
Desc: not available
Url : http://lists.netfielders.de/pipermail/typo3-team-dam/attachments/20090413/6b5c8b7c/attachment-0001.patch