Re: sub query is extermely slow

[sam wun]

Clint Edwards wrote:
Sam,
Can you send the output of the following:
#>explain <your query>\G
Thanks for the suggestion, here is the output of the explain query:
mysql> explain select DISTINCT i.basename from inventory i, transaction 
t, customer c where i.prodcode = t.prodcode and c.custcode = t.custcode 
and i.basename is not NULL and i.prodname is not NULL and ((date(t.date) 
>= "2004-01-01" and date(t.date) <= "2004-01-31") and i.basename IN 
(select DISTINCT ii.basename from inventory ii, transaction tt, customer 
cc where ii.prodcode = tt.prodcode and cc.custcode = tt.custcode and 
ii.basename is not NULL and ii.prodname is not NULL and(date(tt.date) >= 
"2005-01-01" and date(tt.date) <= "2005-01-31"))) order by i.basename;
+----+--------------------+-------+--------+-------------------+----------+---------+----------------------+------+----------------------------------------------+
| id | select_type        | table | type   | possible_keys     | 
key      | key_len | ref                  | rows | 
Extra                                        |
+----+--------------------+-------+--------+-------------------+----------+---------+----------------------+------+----------------------------------------------+
|  1 | PRIMARY            | c     | index  | PRIMARY           | 
PRIMARY  | 32      | NULL                 |  317 | Using index; Using 
temporary; Using filesort |
|  1 | PRIMARY            | t     | ref    | custcode,prodcode | 
custcode | 32      | datacube.c.custcode  |   36 | Using 
where                                  |
|  1 | PRIMARY            | i     | eq_ref | PRIMARY           | 
PRIMARY  | 32      | datacube.t.prodcode  |    1 | Using 
where                                  |
|  2 | DEPENDENT SUBQUERY | cc    | index  | PRIMARY           | 
PRIMARY  | 32      | NULL                 |  317 | Using index; Using 
temporary                 |
|  2 | DEPENDENT SUBQUERY | tt    | ref    | custcode,prodcode | 
custcode | 32      | datacube.cc.custcode |   36 | Using 
where                                  |
|  2 | DEPENDENT SUBQUERY | ii    | eq_ref | PRIMARY           | 
PRIMARY  | 32      | datacube.tt.prodcode |    1 | Using 
where                                  |
+----+--------------------+-------+--------+-------------------+----------+---------+----------------------+------+----------------------------------------------+
6 rows in set (0.01 sec)

Clint
From: sam wun <[EMAIL PROTECTED]>
To: mysql@lists.mysql.com
Subject: sub query is extermely slow
Date: Wed, 19 Jan 2005 20:02:37 +0800
Hi list,
The following sql statement takes 3 mintues to complete the query. 
How can I improve its speed?
select DISTINCT i.basename from inventory i, transaction t, customer 
c where i.prodcode = t.prodcode and c.custcode = t.custcode and 
i.basename is not NULL and i.prodname is not NULL and ((date(t.date) 
>= "2004-01-01" and date(t.date) <= "2004-01-31") and i.basename IN 
(select DISTINCT ii.basename from inventory ii, transaction tt, 
customer cc where ii.prodcode = tt.prodcode and cc.custcode = 
tt.custcode and ii.basename is not NULL and ii.prodname is not NULL 
and(date(tt.date) >= "2005-01-01" and date(tt.date) <= 
"2005-01-31"))) order by i.basename

Thanks
Sam

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]