From: Peter Brawley
Date: February 1 2006 3:36pm
Subject: Re: Totally different join-behaviour in mysql 4 and 5?
List-Archive: http://lists.mysql.com/mysql/194459
Message-Id: <43E0D58D.1060206@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="=======AVGMAIL-43E0D58D6701======="

--=======AVGMAIL-43E0D58D6701=======
Content-Type: multipart/alternative; boundary=------------010002040206020601070601

--------------010002040206020601070601
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit

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)
>
>
>

--------------010002040206020601070601
Content-Type: text/html; charset=ISO-8859-15
Content-Transfer-Encoding: 8bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-15"
 http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Jan,<br>
<br>
<i>&gt;While my query has a cardinality of 23,124*1=23,124 on mysql4,
it has 
<br>
&gt;6,412*34,341=220,194,492 on mysql5 - and takes forever and makes <br>
&gt;me
think everything hangs?!
</i><br>
<br>
Yep, MySQL improved the SQL compatibility of its join syntax beginning
with 5.0.12, see <a class="moz-txt-link-freetext" href="http://dev.mysql.com/doc/refman/5.0/en/join.html">http://dev.mysql.com/doc/refman/5.0/en/join.html</a> and,
esp. comments there on the change in precedence of the comma operator. <br>
<br>
PB<br>
<br>
-----<br>
<br>
Jan Kirchhoff wrote:
<blockquote cite="mid43E09C08.7020807@stripped" type="cite"><br>
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.
  <br>
I think I now found out why:
  <br>
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:
  <br>
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?!
  <br>
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.
  <br>
  <br>
It looks like the join-behaviour of mysql has totally changed in
mysql5!
  <br>
This seems to affect quite a lot of queries here.
  <br>
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?
  <br>
  <br>
Jan
  <br>
  <br>
I put the mysql versions in front of the "mysql&gt;" prompts:
  <br>
  <br>
  <br>
4.1.13-standard mysql&gt; show table status;
  <br>
+--------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
  <br>
| 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 |
  <br>
+--------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
  <br>
| 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 |               
|         |
  <br>
| 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 |               
|         |
  <br>
+--------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
  <br>
2 rows in set (0.00 sec)
  <br>
  <br>
  <br>
5.0.18-max-log mysql&gt; show table status;
  <br>
+--------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
  <br>
| 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 |
  <br>
+--------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
  <br>
| 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 |               
|         |
  <br>
| 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 |               
|         |
  <br>
+--------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
  <br>
2 rows in set (0.00 sec)
  <br>
  <br>
  <br>
4.1.13-standard mysql&gt; 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;
+----+-------------+-------+------+---------------------------+-------------+---------+---------------+-------+-------------+
  <br>
| id | select_type | table | type | possible_keys             |
key         | key_len | ref           | rows  | Extra       |
  <br>
+----+-------------+-------+------+---------------------------+-------------+---------+---------------+-------+-------------+
  <br>
|  1 | SIMPLE      | s     | ref  | idx_sys_cdd,idx_sym_boe   |
idx_sym_boe |      21 | const         | 23124 | Using where |
  <br>
|  1 | SIMPLE      | t     | ref  | PRIMARY,idx_local,sys_cdd |
idx_local   |      13 | test2.s.local |     1 | Using where |
  <br>
+----+-------------+-------+------+---------------------------+-------------+---------+---------------+-------+-------------+
  <br>
2 rows in set (0.00 sec)
  <br>
  <br>
  <br>
5.0.18-max-log mysql&gt; 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;
+----+-------------+-------+------+---------------------------+-------------+---------+-----------------+-------+-------------+
  <br>
| id | select_type | table | type | possible_keys             |
key         | key_len | ref             | rows  | Extra       |
  <br>
+----+-------------+-------+------+---------------------------+-------------+---------+-----------------+-------+-------------+
  <br>
|  1 | SIMPLE      | s     | ref  | idx_sys_cdd,idx_sym_boe   |
idx_sym_boe | 23      | const           |  6412 | Using where |
  <br>
|  1 | SIMPLE      | t     | ref  | PRIMARY,idx_local,sys_cdd |
sys_cdd     | 8       | test2.s.sys_cdd |     1 | Using where |
  <br>
+----+-------------+-------+------+---------------------------+-------------+---------+-----------------+-------+-------------+
  <br>
2 rows in set (0.00 sec)
  <br>
  <br>
4.1.13-standard mysql&gt;  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 ;
+----+-------------+-------+------+---------------------------+-------------+---------+---------------+-------+-------------+
  <br>
| id | select_type | table | type | possible_keys             |
key         | key_len | ref           | rows  | Extra       |
  <br>
+----+-------------+-------+------+---------------------------+-------------+---------+---------------+-------+-------------+
  <br>
