Archive for October 23rd, 2009
From Stack Overflow:
When I run an SQL command like the one below, it takes more than 15 seconds:SELECT * FROM news WHERE cat_id = 4 ORDER BY id DESC LIMIT 150000, 10
EXPLAINshows that its usingwhereand the index on
LIMIT 20, 10on the same query only takes several milliseconds.
This task can be reformulated like this:
take the last 150,010 rows in
id order and return the first 10 of them
It means that though we only need 10 records we still need to count off the first 150,000.
The table has an index which keeps the records ordered. This allows us not to use a
filesort. However, the query is still far from being efficient: 15 seconds for 150,000 records (which are already ordered) is way too much.
To better understand the reason behind the low performance let's look into this picture: