Bug #102179
closed
TYPO3 backend module "list" can slow down on huge tables without index
0%
Description
Our concrete example was https://github.com/tpwd/ke_search/issues/190. We have an installation with 5525315 entries within that table. TYPO3 issues the following query for each table when opening the list module: SELECT COUNT(*) FROM `tx_kesearch_stat_word` WHERE `tx_kesearch_stat_word`.`pid` = 0 LIMIT 1;
This took very long as no index could be applied:
explain SELECT COUNT(*) FROM `tx_kesearch_stat_word` WHERE `tx_kesearch_stat_word`.`pid` = 0 LIMIT 1; ┌────┬─────────────┬───────────────────────┬────────────┬──────┬───────────────┬────────┬─────────┬────────┬─────────┬──────────┬─────────────┐ │ id │ select_type │ table │ partitions │ type │ possible_keys │ key │ key_len │ ref │ rows │ filtered │ Extra │ ├────┼─────────────┼───────────────────────┼────────────┼──────┼───────────────┼────────┼─────────┼────────┼─────────┼──────────┼─────────────┤ │ 1 │ SIMPLE │ tx_kesearch_stat_word │ <null> │ ALL │ <null> │ <null> │ <null> │ <null> │ 5525315 │ 10.0 │ Using where │ └────┴─────────────┴───────────────────────┴────────────┴──────┴───────────────┴────────┴─────────┴────────┴─────────┴──────────┴─────────────┘
Adding the index (as done here: https://github.com/tpwd/ke_search/pull/191) solves the issue:
explain SELECT COUNT(*) FROM `tx_kesearch_stat_word` WHERE `tx_kesearch_stat_word`.`pid` = 0 LIMIT 1; ┌────┬─────────────┬───────────────────────┬────────────┬──────┬───────────────┬───────────┬─────────┬───────┬──────┬──────────┬─────────────┐ │ id │ select_type │ table │ partitions │ type │ possible_keys │ key │ key_len │ ref │ rows │ filtered │ Extra │ ├────┼─────────────┼───────────────────────┼────────────┼──────┼───────────────┼───────────┼─────────┼───────┼──────┼──────────┼─────────────┤ │ 1 │ SIMPLE │ tx_kesearch_stat_word │ <null> │ ref │ index_pid │ index_pid │ 4 │ const │ 1 │ 100.0 │ Using index │ └────┴─────────────┴───────────────────────┴────────────┴──────┴───────────────┴───────────┴─────────┴───────┴──────┴──────────┴─────────────┘
This is a TYPO3 backend specific query, not related to extension specifics. Maybe TYPO3 should add this index for all corresponding tables (Tables shown in list view)?
Updated by Stefan Bürk 7 months ago
- Status changed from New to Rejected
Hi Daniel,
TYPO3 creates a corresponding `parent(pid)` index, if the `pid`
field is added by the default TCA schema handling of the core.
The extension "kesearch" still defines `uid` and `pid` columns
in the `ext_tables.sql` itself. That takes the extension into
charge to define the index themself.
The saner solution would be, that the extension removes the auto
created fields from the `ext_tables.sql` and let the core create
the fields and indexes (where we create any).
#
# Table structure for table 'tx_kesearch_stat_word'
#
CREATE TABLE tx_kesearch_stat_word (
uid int(11) NOT NULL auto_increment,
pid int(11) DEFAULT '0' NOT NULL,
word text,
tstamp int(11) DEFAULT '0' NOT NULL,
pageid int(11) DEFAULT '0' NOT NULL,
resultsfound int(1) DEFAULT '0' NOT NULL,
language int(11) DEFAULT '0' NOT NULL,
PRIMARY KEY (uid)
);
Here, no parent index is created. Other tables in that
files defines the pid column and the parent index.
Therefore, I reject this issue. Please create a issue
on the extension side to take care of this.
Note: uid/pid and indexes are auto-created since TYPO3 v10!
Updated by Daniel Siepmann 6 months ago
- Assignee set to Daniel Siepmann
Thanks for your feedback. It is even more confusing. The extension does not define TCA for the table. That TYPO3 would not be able to create the schema. But the TYPO3 instance we inherited added TCA for the table. That's why TYPO3 issues the “slow” queries.
I encountered a 2nd query which is slow and will investigate myself whether this is an issue under “proper” circumstances. I therefore assign the issue to myself for further investigations.