maria-discuss team mailing list archive
Mailing list archive
New Question: Fitting index not used
A new question has been asked in "Query Optimizations" by mandark. Please answer it at http://mariadb.com/kb/en/fitting-index-not-used/ as the person asking the question may not be subscribed to the mailing list.
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):
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
- 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
- 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.
To view or answer this question please visit: http://mariadb.com/kb/en/fitting-index-not-used/