Jan Kirchhoff <[EMAIL PROTECTED]> wrote on 02/01/2006 06:31:20 AM: > > 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) > >
Comma separated JOINS strikes again!!! As of 5.0.12, the comma you are using as a JOIN operator has been properly demoted in priority so that MySQL now more closely adheres to the SQL:2003 standard. This move from a non-standard implementation to a more standard implementation has caught more than one person. Your previous execution performance should return if you change your query to use an explicit JOIN statement and quit using those (#&[EMAIL PROTECTED]( commas. (I don't know if you can tell, but those commas are my pet peeve this decade) Try it this way and let us know how it works. SELECT * FROM dlstm_data_d s INNER JOIN stm_data_d t ON s.sys_cdd=t.sys_cdd AND s.local=t.local WHERE s.sym_cd_ is null AND t.sym_cd is not null; Here is where you will find this change documented in the manual: http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html and http://dev.mysql.com/doc/refman/5.0/en/news-5-0-12.html with more explicit details here: http://dev.mysql.com/doc/refman/5.0/en/join.html HTH! Shawn Green Database Administrator Unimin Corporation - Spruce Pine