Description:
Hi, after upgrading from Mysql 3.23.56 to 4.1.0 some NATURAL JOINS don't work anymore (they kind of "work" but are extremely slow and returns way to many records). The first query works fine, but the second don't:
mysql> select count(*) from track natural join raceday;
+----------+
| count(*) |
+----------+
| 7074 |
+----------+
1 row in set (0.13 sec)
mysql> select count(*) from track natural join raceday natural join race;
+-----------+
| count(*) |
+-----------+
| 490709232 |
+-----------+
1 row in set (53.11 sec)
mysql>
Here are the explain table syntax (only primary key fields copyed):
mysql> explain track;
+-------------+---------------------------------------------------------------+-------------------+------+-----+---------+-------+
| Field | Type | Collation | Null | Key | Default | Extra |
+-------------+---------------------------------------------------------------+-------------------+------+-----+---------+-------+
| trackid | tinyint(3) unsigned | binary | | PRI | 0 | |
mysql> explain raceday;
+-------------+------------------------------+-------------------+------+-----+------------+-------+
| Field | Type | Collation | Null | Key | Default | Extra |
+-------------+------------------------------+-------------------+------+-----+------------+-------+
| trackid | tinyint(3) unsigned | binary | | PRI | 0 | |
| racedaynr | tinyint(3) unsigned | binary | | PRI | 0 | |
| rdate | date | latin1_swedish_ci | | PRI | 0000-00-00 | |
mysql> explain race;
+-------------+------------------------------------------------------------+-------------------+------+-----+------------+-------+
| Field | Type | Collation | Null | Key | Default | Extra |
+-------------+------------------------------------------------------------+-------------------+------+-----+------------+-------+
| trackid | tinyint(3) unsigned | binary | | PRI | 0 | |
| racedaynr | tinyint(3) unsigned | binary | | PRI | 0 | |
| rdate | date | latin1_swedish_ci | | PRI | 0000-00-00 | |
| racenr | tinyint(3) unsigned | binary | | PRI | 0 | |
EXPLAIN ON THE QUERY:
mysql> explain select count(*) from track natural join raceday natural join race;
+----+-------------+---------+-------+-------------------------+-----------+---------+---------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+-------------------------+-----------+---------+---------------+------+-------------+
| 1 | SIMPLE | track | index | PRIMARY | PRIMARY | 1 | NULL | 58 | Using index |
| 1 | SIMPLE | race | ref | PRIMARY,race_raceday_fk | PRIMARY | 1 | track.trackid | 693 | Using index |
| 1 | SIMPLE | raceday | index | NULL | rdate_idx | 3 | NULL | 7074 | Using index |
+----+-------------+---------+-------+-------------------------+-----------+---------+---------------+------+-------------+
I don't really know what causes the problem but it seems to have something to do with the primary key having a date column as part of the composite key. It should also use the PRIMARY key or race_raceday_fk index instead of the rdate_idx index...
mysql> show index from race;
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----
----+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comm
ent |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----
----+
| race | 0 | PRIMARY | 1 | trackid | A | NULL | NULL | NULL | | BTREE |
|
| race | 0 | PRIMARY | 2 | racedaynr | A | NULL | NULL | NULL | | BTREE |
|
| race | 0 | PRIMARY | 3 | rdate | A | NULL | NULL | NULL | | BTREE |
|
| race | 0 | PRIMARY | 4 | racenr | A | 69368 | NULL | NULL | | BTREE |
|
| race | 1 | horsetype_idx | 1 | horsetype | A | 3 | NULL | NULL | | BTREE |
|
| race | 1 | startmethod_idx | 1 | startmethod | A | 3 | NULL | NULL | | BTREE |
|
| race | 1 | race_raceday_fk | 1 | trackid | A | 55 | NULL | NULL | | BTREE |
|
| race | 1 | race_raceday_fk | 2 | racedaynr | A | 55 | NULL | NULL | | BTREE |
|
| race | 1 | race_raceday_fk | 3 | rdate | A | 6936 | NULL | NULL | | BTREE |
|
| race | 1 | rdate_idx | 1 | rdate | A | 2167 | NULL | NULL | | BTREE |
|
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----
----+
In MySQL 3.23.56 it all works great and the optimizer uses the correct indexes.
How to repeat:
Create tables with the same columns and run queries.
Suggested fix:
I don't know how to fix the problem, all I know is that the optimizer must be confused by something in my database.
Description: Hi, after upgrading from Mysql 3.23.56 to 4.1.0 some NATURAL JOINS don't work anymore (they kind of "work" but are extremely slow and returns way to many records). The first query works fine, but the second don't: mysql> select count(*) from track natural join raceday; +----------+ | count(*) | +----------+ | 7074 | +----------+ 1 row in set (0.13 sec) mysql> select count(*) from track natural join raceday natural join race; +-----------+ | count(*) | +-----------+ | 490709232 | +-----------+ 1 row in set (53.11 sec) mysql> Here are the explain table syntax (only primary key fields copyed): mysql> explain track; +-------------+---------------------------------------------------------------+-------------------+------+-----+---------+-------+ | Field | Type | Collation | Null | Key | Default | Extra | +-------------+---------------------------------------------------------------+-------------------+------+-----+---------+-------+ | trackid | tinyint(3) unsigned | binary | | PRI | 0 | | mysql> explain raceday; +-------------+------------------------------+-------------------+------+-----+------------+-------+ | Field | Type | Collation | Null | Key | Default | Extra | +-------------+------------------------------+-------------------+------+-----+------------+-------+ | trackid | tinyint(3) unsigned | binary | | PRI | 0 | | | racedaynr | tinyint(3) unsigned | binary | | PRI | 0 | | | rdate | date | latin1_swedish_ci | | PRI | 0000-00-00 | | mysql> explain race; +-------------+------------------------------------------------------------+-------------------+------+-----+------------+-------+ | Field | Type | Collation | Null | Key | Default | Extra | +-------------+------------------------------------------------------------+-------------------+------+-----+------------+-------+ | trackid | tinyint(3) unsigned | binary | | PRI | 0 | | | racedaynr | tinyint(3) unsigned | binary | | PRI | 0 | | | rdate | date | latin1_swedish_ci | | PRI | 0000-00-00 | | | racenr | tinyint(3) unsigned | binary | | PRI | 0 | | EXPLAIN ON THE QUERY: mysql> explain select count(*) from track natural join raceday natural join race; +----+-------------+---------+-------+-------------------------+-----------+---------+---------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+-------------------------+-----------+---------+---------------+------+-------------+ | 1 | SIMPLE | track | index | PRIMARY | PRIMARY | 1 | NULL | 58 | Using index | | 1 | SIMPLE | race | ref | PRIMARY,race_raceday_fk | PRIMARY | 1 | track.trackid | 693 | Using index | | 1 | SIMPLE | raceday | index | NULL | rdate_idx | 3 | NULL | 7074 | Using index | +----+-------------+---------+-------+-------------------------+-----------+---------+---------------+------+-------------+ I don't really know what causes the problem but it seems to have something to do with the primary key having a date column as part of the composite key. It should also use the PRIMARY key or race_raceday_fk index instead of the rdate_idx index... mysql> show index from race; +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----- ----+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comm ent | +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----- ----+ | race | 0 | PRIMARY | 1 | trackid | A | NULL | NULL | NULL | | BTREE | | | race | 0 | PRIMARY | 2 | racedaynr | A | NULL | NULL | NULL | | BTREE | | | race | 0 | PRIMARY | 3 | rdate | A | NULL | NULL | NULL | | BTREE | | | race | 0 | PRIMARY | 4 | racenr | A | 69368 | NULL | NULL | | BTREE | | | race | 1 | horsetype_idx | 1 | horsetype | A | 3 | NULL | NULL | | BTREE | | | race | 1 | startmethod_idx | 1 | startmethod | A | 3 | NULL | NULL | | BTREE | | | race | 1 | race_raceday_fk | 1 | trackid | A | 55 | NULL | NULL | | BTREE | | | race | 1 | race_raceday_fk | 2 | racedaynr | A | 55 | NULL | NULL | | BTREE | | | race | 1 | race_raceday_fk | 3 | rdate | A | 6936 | NULL | NULL | | BTREE | | | race | 1 | rdate_idx | 1 | rdate | A | 2167 | NULL | NULL | | BTREE | | +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----- ----+ In MySQL 3.23.56 it all works great and the optimizer uses the correct indexes. How to repeat: Create tables with the same columns and run queries. Suggested fix: I don't know how to fix the problem, all I know is that the optimizer must be confused by something in my database.