Jan,
>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?!
Yep, MySQL improved the SQL compatibility of its join syntax beginning
with 5.0.12, see http://dev.mysql.com/doc/refman/5.0/en/join.html and,
esp. comments there on the change in precedence of the comma operator.
PB
-----
Jan Kirchhoff wrote:
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)
|