2023-01-13

query timeout because of one always valid primary key condition in select mysql

I have a 40M record table having id as the primary key. I execute a select statement as follows:

select * from messages where (some condition) order by id desc limit 20;

It is ok and the query executes in a reasonable time. But when I add an always valid condition as follows, It takes a huge time.

select * from messages where id > 0 and (some condition) order by id desc limit 20;

I guess it is a bug and makes MySQL search from the top side of the table instead of the bottom side. If there is any other justification or optimization it would be great a help.

p.s. with a high probability, the results are found in the last 10% records of my table.

p.p.s. the some condition is like where col1 = x1 and col2 = x2 where col1 and col2 are indexed.



1 comment: