Bug #44593 MyISAM tables showing odd "rows" value with EXPLAIN of a JOIN query
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.
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.
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.