List:General Discussion« Previous MessageNext Message »
From:Jan Kirchhoff Date:February 1 2006 11:31am
Subject:Totally different join-behaviour in mysql 4 and 5?
View as plain text  
As I already wrote I try do get a replication running from a mysql-4.1.13 (32bit) master
to a 5.0.18 (64bit) slave. It only runs for a few minutes and then a query hangs.
I think I now found out why:
I modified a multi-table-update that hung to a select.  The same query on the absolutely
identical tables gives totally different "explains" on both systems:
While my query has a cardinality of 23,124*1=23,124 on mysql4, it has 
6,412*34,341=220,194,492 on mysql5 - and takes forever and makes me think everything
hangs?!
I verified this with a dump of to tables that I imported on various few different systems.
I created a new test-database, piped the dump into that and ran the following queries.

It looks like the join-behaviour of mysql has totally changed in mysql5!
This seems to affect quite a lot of queries here.
Am I doing really stupid mistakes or did I miss a major chapter in the "upgrading to
mysql5"-documentation that I read over and over during the last days?

Jan

I put the mysql versions in front of the "mysql>" prompts:


4.1.13-standard mysql> show table status;
+--------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name         | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length |
Max_data_length | Index_length | Data_free | Auto_increment | Create_time         |
Update_time         | Check_time          | Collation         | Checksum | Create_options
| Comment |
+--------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| dlstm_data_d | MyISAM |       9 | Dynamic    |  48621 |             49 |     2428108 |  
   4294967295 |      1921024 |         0 |           NULL | 2006-02-01 11:54:57 |
2006-02-01 11:55:08 | 2006-02-01 11:55:09 | latin1_swedish_ci |     NULL |               
|         |
| stm_data_d   | MyISAM |       9 | Dynamic    | 480772 |            105 |    50816164 |  
   4294967295 |     57697280 |         0 |           NULL | 2006-02-01 11:55:09 |
2006-02-01 11:57:00 | 2006-02-01 11:58:58 | latin1_swedish_ci |     NULL |               
|         |
+--------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
2 rows in set (0.00 sec)


5.0.18-max-log mysql> show table status;
+--------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name         | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length |
Max_data_length | Index_length | Data_free | Auto_increment | Create_time         |
Update_time         | Check_time          | Collation         | Checksum | Create_options
| Comment |
+--------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| dlstm_data_d | MyISAM |      10 | Dynamic    |  48621 |             49 |     2384860 |
281474976710655 |      1836032 |         0 |           NULL | 2006-02-01 11:54:05 |
2006-02-01 11:54:07 | 2006-02-01 11:54:07 | latin1_swedish_ci |     NULL |               
|         |
| stm_data_d   | MyISAM |      10 | Dynamic    | 480772 |            104 |    50192768 |
281474976710655 |     52738048 |         0 |           NULL | 2006-02-01 11:54:07 |
2006-02-01 11:55:12 | 2006-02-01 11:55:40 | latin1_swedish_ci |     NULL |               
|         |
+--------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
2 rows in set (0.00 sec)


4.1.13-standard mysql> explain select *from dlstm_data_d s, stm_data_d t where
s.sym_cd_ is null and s.sys_cdd=t.sys_cdd and s.local=t.local and s.local is not null and
s.local!="" and s.sys_cdd is not null and s.sys_cdd!="" and t.sym_cd is not null; 
+----+-------------+-------+------+---------------------------+-------------+---------+---------------+-------+-------------+
| id | select_type | table | type | possible_keys             | key         | key_len |
ref           | rows  | Extra       |
+----+-------------+-------+------+---------------------------+-------------+---------+---------------+-------+-------------+
|  1 | SIMPLE      | s     | ref  | idx_sys_cdd,idx_sym_boe   | idx_sym_boe |      21 |
const         | 23124 | Using where |
|  1 | SIMPLE      | t     | ref  | PRIMARY,idx_local,sys_cdd | idx_local   |      13 |
test2.s.local |     1 | Using where |
+----+-------------+-------+------+---------------------------+-------------+---------+---------------+-------+-------------+
2 rows in set (0.00 sec)


5.0.18-max-log mysql> explain select *from dlstm_data_d s, stm_data_d t where s.sym_cd_
is null and s.sys_cdd=t.sys_cdd and s.local=t.local and s.local is not null and
s.local!="" and s.sys_cdd is not null and s.sys_cdd!="" and t.sym_cd is not null; 
+----+-------------+-------+------+---------------------------+-------------+---------+-----------------+-------+-------------+
| id | select_type | table | type | possible_keys             | key         | key_len |
ref             | rows  | Extra       |
+----+-------------+-------+------+---------------------------+-------------+---------+-----------------+-------+-------------+
|  1 | SIMPLE      | s     | ref  | idx_sys_cdd,idx_sym_boe   | idx_sym_boe | 23      |
const           |  6412 | Using where |
|  1 | SIMPLE      | t     | ref  | PRIMARY,idx_local,sys_cdd | sys_cdd     | 8       |
test2.s.sys_cdd |     1 | Using where |
+----+-------------+-------+------+---------------------------+-------------+---------+-----------------+-------+-------------+
2 rows in set (0.00 sec)

