->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
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
So only at the first query only one colums is not using a index.
[TASK] Adapt indexes of sys_file_reference
The backend and frontend fire several queries for referenced
files - files with metadata overlay as used in tt_content for
images and media field as well as in pages:media.
Analysing the queries for that some indexed should be added
to improve the query speed on large installations.
This patch adds indexed for the table/fieldname combination
as well es the uid of the record the file is attached to.