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

Comment

If we look at this constraint:
----
(test_coat.contentclassattribute_id = 620 OR test_coat.contentclassattribute_id = 465 OR test_coat.contentclassattribute_id = 863)
---
MySQL thinks that it is more restrictive than a timestamp constraint if the timestamp is big. But since the ordering and distinct are also on timestamp, the timestamp index could give better performance especially if the number of result rows is big. IMO MySQLs query optimizer does not give any priority to indices for sorting (based on some tests I did on MySQL 5.0.38), so I would use FORCE INDEX(data_int).

If you really do not want to, then try some tricks such as adding OR test_coat.contentclassattribute_id > large number, OR data_int=0, that kind of tricks.

Comment

If we look at this constraint:
----
(test_coat.contentclassattribute_id = 620 OR test_coat.contentclassattribute_id = 465 OR test_coat.contentclassattribute_id = 863)
---
MySQL thinks that it is more restrictive than a timestamp constraint if the timestamp is big. But since the ordering and distinct are also on timestamp, the timestamp index could give better performance especially if the number of result rows is big. IMO MySQLs query optimizer does not give any priority to indices for sorting (based on some tests I did on MySQL 5.0.38), so I would use FORCE INDEX(data_int).

If you really do not want to, then try some tricks such as adding OR test_coat.contentclassattribute_id > large number, OR data_int=0, that kind of tricks.