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/ _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
