[TYPO3-dam] FYI - DAM 1.0.5 Media>File - performance - Indices missing warning

[georg k]

Ahoi Rene / DAM-Users;

As we've experienced this recently, I'd like to share it:
- only for DAM 1.0.5 Users - relevant
- simple: Do Upgrade!
- longer: Dont blame your DB / Filesystem / TYPO or DAM-Source, but DO 
check your tx_dam DB-Indices;

We recently experienced performance-issues with DAM 1.0.5 for the 
Media>File Module Listing of a Directory with 500+ Files/Dir.
Within the first 3 month of production the application performed fine, 
however later on the BE-listing took 30 - 300 seconds. This was in STARK 
contrast to the Media>List Module which worked pretty fast.

So we went ahead and debugged file-system performance, php-errors, used 
TYPO3-Debugging, checked mysql-performance, mysql slow-log and much more 
for quite too much time without any meaningful results. In addition 
/fileadmin is provided from another Box via NFS (which we suspected to 
be the bottleneck beforehand) and the DB is on another box too.

Last 'rescue' was "massive"-analysis by simply measuring the 
expensiveness (run-time) of roughly 10 dam-functions and finally found 
one SELECT which underperformed massively (took 0,4 seconds instead of 
the avv. 0,00x ...);
Solution was to add two indices to tx_dam, especially file_name and 
file_path, which caused the delay and apperently were missing in 1.0.5 
and , but are already included from 1.0.8 (AFAIR) onwards, aside others 
which should be standard like deleted/hidden...;

Now, the thingie "flutsches" again pretty fine, even with those 
overloaded directories containing +1.500 files (totally in DAM ~45.000) 
guess we have to re-educate those "editors" or start writing an 
auto-archiver for the file-system;

Anyway, hope this helps anybody
Cheers Georg