mysql限制行数,MySQL解释行数限制

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

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

HERE I found the answer

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 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.

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

解决方案Does mysql LIMIT is taken into account when estimating number of rows in Explain?

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