[TYPO3-core] RFC: improving index of sys_template

Martin Kutschker Martin.Kutschker at n0spam-blackbox.net
Mon Dec 11 12:50:14 CET 2006

Martin Kutschker schrieb:
> Martin Kutschker schrieb:
>> Hi!
>> I suggest to change the index "parent" to KEY parent (pid,sorting). 
>> All SELECTs that have a pid in the WHERE have also sorting in the 
>> As a test you may use this and have a look at output of EXPLAIN:
>> create index parent2 on sys_template (pid,sorting);
>> EXPLAIn SELECT * FROM sys_template WHERE pid=1 AND deleted=0 AND 
>> hidden=0 AND (starttime<=1157534861) AND (endtime=0 OR 
>> endtime>1157534861) ORDER BY sorting LIMIT 1;
>> The point is that now the "Using filesort" is gone and another few 
>> microseconds are saved by a tiny change of the code.
>> It probably makes sense to change other "parent" indices of tables 
>> with sorting columns as well (eg pages and tt_content), but I haven't 
>> investigated closely.
> # changed
> create index parent on pages (pid,sorting);
> create index parent on tt_content (pid,sorting);
> create index recordidentAS on sys_history (tablename,recuid,tstamp);
> To avoid a filesort for typical queries on pages and tt_content. The 
> last one is for Web>Pages/alt_doc.php which also reads the history.
> # new
> create index record on sys_log (recuid,uid);
> The last index is only used in history/undo but avoids a scan of the 
> complete sys_log table (history has WHERE recuid=<id> SORT BY <uid>).
> Attached is the diff. See above what's changed in t3lib/stddb/tables.sql 
> and typo3/sysext/cms/ext_tables.sql.



More information about the TYPO3-team-core mailing list