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

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.

CodePudding user response：

MySQL has to choose whether to use an index to process the WHERE clause, or use an index to control ORDER BY ... LIMIT ....

In the first query, the WHERE clause can't make effective use of an index, so it prefers to use the primary key index to optimize scanning in order by ORDER BY. In this case it stops when it finds 20 results that satisfy the WHERE condition.

In the second query, the id > 0 condition in the WHERE clause can make use of the index, so it prefers to use that instead of using the index for ORDER BY. In this case, it has to find all the results that match the WHERE condition, and then sort them by id.

I wouldn't really call this a bug, as there's no specification of precisely how a query should be optimized. It's not always easy for the query planner to determine the best way to make use of indexes. Using the index to filter the rows using WHERE id > x could be better if there aren't many rows that match that condition.

CodePudding user response：

It’s not a bug. You are searching through a 40 million table where your where clause doesn’t have an index. Add an index on the column in your where clause and you will see substantial improvement.