mysql query with where and order by take long time
database, mysql, performance
Solution
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.
Problem
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