Actions
Task #29896
closedIndex for "pages" table is still not optimal
Status:
Closed
Priority:
Must have
Assignee:
Category:
Performance
Target version:
Start date:
2011-09-16
Due date:
% Done:
100%
Estimated time:
0.50 h
TYPO3 Version:
4.6
PHP Version:
Tags:
Complexity:
medium
Sprint Focus:
Description
The fix for #21381 is not optimal. It causes "using filesort" in the List view for "pages" table. This happens due to the "hidden" field present in the index. This field should be removed from the index because it is not needed for the List module and it is not useful in FE due to more expensive fe_group field.
Sample SQL session with profiling results:
mysql> alter table pages drop index parent; Query OK, 1 row affected (0.03 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> alter table pages add index parent (pid,deleted,sorting); Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> explain SELECT title,uid,pid,module,extendToSubpages,nav_hide,doktype,hidden,starttime,endtime,fe_group,t3ver_id,t3ver_state,t3ver_wsid,t3ver_swapmode FROM pages WHERE pid=0 AND 1=1 AND pages.deleted=0 AND (pages.t3ver_state <= 0 OR pages.t3ver_wsid = 0) ORDER BY sorting LIMIT 31; +----+-------------+-------+------+---------------+--------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------+---------+-------------+------+-------------+ | 1 | SIMPLE | pages | ref | parent | parent | 5 | const,const | 112 | Using where | +----+-------------+-------+------+---------------+--------+---------+-------------+------+-------------+ 1 row in set (0.00 sec) mysql> alter table pages drop index parent; Query OK, 2 rows affected (0.07 sec)Records: 2 Duplicates: 0 Warnings: 0 mysql> alter table pages add index parent (pid,deleted,hidden,sorting); Query OK, 2 rows affected (0.02 sec)Records: 2 Duplicates: 0 Warnings: 0 mysql> explain SELECT title,uid,pid,module,extendToSubpages,nav_hide,doktype,hidden,starttime,endtime,fe_group,t3ver_id,t3ver_state,t3ver_wsid,t3ver_swapmode FROM pages WHERE pid=0 AND 1=1 AND pages.deleted=0 AND (pages.t3ver_state <= 0 OR pages.t3ver_wsid = 0) ORDER BY sorting LIMIT 31; +----+-------------+-------+------+---------------+--------+---------+-------------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------+---------+-------------+------+-----------------------------+ | 1 | SIMPLE | pages | ref | parent | parent | 5 | const,const | 112 | Using where; Using filesort | +----+-------------+-------+------+---------------+--------+---------+-------------+------+-----------------------------+ 1 row in set (0.01 sec) mysql>
Actions