TYPO3 Core

Szenario:
>Setup two 6.2 Installations with content, pages, images based on heimwerker.de and gartencenter.de
>Activated all logs
>worked in the backend
>called different pages in the fe via AP
->Analysed mysql_slow_log by mysqldumpslow (look into you mysql distribution)

...
Count: 6 Time=0.02s (0s) Lock=0.00s (0s) Rows=0.8 (5), root[root]@localhost
SELECT uid FROM sys_file_reference WHERE uid_foreign=N AND sys_file_reference.deleted=N AND tablenames='S' AND fieldname='S' AND sys_file_reference.t3ver_wsid=N AND sys_file_reference.pid!=-N ORDER BY so
rting_foreign

Count: 24 Time=0.01s (0s) Lock=0.00s (0s) Rows=0.4 (9), root[root]@localhost
SELECT uid FROM sys_file_reference WHERE uid_foreign = N AND fieldname = 'S' AND tablenames = 'S' AND sys_file_reference.deleted=N

Count: 1765 Time=0.01s (21s) Lock=0.00s (0s) Rows=0.4 (732), root[root]@localhost
SELECT * FROM sys_file_reference WHERE tablenames='S' AND deleted = N AND hidden = N AND uid_foreign=N AND fieldname='S' ORDER BY sorting_foreign
...

Solution should be to add indices to the table sys_file_reference
KEY: tablenames and filedname
KEY: deleted
KEY: uid_foreign

So only at the first query only one colums is not using a index.