[TYPO3-core] RFC: improving index of sys_template

[Martin Kutschker]

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 ORDER BY.
> 
> 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.

Masi
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: tables-index.sql
Url: http://lists.netfielders.de/pipermail/typo3-team-core/attachments/20061204/fcc0e709/attachment-0001.pot 
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: ext_tables-index.sql
Url: http://lists.netfielders.de/pipermail/typo3-team-core/attachments/20061204/fcc0e709/attachment-0001.asc