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.