List:General Discussion« Previous MessageNext Message »
From:Clint Edwards Date:January 19 2005 1:54pm
Subject:Re: sub query is extermely slow
View as plain text  
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
>


Thread
sub query is extermely slowsam wun19 Jan
  • RE: sub query is extermely slowClint Edwards19 Jan
    • Re: sub query is extermely slowsam wun19 Jan
      • Re: sub query is extermely slowClint Edwards19 Jan
  • Re: sub query is extermely slowSGreen19 Jan
    • RE: sub query is extermely slowAndy Eastham19 Jan
    • Re: sub query is extermely slowsam wun20 Jan
    • Re: sub query is extermely slowsam wun20 Jan
      • Re: sub query is extermely slowSGreen20 Jan
Re: sub query is extermely slowClint Edwards19 Jan
  • Re: sub query is extermely slowsam wun19 Jan
RE: sub query is extermely slowArtem Koltsov19 Jan