Why is MySQL preferring a single index over a composite index?

Question

I currently have this query:

SELECT 
    *
FROM
    (`session`)
WHERE
    (`session`.`isSale` = 0)
        AND (`session`.`createDatetime` >= (NOW() - INTERVAL 3 WEEK))
        AND (`session`.`sellerName` IS NOT NULL)
GROUP BY `session`.`id`
ORDER BY `session`.`id` DESC;

and I have the below indexes:

idx_createDatetime (`createDatetime` ASC)
idx_isSale (`isSale` ASC)
idx_sellerName (`sellerName` ASC)
idx_isSale_createDatetime_sellerName (`isSale` ASC, `createDatetime` ASC, `sellerName` ASC)
idx_createDatetime_isSale_sellerName (`createDatetime` ASC, `isSale` ASC, `sellerName` ASC)
idx_createDatetime_sellerName_isSale (`createDatetime` ASC, `sellerName` ASC, `isSale` ASC)

When using EXPLAIN before the query, to check it out, I'm finding that the index that's being used is idx_createDatetime

Is there a specific reason why MySQL is deciding to go with a basic index, when there are better indexes to use?

Answer 1

I have long thought that it is a bug. I have not figured out how to write a good test case to "prove" it.

Drop idx_isSale; then it will use idx_isSale_createDatetime_sellerName, which is probably optimal.

Virtually any use for the narrower index will be handled about as well by the wider index.

BTW, it it is rarely useful to start a composite index with a column that will be tested as a range. (That is, drop the last two indexes.)