Bug #44593 MyISAM tables showing odd "rows" value with EXPLAIN of a JOIN query

Submitted: 1 May 2009 3:31 Modified: 1 May 2009 3:39
Reporter: Shawn Green Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.56, 5.0.72 OS:Any
Assigned to: CPU Architecture:Any

[1 May 2009 3:31] Shawn Green
Description:
The EXPLAIN plan appears to show an incorrect number of "rows" for half of a JOIN query. 

How to repeat:
1) Load the test tables - files to be attached later

2) RUN the test explain:

mysql>EXPLAIN SELECT b.a_jk
    -> FROM table_b b
    -> INNER JOIN table_a a
    -> ON a.a_pk = b.a_jk
    -> WHERE a.a_pk = 121705
    -> AND b.b_val in (20268,1894969,1459535);
+----+-------------+-------+-------+------------------+----------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys    | key      | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+------------------+----------+---------+-------+------+-------------+
|  1 | SIMPLE      | a     | const | PRIMARY          | PRIMARY  | 4       | const |    1 | Using index |
|  1 | SIMPLE      | b     | ref   | join_key,val_key | join_key | 5       | const |   10 | Using where |
+----+-------------+-------+-------+------------------+----------+---------+-------+------+-------------+
2 rows in set (1.16 sec)

mysql>analyze table table_a, table_b;
+--------------------+---------+----------+-----------------------------+
| Table              | Op      | Msg_type | Msg_text                    |
+--------------------+---------+----------+-----------------------------+
| issue35717.table_a | analyze | status   | OK                          |
| issue35717.table_b | analyze | status   | Table is already up to date |
+--------------------+---------+----------+-----------------------------+
2 rows in set (41.14 sec)

mysql>EXPLAIN SELECT b.a_jk
    -> FROM table_b b
    -> INNER JOIN table_a a
    -> ON a.a_pk = b.a_jk
    -> WHERE a.a_pk = 121705
    -> AND b.b_val in (20268,1894969,1459535);
+----+-------------+-------+-------+------------------+----------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys    | key      | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+------------------+----------+---------+-------+------+-------------+
|  1 | SIMPLE      | a     | const | PRIMARY          | PRIMARY  | 4       | const |    1 | Using index |
|  1 | SIMPLE      | b     | ref   | join_key,val_key | join_key | 5       | const |   10 | Using where |
+----+-------------+-------+-------+------------------+----------+---------+-------+------+-------------+
2 rows in set (0.00 sec)

localhost.issue35717 >show indexes from table_a\G
*************************** 1. row ***************************
       Table: table_a
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: a_pk
   Collation: A
 Cardinality: 320975
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
1 row in set (0.00 sec)

localhost.issue35717 >show indexes from table_b\G
*************************** 1. row ***************************
       Table: table_b
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: b_pk
   Collation: A
 Cardinality: 31795615
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 2. row ***************************
       Table: table_b
  Non_unique: 1
    Key_name: join_key
Seq_in_index: 1
 Column_name: a_jk
   Collation: A
 Cardinality: 118199
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 3. row ***************************
       Table: table_b
  Non_unique: 1
    Key_name: val_key
Seq_in_index: 1
 Column_name: b_val
   Collation: A
 Cardinality: 5299269
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
3 rows in set (0.02 sec)

Based on either the cardinality estimates or direct queries, there should not be 10 rows in this query from table_b. 

Suggested fix:
Either fix the computation or explain why 10 is the correct value.
[1 May 2009 3:34] MySQL Verification Team
files uploaded to FTP site as:
table_a.md.sql.bz2     843084 bytes
table_b.md.sql.bz2  272100674 bytes

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.052 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.