Issue Task Closed

#54725Add Indexes to sys_file_reference

Ingo Schmitt 01/03/14, 15:24 updated 10/23/17, 21:46

Description

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.

Reviews 1