0

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?

3 Answers 3

1

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.

1
  • yeah i know that but that don't means that EXPLAIN estimate will be lower than required, it can be higher. also not in that case, many more times i notice that EXPLAIN shows estimate row lot lower than it examined. so that's really hurting me as i'm unable to find out my query efficiency. also there is no clear documentation on MySQL website, how it works.
    – Rhidoy
    Commented Mar 26, 2019 at 17:32
0

First of all, EXPLAIN shows the number of examined rows. Suppose your query returns 0 rows, but MySQL needs to do a full table scan. EXPLAIN will show the number of rows in the table, not 0. However this doesn't really answer your question, since the number returned by EXPLAIN is lower than the rows you get.

The other thing you should know is that the number of rows you see is an approximation. In some cases it will be exact. Most of the times it will be close to the reality. Sometimes it will be completely wrong, which means that the table statistics are outdated and an ANALYZE TABLE is needed.

2
  • yeah i know that but that don't means that EXPLAIN estimate will be lower than required, it can be higher. also not in that case, many more times i notice that EXPLAIN shows estimate row lot lower than it examined, also my all table is up to date. so that's really hurting me as i'm unable to find out my query efficiency. also there is no clear documentation on MySQL website, how it works.
    – Rhidoy
    Commented Mar 26, 2019 at 17:33
  • Even after an ANALYZE TABLE the optimizer statistics will never have all your tables data precise. It would require queries that are simply not doable in non-trivial databases. Instead it does 10 random dives into indexes, and makes statistics on that sample. And that would be useless: the optimiser's purpose is to decide a proper query plan, not to tell you in advance how many rows you will get (that would require a magic spell). I think that your numbers are ok, unless you are observing some misbehaviour. Commented Mar 29, 2019 at 6:56
0

(I'll get to your question in a minute.)

How about simplifying it to

select  *
    from Player AS A
    inner join  Country C  ON A.Country_Name=C.Country_Id;
    where  p.Country_Name in (1,2)

Are your country "names" really "1" and "2"?

Most "Row" counts in EXPLAIN are estimates. They can be quite far off. There is no reliable way to get its counts to be reliable, or even consistent.

For the query as you wrote it, you need

Player:  INDEX(country_name)
Country: INDEX(country_id)   (or possibly PRIMARY KEY)

What version are you running? I'm surprised that it started with C and managed to realize that only 2 rows need be looked at, because, effectively, C.Country_Id IN (1,2). Then it looked in Player and used A.Country_Name.

It is as if it converted what you wrote into what I wrote.

Please try this: Run SHOW WARNINGS; immediately after doing the EXPLAIN. The output may show a radically different query -- and make the EXPLAIN more obvious. (If there are no warnings, then change to EXPLAIN EXTENDED SELECT ...; SHOW WARNINGS;)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.