CODEWITHSUNDEEP
mysqldatabaseperformance
khalid seleem
khalid seleem

Reputation: 117

mysql query with where and order by take long time

I have mysql db with 7 Million records

when I run query like

select  * from  data where cat_id=12 order by id desc limit 0,30

the query take long time like 0.4603 sec

but same query with out (where cat_id=12) or with out (order by id desc) very Fast the query take long time like 0.0002 sec

I have indexes on cat_id and id

there is any way to make query with (where and order by) fast

thanks

Upvotes: 3

Views: 1310

Answers (2)

Guntram Blohm
Guntram Blohm

Reputation: 9819

Create a composite index that combines cat_id and id. See http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html for syntax and examples.

If you state 'cat_id=12' only, you will get all matching rows, which is fast, because of the index. But these rows won't be ordererd, so mysql has to read them all into a temporary table and sort that table, which is slow.

Similarly, 'order by id desc' will order the rows quickly, but mysql has to read all of them to find out which have 'cat_id=12', which is slow.

A composite index should solve these issues.

Upvotes: 4

Rahul Tripathi
Rahul Tripathi

Reputation: 172438

It is running fast without order by since when you write order by DESC then it first iterates through all the rows and then it selects in descending order. Removing the condition makes it by default ASCENDING which makes it fast.

Also it may be that your index is sorted ascending so when you ask for descending it needs to do a lot more work to bring it back in that order

Upvotes: 0

Related Questions