[TYPO3-dam-devel] DAM, slow queries and DBAL

[Dan Osipov]

First - wrong list to discuss this. It should be posted to the t.p.dam

Second - I have no opinion on the views/no views issue, so I'm for 
anything that improves performance. But this would be a radical change 
to the DAM, and would have to be implemented in 1.2
I noticed some other file operations that need improvement, and was 
going to work on them after 1.1 is released.

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

Peter Kuehn [wmdb] wrote:
> Hi list,
> 
> we are currently trying to solve a problem that imho should be solved in 
> general.
> 
> One of the things editors love when working with DAM is the posibility 
> to say "show me files that are categorised with category a or b or c".
> 
> The generated query reads (as you all know)
> 
> SELECT [fieldlist]
> 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
> , 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_a.uid_foreign IN (7)
> OR tx_dam_mm_cat_b.uid_foreign IN (8)
> OR tx_dam_mm_cat_c.uid_foreign IN (16))
> AND tx_dam.deleted=0 AND tx_dam_cat.deleted=0;
> 
> and is fired twice, first to "COUNT(DISTINCT tx_dam.uid) as count" and 
> then again to get the rows if there are any.
> 
> Having 18.526 DAM-records with 20.068 relations to 200 categories it 
> takes 13 min 15.80 sec to return "36" and again 11 min 1.03 sec to 
> return the 36 rows on our quadcore/8GB RAM devserver.
> 
> 
> Basically the query can be rewriten 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) AND tx_dam_cat.deleted=0
> GROUP BY tx_dam_mm_cat.uid_local
> having (find_in_set(7,GROUP_CONCAT(tx_dam_cat.uid)) OR 
> find_in_set(8,GROUP_CONCAT(tx_dam_cat.uid)) OR 
> find_in_set(16,GROUP_CONCAT(tx_dam_cat.uid)))
> 
> which returns the same 36 rows in 0.31 sec.
> 
> To get it to return "36" with a count() it would have to be implemented 
> as a view.
> On the one hand working with views means breaking with DBAL compliance - 
> we know. On the other looking at class.ext_update.php DAM is already not 
> DBAL compliant - if we´re right.
> 
> We all know about the hassle with introducing new stuff to TYPO3 and 
> still being "compatible" with low cost webhosting, but since we rose the 
> system requirements to PHP5 and MySQL5 in the last Major version (if I 
> recall Ingo correct) I don't see the problem in introducing views (given 
> the 10000000...(add lot's of zeroes here)..00% performance increase.
> 
> On the performance list Mathias and others already talked about dropping 
> support for systems that do not allow mysql users to create views or 
> stored procedures - with the result "enterprise needs enterprise access 
> levels to the system".
> 
> Even if we don't do this on the "big" scope of TYPO3 itself, at least 
> DAM is mostly being used by enterprise clients (or at least people who 
> know about the power of DAM) DAM should make use of features like this.
> 
> /discuss :)
> 
> greetinx
> pekue and mattes