|  1 | SIMPLE      | s     | ref  | idx_sys_cdd,idx_sym_boe   |
idx_sym_boe |      21 | const         | 23124 | Using where |
  <br>
|  1 | SIMPLE      | t     | ref  | PRIMARY,idx_local,sys_cdd |
idx_local   |      13 | test2.s.local |     1 | Using where |
  <br>
+----+-------------+-------+------+---------------------------+-------------+---------+---------------+-------+-------------+
  <br>
2 rows in set (0.00 sec)
  <br>
  <br>
5.0.18-max-log mysql&gt; 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 ;
+----+-------------+-------+------+---------------------------+-------------+---------+---------------+------+-------------+
  <br>
| id | select_type | table | type | possible_keys             |
key         | key_len | ref           | rows | Extra       |
  <br>
+----+-------------+-------+------+---------------------------+-------------+---------+---------------+------+-------------+
  <br>
|  1 | SIMPLE      | s     | ref  | idx_sys_cdd,idx_sym_boe   |
idx_sym_boe | 23      | const         | 6412 | Using where |
  <br>
|  1 | SIMPLE      | t     | ref  | PRIMARY,idx_local,sys_cdd |
idx_local   | 15      | test2.s.local |    1 | Using where |
  <br>
+----+-------------+-------+------+---------------------------+-------------+---------+---------------+------+-------------+
  <br>
2 rows in set (0.00 sec)
  <br>
  <br>
  <br>
I did some further testing on other systems (with mysql-packages from
debian, all 32bit systems):
  <br>
  <br>
4.1.10a-Debian_2 mysql&gt;  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 ;
+----+-------------+-------+------+---------------------------+-------------+---------+---------------+-------+-------------+
  <br>
| id | select_type | table | type | possible_keys             |
key         | key_len | ref           | rows  | Extra       |
  <br>
+----+-------------+-------+------+---------------------------+-------------+---------+---------------+-------+-------------+
  <br>
|  1 | SIMPLE      | s     | ref  | idx_sys_cdd,idx_sym_boe   |
idx_sym_boe |      21 | const         | 23124 | Using where |
  <br>
|  1 | SIMPLE      | t     | ref  | PRIMARY,idx_local,sys_cdd |
idx_local   |      13 | test2.s.local |     2 | Using where |
  <br>
+----+-------------+-------+------+---------------------------+-------------+---------+---------------+-------+-------------+
  <br>
2 rows in set (0.16 sec)
  <br>
  <br>
5.0.13-rc-Debian_1-log mysql&gt;  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;
+----+-------------+-------+------+---------------------------+-------------+---------+-----------------+-------+-------------+
  <br>
| id | select_type | table | type | possible_keys             |
key         | key_len | ref             | rows  | Extra       |
  <br>
+----+-------------+-------+------+---------------------------+-------------+---------+-----------------+-------+-------------+
  <br>
|  1 | SIMPLE      | s     | ref  | idx_sys_cdd,idx_sym_boe   |
idx_sym_boe | 23      | const           |  6412 | Using where |
  <br>
|  1 | SIMPLE      | t     | ref  | PRIMARY,idx_local,sys_cdd |
sys_cdd     | 8       | test2.s.sys_cdd | 34341 | Using where |
  <br>
+----+-------------+-------+------+---------------------------+-------------+---------+-----------------+-------+-------------+
  <br>
2 rows in set (0.00 sec)
  <br>
  <br>
5.0.13-rc-Debian_1-log mysql&gt;  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 ;
+----+-------------+-------+------+---------------------------+-------------+---------+---------------+------+-------------+
  <br>
| id | select_type | table | type | possible_keys             |
key         | key_len | ref           | rows | Extra       |
  <br>
+----+-------------+-------+------+---------------------------+-------------+---------+---------------+------+-------------+
  <br>
|  1 | SIMPLE      | s     | ref  | idx_sys_cdd,idx_sym_boe   |
idx_sym_boe | 23      | const         | 6412 | Using where |
  <br>
|  1 | SIMPLE      | t     | ref  | PRIMARY,idx_local,sys_cdd |
idx_local   | 15      | test2.s.local |    1 | Using where |
  <br>
+----+-------------+-------+------+---------------------------+-------------+---------+---------------+------+-------------+
  <br>
2 rows in set (0.00 sec)
  <br>
  <br>
  <br>
  <br>
</blockquote>
</body>
</html>

--------------010002040206020601070601--
--=======AVGMAIL-43E0D58D6701=======
Content-Type: text/plain; x-avg=cert; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Content-Description: "AVG certification"

No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.0/248 - Release Date: 2/1/2006

--=======AVGMAIL-43E0D58D6701=======--