MySQL解释行数限制 [英] MySQL Explain rows limit

问题描述

以下是我的查询，以获取20个带有genre_id 1的行.

Below is my query to get 20 rows with genre_id 1.

EXPLAIN SELECT * FROM (`content`) 
WHERE `genre_id` = '1' 
AND `category` = 1
LIMIT 20

我在内容表中总共有genre_id 1的654行，在genre_id上​​有索引，在上面的查询中，我将结果限制为仅显示20条记录，但工作正常，但解释是在行下显示654条记录，我尝试添加类别上的索引，但结果仍然相同，然后我也删除了AND类别= 1，但行数相同:

I have total 654 rows in content table with genre_id 1, I have index on genre_id and in above query I am limiting result to display only 20 records which is working fine but explain is showing 654 records under rows, I tried to add index on category but still same result and then also I removed AND category = 1 but same rows count:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  content ref genre_id    genre_id    4   const   654 Using where

这里，我找到了答案

估计行数时不考虑

LIMIT偶数 如果您有LIMIT来限制要检查的行数MySQL 仍会打印完整号码

LIMIT is not taken into account while estimating number of rows Even if you have LIMIT which restricts how many rows will be examined MySQL will still print full number

而且在评论中，还发布了另一个答复:

But also In comments another reply was posted:

现在，在估计行数时会考虑

LIMIT.我是 不知道哪个版本解决了这个问题，但是在5.1.30中，请解释 准确地考虑了LIMIT.

LIMIT is now taken into account when estimating number of rows. I’m not sure which version addressed this, but in 5.1.30, EXPLAIN accurately takes LIMIT into account.

我正在将MySQL 5.5.16与InnoDB一起使用.因此，根据上述评论，它仍然没有考虑在内.所以我的问题是，即使我设置了限制，mysql也会遍历所有654行以返回20行吗?谢谢

I am using MySQL 5.5.16 with InnoDB. so as per above comment its still not taking into account. So my question is does mysql go through all 654 rows to return 20 rows even I have set limit? Thanks

推荐答案

在 MySQL

在Explain中估计行数时，是否考虑mysql LIMIT?

Does mysql LIMIT is taken into account when estimating number of rows in Explain?

不. (5.7与JSON可能是另一回事.)

No. (5.7 with JSON may be a different matter.)

这篇关于MySQL解释行数限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！