Come together with the global Drupal community in Rotterdam, 28 Sept – 1 Oct 2026. Sessions, contribution, connection, and Early Bird savings until 8 June.There is an awful lot of indexes on l10n_community_translation. I looked through the code and tested various indexes and came to the conclusion that apart from the primary index, these two indexes suffice to cover almost all queries: "is_suggestion/is_active/language" and "sid/language/is_suggestion/is_active" (fields in that exact order)
Separate indexes for is_active and is_suggestion and language are pretty useless and inefficient because most DBMS can only use one index anyway. It suffices to have both is_suggestion and is_active in one index (at the beginning) because all queries include either none or both of these fields. MySQL's query optimizer will internally rearrange the columns in the WHERE clause so that it can use that index. The second index starting with sid and language can be used for all queries that contain either only sid or sid and language or all fields.
This optimization is most apparent when executing the Query "INSERT INTO {l10n_community_status_flag} (sid, language) SELECT sid, '%s' FROM {l10n_community_string}"
| Comment | File | Size | Author |
|---|---|---|---|
| #4 | reorganize-indexes-2.patch | 2.21 KB | gábor hojtsy |
| #2 | reorganize-indexes.patch | 2.28 KB | gábor hojtsy |
Comments
Comment #1
gábor hojtsyGreat analysis. Can you cook up a quick patch? Thanks!
Comment #2
gábor hojtsyOk, looked at the indexes once more. is_active and is_suggestion are truly not looked at individually, only in concert. Same applies for language. The previously available is_suggestion+is_active index is covered by the new one which also adds language. Then, the sid, language and is_suggestion combo got is_active, since we always need to look at that one too. That also rules out the need for the standalone sid key.
This leaves us with the uid_entered key, which is then not covered in your proposed index set, so searching for submitted users would not use an index for the user lookup (but will for picking all active strings based on the second index). What do you think about this?
Here is a patch which then needs some discussion in terms of the uid_entered lookups I believe. Otherwise it all looks good IMHO.
Comment #3
kkaefer commentedYep, the uid_entered is not covered. I added a key and checked whether it was used by queries involving uid_entered, but it wasn't. However, it's possible that MySQL will use it when there are multiple users.
Comment #4
gábor hojtsyOk, this updated patch now leaves the uid_entered index alone, committing this one. Thanks for your report.