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.
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.