TYPO3 Core

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)?