[TYPO3-dev] rfc: minor db change for TS templates

[Martin Kutschker]

Hi!

All select on sys_template that go for pid use sorting. So the index should 
not be on (pid) but on (pid,sorting).

Eg:
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;

[ I hope the extra long lines come through ok]

Original:
+----+-------------+--------------+------+---------------+--------+---------+-------+------+-----------------------------+
| id | select_type | table        | type | possible_keys | key    | key_len 
| ref   | rows | Extra                       |
+----+-------------+--------------+------+---------------+--------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | sys_template | ref  | parent        | parent |       4 
| const |    3 | Using where; Using filesort |
+----+-------------+--------------+------+---------------+--------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)

New index:
create index parent2 on sys_template (pid,sorting);

New:
+----+-------------+--------------+------+----------------+---------+---------+-------+------+-------------+
| id | select_type | table        | type | possible_keys  | key     | 
key_len | ref   | rows | Extra       |
+----+-------------+--------------+------+----------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | sys_template | ref  | parent,parent2 | parent2 | 
  4 | const |    1 | Using where |
+----+-------------+--------------+------+----------------+---------+---------+-------+------+-------------+

Not much, but a filesort is avoided.

Masi