CODEWITHSUNDEEP
mysqloptimizationindexing
Benvorth
Benvorth

Reputation: 7722

MySQL: Add index on column with condition and operation

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?

Upvotes: 0

Views: 42

Answers (1)

Rahul
Rahul

Reputation: 77906

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

Upvotes: 1

Related Questions