I noticed that Drupal 8 (latest from git) has 13 duplicate MySQL indexes in default install. These are indexes on entity_id while entity_id is the first field in the primary key. Duplicate indexes are bad for writing (all indexes have to be maintained) but also for reading (optimizer has more indexes to consider).
It concerns these tables:
I checked an older (alpha version) we run somewhere and it quickly adds up to 126 duplicate indexes, so this seems bad. Eg:
| Issue category | Bug because the current database schema is not optimal |
|---|---|
| Issue priority | Normal because this doesn't change any functionality |
| Unfrozen changes | Unfrozen because it only changes the database schema for indexes, this doesn't change any API. |
| Comment | File | Size | Author |
|---|---|---|---|
| #7 | 2428297-7.patch | 1.69 KB | tvlooy |
| #4 | 2428297-3.patch | 673 bytes | tvlooy |
| #2 | 2428297-2.patch | 891 bytes | tvlooy |
Comments
Comment #1
tvlooy commentedComment #2
tvlooy commentedThis patch fixes the behavior, but I'm not sure this is the right place to fix it.
Comment #4
tvlooy commentedComment #5
tvlooy commentedThis fixes it right where it should be fixed.
Comment #7
tvlooy commentedFix tests.
Comment #8
tvlooy commentedComment #9
borisson_Added beta evaluation.
Comment #10
berdirThe duplication is because entity_id is already the first part in the primary key, and can be used to just query on that, the issue summary does not make that very clear, can you maybe update that.
I'm also wondering if a separate index for deleted is useful, that will in most cases always contain 0, and I don't think there is any performance-relevant query that would filter just on that? So we can possibly drop that too?
Same for langcode, what's the use case for querying *only* for the language code?
The primary query is probably loadFieldItems(), which does a query on entity_id, deleted, langcode and filters by delta. Does the order of the primary key work for that, or would it be worth switching langcode and delta there? The other one is probably entity query, specifically Tables::ensureFieldTable(), which joins on either entity_id or revision_id.
Comment #11
tvlooy commentedComment #12
tvlooy commentedComment #13
tvlooy commentedI updated the description a bit. Regarding the other indexes, someone added them (git blame says Alex Pott), maybe he can give us an insight about why and if the they can be removed.
Comment #14
berdiralex is one of the core committers, you need to look at the commit message (the issue reference and those that got commit credit) to see who actually worked on it.
Also, whatever commit that was, it probably just moved things around.
Anyway, my two suggestions are less clear than the change here, which makes sense. Let's open a separate issue to discuss those two and then I'm happy to RTBC this.
Comment #15
jeroentCreated follow up issue: #2447555: Unnecessary index on langcode and deleted column in dedicated field tables.
Comment #16
berdirThanks. I think this makes sense, as discussed, there is zero benefit from having this index, as the primary key solves this use case just fine.
Comment #17
jeroentComment #18
alexpottNice find. Over indexing and unnecessary indexes have been a problem with Drupal for ages. This issue is a normal bug fix, and doesn't include any disruptive changes, so it is allowed per https://www.drupal.org/core/beta-changes. Committed 91ffcc1 and pushed to 8.0.x. Thanks!