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>
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
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
Updated by Dmitry Dulepov almost 13 years ago
- Status changed from Under Review to Resolved
- % Done changed from 50 to 100
Applied in changeset 8ce0aefdd5ee575ff8bb9990534a3b1a674c70fb.
Updated by Riccardo De Contardi almost 7 years ago
- Status changed from Resolved to Closed
Actions