0

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?

Patrick Younes
  • 139
  • 1
  • 15
  • *why MySQL is deciding to go with a basic index, when there are better indexes to use?* #1 - single index is more compact. #2 - additional columns selectivity is low. – Akina May 25 '22 at 13:26
  • The bigger question, why have all the individual column indexes when others have it and more. Indexes should be prepared based on most common conditions of querying such as your composites show. Get rid of the others, they will be redundant and slow/add confusion as you are encountering now. – DRapp May 25 '22 at 13:31

1 Answers1

0

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

Rick James
  • 135,179
  • 13
  • 127
  • 222