List:General Discussion« Previous MessageNext Message »
From:SGreen Date:February 1 2006 2:50pm
Subject:Re: Totally different join-behaviour in mysql 4 and 5?
View as plain text  
Jan Kirchhoff <kirchy@stripped> 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 (#&$@#( 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
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