Fitting index not used
MariaDB version: mysql Ver 15.1 Distrib 10.1.2-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
Here is the simplified situation (Query with like 10 joins, so I simplify to the simpliest query still reproducing the problem when working on slow queries):
SELECT article.article_id FROM article -- USE INDEX (s_ti_pd) LEFT JOIN member ON member.member_id = article.member_id WHERE article.thema_id = 29 AND article.state = 1 ORDER BY published_date LIMIT 0, 3;
- I have an index on
state, thema_id, published_date
which fit particularly well from my point of view (Query run in a few milliseconds using it)- When using
state, thema_id, published_date
index, explain give me: key_len: 8, ref: const,const, rows: 15006, filter: 100.00, Using where
- When using
- I have another index on
published_date
only (Query run in 1s using it)- When using
published_date
index, explain give me: key_len: 9, rows: 94, filtered: 100.00, Using where
- When using
- Table have a few other indexes...
- Forcing the usage of the index ran my query in a few milliseconds, without, one second.
- Removing the LEFT JOIN makes MariaDB use the good index
- Changing the thema_id sometimes change the index used
I dont see any factor that may tell the query optimizer to use published_date. In fact I see only one, it's shorter, so faster to read. But the status, thema_id, published_date
index seems, for me, obviously better, as it starts with two consts, and also can be used for the ORDER BY.
What can I do to understand MariaDB on this choice ? I only tried an "analyze table" but engine side, not "table side", for the moment, it changed nothing.
Answer Answered by Ian Gilfillan in this comment.
If you haven't already, please report this as an issue on JIRA - if you don't yet have a reproducible case, someone there may be able to help create one.