4.1.13-standard mysql>  explain select *from dlstm_data_d s, stm_data_d t where
s.sym_cd_ is null and s.sys_cdd=t.sys_cdd and s.local=t.local    and t.sym_cd is not null
; 
+----+-------------+-------+------+---------------------------+-------------+---------+---------------+-------+-------------+
| id | select_type | table | type | possible_keys             | key         | key_len |
ref           | rows  | Extra       |
+----+-------------+-------+------+---------------------------+-------------+---------+---------------+-------+-------------+
|  1 | SIMPLE      | s     | ref  | idx_sys_cdd,idx_sym_boe   | idx_sym_boe |      21 |
const         | 23124 | Using where |
|  1 | SIMPLE      | t     | ref  | PRIMARY,idx_local,sys_cdd | idx_local   |      13 |
test2.s.local |     1 | Using where |
+----+-------------+-------+------+---------------------------+-------------+---------+---------------+-------+-------------+
2 rows in set (0.00 sec)

5.0.18-max-log mysql> explain select *from dlstm_data_d s, stm_data_d t where s.sym_cd_
is null and s.sys_cdd=t.sys_cdd and s.local=t.local    and t.sym_cd is not null ; 
+----+-------------+-------+------+---------------------------+-------------+---------+---------------+------+-------------+
| id | select_type | table | type | possible_keys             | key         | key_len |
ref           | rows | Extra       |
+----+-------------+-------+------+---------------------------+-------------+---------+---------------+------+-------------+
|  1 | SIMPLE      | s     | ref  | idx_sys_cdd,idx_sym_boe   | idx_sym_boe | 23      |
const         | 6412 | Using where |
|  1 | SIMPLE      | t     | ref  | PRIMARY,idx_local,sys_cdd | idx_local   | 15      |
test2.s.local |    1 | Using where |
+----+-------------+-------+------+---------------------------+-------------+---------+---------------+------+-------------+
2 rows in set (0.00 sec)


I did some further testing on other systems (with mysql-packages from debian, all 32bit
systems):

4.1.10a-Debian_2 mysql>  explain select *from dlstm_data_d s, stm_data_d t where
s.sym_cd_ is null and s.sys_cdd=t.sys_cdd and s.local=t.local    and t.sym_cd is not null
; 
+----+-------------+-------+------+---------------------------+-------------+---------+---------------+-------+-------------+
| id | select_type | table | type | possible_keys             | key         | key_len |
ref           | rows  | Extra       |
+----+-------------+-------+------+---------------------------+-------------+---------+---------------+-------+-------------+
|  1 | SIMPLE      | s     | ref  | idx_sys_cdd,idx_sym_boe   | idx_sym_boe |      21 |
const         | 23124 | Using where |
|  1 | SIMPLE      | t     | ref  | PRIMARY,idx_local,sys_cdd | idx_local   |      13 |
test2.s.local |     2 | Using where |
+----+-------------+-------+------+---------------------------+-------------+---------+---------------+-------+-------------+
2 rows in set (0.16 sec)

5.0.13-rc-Debian_1-log mysql>  explain select *from dlstm_data_d s, stm_data_d t where
s.sym_cd_ is null and s.sys_cdd=t.sys_cdd and s.local=t.local and s.local is not null and
s.local!="" and s.sys_cdd is not null and s.sys_cdd!="" and t.sym_cd is not null; 
+----+-------------+-------+------+---------------------------+-------------+---------+-----------------+-------+-------------+
| id | select_type | table | type | possible_keys             | key         | key_len |
ref             | rows  | Extra       |
+----+-------------+-------+------+---------------------------+-------------+---------+-----------------+-------+-------------+
|  1 | SIMPLE      | s     | ref  | idx_sys_cdd,idx_sym_boe   | idx_sym_boe | 23      |
const           |  6412 | Using where |
|  1 | SIMPLE      | t     | ref  | PRIMARY,idx_local,sys_cdd | sys_cdd     | 8       |
test2.s.sys_cdd | 34341 | Using where |
+----+-------------+-------+------+---------------------------+-------------+---------+-----------------+-------+-------------+
2 rows in set (0.00 sec)

5.0.13-rc-Debian_1-log mysql>  explain select *from dlstm_data_d s, stm_data_d t where
s.sym_cd_ is null and s.sys_cdd=t.sys_cdd and s.local=t.local    and t.sym_cd is not null
; 
+----+-------------+-------+------+---------------------------+-------------+---------+---------------+------+-------------+
| id | select_type | table | type | possible_keys             | key         | key_len |
ref           | rows | Extra       |
+----+-------------+-------+------+---------------------------+-------------+---------+---------------+------+-------------+
|  1 | SIMPLE      | s     | ref  | idx_sys_cdd,idx_sym_boe   | idx_sym_boe | 23      |
const         | 6412 | Using where |
|  1 | SIMPLE      | t     | ref  | PRIMARY,idx_local,sys_cdd | idx_local   | 15      |
test2.s.local |    1 | Using where |
+----+-------------+-------+------+---------------------------+-------------+---------+---------------+------+-------------+
2 rows in set (0.00 sec)


Thread
Totally different join-behaviour in mysql 4 and 5?Jan Kirchhoff1 Feb
  • Re: Totally different join-behaviour in mysql 4 and 5?SGreen1 Feb
    • Re: Totally different join-behaviour in mysql 4 and 5?Jan Kirchhoff1 Feb
  • Re: Totally different join-behaviour in mysql 4 and 5?Peter Brawley1 Feb