MySQL Forums
Forum List  »  Optimizer & Parser

CONCAT and Index in where clause
Posted by: Aurelien PANIZZA
Date: August 22, 2012 08:45AM


I've got a really slow query (which I can't change) using the concat function in the where clause.

The query looks like :
SELECT * from MyTABLE WHERE CONCAT(YEAR,'-',MONTH,'-',DAY) = '2012-08-21';

Where 'YEAR', 'MONTH' and 'DAY' are three fields of type CHAR.

I tried to add an index only on the column YEAR (I don't think an Index on the three columns would be usefull because of the CONCAT function).

I can't get the index to be used in this case. I've seen some cases where there are types INT and CHAR in the CONCAT function, and so a function like CAST must be used to convert the INT type, but this is not my case.

Do you have any idea on how to get this fixed ?

(I'm looking with EXPLAIN SELECT ..., but the index is not listed as "possible_keys").

Options: ReplyQuote

Written By
CONCAT and Index in where clause
August 22, 2012 08:45AM
August 23, 2012 09:13PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.