maria-discuss team mailing list archive

[AskMonty KB <noreply@xxxxxxxxxxxx>]

Hello,

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):

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

To view or answer this question please visit: http://mariadb.com/kb/en/fitting-index-not-used/