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

[Peter Kuehn [wmdb]]

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