Below is my Query:
select *
from (
SELECT *
from Player
where Country_Name in (1,2)
) as A
inner join Country C ON A.Country_Name=C.Country_Id;
When i execute that Query i got 301 Rows but when i use
EXPLAIN
the output shows Rows 42 and 2.
Below is EXPLAIN Output:
+----+-------------+--------+------------+-------+---------------+--------------+---------+-----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+--------------+---------+-----------------------+------+----------+-------------+
| 1 | SIMPLE | C | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using where |
| 1 | SIMPLE | Player | NULL | ref | Country_Name | Country_Name | 4 | database.C.Country_Id | 42 | 100.00 | NULL |
+----+-------------+--------+------------+-------+---------------+--------------+---------+-----------------------+------+----------+-------------+
Why there is negative difference between Output Rows and Explain
Rows? Is not they will be same or more?
Best Answer
The
rows
column of EXPLAIN output is an estimate of the rows to be examined. Thus, value of this column and number of rows returned on executing the command can be different.