Sql查询性能限制行数之间的差异

I have a hug table with 170,000 records.

我有一张170,000条记录的拥抱表。

What is difference between this query

这个查询有什么区别

Showing rows 0 - 299 (1,422 total, Query took 1.9008 sec)
    SELECT 1 FROM `p_apartmentbuy` p 
    where 
    p.price between 500000000 and 900000000
    and p.yard = 1
    and p.dateadd between 1290000000 and 1320000000
    ORDER BY `p`.`id` desc
    limit 1669

Explain

And this one:

和这个:

Showing rows 0 - 299 (1,422 total, Query took 0.2625 sec)
    SELECT 1 FROM `p_apartmentbuy` p 
    where 
    p.price between 500000000 and 900000000
    and p.yard = 1
    and p.dateadd between 1290000000 and 1320000000
    ORDER BY `p`.`id` desc
    limit 1670

Explain:

Both of these queries are using 1 table with same data and have same where clasue, but only limit row count are different

这两个查询都使用1个具有相同数据的表,并且具有相同的clasue,但只有限制行数不同

2 个解决方案

#1

MySQL has a buffer for sorting. When the stuff to be sorted is too big, it sorts chunks, then mergesorts them. This is called "filesort". Your 1670-th row apparently just overflows the sort buffer.

MySQL有一个用于排序的缓冲区。当要排序的东西太大时,它会对块进行排序,然后合并它们。这称为“filesort”。你的第1670行显然只是溢出了排序缓冲区。

2 个解决方案

#1

更多相关文章

随机推荐