Project

General

Profile

Actions

Task #29896

closed

Index for "pages" table is still not optimal

Added by Dmitry Dulepov almost 13 years ago. Updated over 6 years ago.

Status:
Closed
Priority:
Must have
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 #1

Updated by Mr. Hudson almost 13 years ago

Patch set 1 of change Ia5fc87f7b1fa89e515c292248365d1d684872be0 has been pushed to the review server.
It is available at http://review.typo3.org/4997

Actions #2

Updated by Dmitry Dulepov almost 13 years ago

  • Category set to Performance
  • Status changed from New to Under Review
  • Assignee set to Dmitry Dulepov
  • Target version set to 4.6.0-beta3
  • % Done changed from 0 to 50
  • Estimated time set to 0.50 h
  • Translation missing: en.field_remaining_hours set to 0.5
  • Complexity set to medium
Actions #3

Updated by Dmitry Dulepov almost 13 years ago

  • Status changed from Under Review to Resolved
  • % Done changed from 50 to 100
Actions #4

Updated by Riccardo De Contardi over 6 years ago

  • Status changed from Resolved to Closed
Actions

Also available in: Atom PDF