#102179TYPO3 backend module "list" can slow down on huge tables without index
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)?
Reviews 0
No change resolves or names this issue in its commit message, and no comment links one.
History 2
-
Stefan Bürk commented and changed status
NewRejected 10/17/23, 12:01 -
Daniel Siepmann commented and changed assigned to Daniel Siepmann 11/02/23, 16:36
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.
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).
See: https://github.com/tpwd/ke_search/blob/3fcfb412bdc67383b474b2ced32836ca60abca9c/ext_tables.sql#L170-L179
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!