问题描述
我有700万条记录的mysql数据库
I have mysql db with 7 Million records
当我运行类似查询时
select * from data where cat_id=12 order by id desc limit 0,30
查询需要很长时间，例如0.4603秒
the query take long time like 0.4603 sec
，但是使用out(where cat_id=12)或out(order by id desc)的相同查询非常快速查询需要很长时间，例如0.0002秒
but same query with out (where cat_id=12) or with out (order by id desc) very Fastthe query take long time like 0.0002 sec
我在cat_id和id上都有索引
I have indexes on cat_id and id
可以通过任何方式快速(按位置和顺序)进行查询
there is any way to make query with (where and order by) fast
谢谢
推荐答案
创建一个结合了cat_id和id的复合索引.参见 http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html 了解语法和示例.
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.
如果仅声明"cat_id = 12"，则将获得所有匹配的行，这是快速的，因为有索引.但是这些行不会被排序，因此mysql必须将它们全部读入一个临时表并对该表进行排序，这很慢.
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.
类似地，"order by id desc"将迅速对行进行排序，但是mysql必须读取所有行以找出哪些行具有"cat_id = 12"，这很慢.
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.
这篇关于mysql查询位置和顺序需要很长时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！