Index issue with TIMESTAMP and indices

[jerome]

Hi all,

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.

)

[gmouse]

The answer is easy: my_table.my_field <= UNIX_TIMESTAMP(10 years ago) is far more restrictive so using an index on my_field narrows the result set by a lot. Using that index makes sense.

Whereas my_table.my_field <= UNIX_TIMESTAMP(NOW()) is not restrictive at all.

What is the full query, table lay-out, and output from EXPLAIN?

[jerome]

Hi gmouse, thanks for the answer.

I attached what you asked for in a file since the schema, the query and the result of EXPLAIN are quite big.

[gmouse]

That query looks horrible )

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.

[jerome]

[B]gmouse wrote on Fri, 14 August 2009 15:35[/B]

That query looks horrible )

he he

[B]gmouse wrote on Fri, 14 August 2009 15:35[/B]

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.

I will try this.

Thanks for your help )