mysql查询位置和顺序需要很长时间 [英] mysql query with where and order by take long time

问题描述

我有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 Fast the 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查询位置和顺序需要很长时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！