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
Triage: Triaged: D3 (Medium)

[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] Shawn Green
files uploaded to FTP site as:
table_a.md.sql.bz2     843084 bytes
table_b.md.sql.bz2  272100674 bytes