TYPO3 Core

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>