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 <sam.wun@stripped>
>> To: mysql@stripped
>> 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
>>
