Task #90420
closed
Add index to fe_users table to improve backend list view performance with >100k rows
0%
Description
File in current master:
https://github.com/TYPO3/TYPO3.CMS/blob/master/typo3/sysext/frontend/ext_tables.sql
Index could be:
KEY backend_list_view_count (pid,deleted)
Index size with 150k rows is <3MB, so this index could be a default.
Could be backported to TYPO3 8 and 9.
Updated by Robert Heinig almost 5 years ago
The affected query used in list view is
select count(*) from fe_users where (fe_users.pid = ?) and (fe_users.deleted = ?)
Updated by Guido Schmechel over 4 years ago
Good catch! I run a simple test with 150k users. My local environment is fast enough for the backend, but i also check the sql data.
150k without index: MySQL Query time ~ 0.11 seconds
150k with index: MySQL Query time ~ 0.02 seconds
Where do you have performance troubles? In the simple list view?
Updated by Benni Mack about 4 years ago
- Status changed from New to Needs Feedback
Updated by Christian Kuhn almost 3 years ago
- Status changed from Needs Feedback to Rejected
Hmm. unsure about this index. we have an index (pid, username) already, so pid is covered already. this is probably why the difference as measured by guido is not that big, since "only" deleted is scanned. I think huge fe_users tables with lots of deleted records should be cleaned up regularily anyway ... that table typically contains personal information, so instances should strive to remove old data here anyways, especially if there are many thousands users in it.
An instance that really needs this key, can also just add in in an own ext_tables.sql file.
I'd thus say this key is likely not that useful for standard instances, so I'm going to close the issue for now.