Sam,
Can you send the following information:
When was the last time 'analyze table <table_name>' (inventory, transaction,
customer) was executed?
OS:
MySQL Version:
Available Ram:
Output from 'SHOW CREATE TABLE <table_name>' (inventory, transaction, and
customer):
Output from "SHOW VARIABLES LIKE '%buffer%';":
Clint
>From: sam wun <sam.wun@stripped>
>To: Clint Edwards <msstudios@stripped>
>CC: mysql@stripped
>Subject: Re: sub query is extermely slow
>Date: Wed, 19 Jan 2005 20:39:41 +0800
>
>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
>>>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
