I have a weird issue with TIMESTAMP() and indices.
In my table I have one INT (11) field, and in my query I use the following line :
[…]AND my_table.my_field <= UNIX_TIMESTAMP(NOW())[…]
EXPLAIN shows that around 7000 rows are read in order to get the result set. Which is quite a lot.
What I noticed is that if the number after the “<=” is 9 digits long, the query only scans 600 rows and seems to use the correct index, but if I use a timestamp (which is 10 digits long) then the wrong index is used, making the query really slow.
I tried with both dynamic (using UNIX_TIMESTAMP(NOW()) and hard-coded timestamps the result is the same, if the timestamp is 9 digits => 600 rows read, if it is 10 digits => 7000 rows read. I do not understand why.
I could force the use of a specific INDEX by using USE|FORCE INDEX but I do not like this solution which does not sound like the real fix for this issue to me.
How could I solve this issue without using USE|FORCE INDEX ?
Thanks in advance for any help.