This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-16. For the latest, interactive version please visit https://mariadb.com/kb/.

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
  • 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.

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.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.