MySQL: Add index on column with condition and operation

Question

In a query like this

SELECT * FROM myTable WHERE date = LEAST(maxDate, '2013-12-31')

I am looking for an index that will be used during execution. date and maxDate are Date types.

Any suggestions?

Rahul · Accepted Answer · 2014-12-04 14:08:52Z

1

Use of function(UDF or built-in) in WHERE clause don't take advantage of existing indexes but you can modify your query like below which will be using the already existing indexes on date or maxdate (if there is any) column like

SELECT * FROM myTable 
WHERE date = case when maxDate > '2013-12-31' then maxDate else '2013-12-31' end

answered Dec 4, 2014 at 14:08

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Benvorth Over a year ago

Very clever Suggestion, thank you. Unfortunately an HASH index on maxDate or date is not used that way ...

Collectives™ on Stack Overflow

MySQL: Add index on column with condition and operation

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related