List:General Discussion« Previous MessageNext Message »
From:Clint Edwards Date:January 19 2005 2:51pm
Subject:Re: sub query is extermely slow
View as plain text  
Sam,

Can you create an index on transaction.date, then run your query again?  If 
that is not better send me the output of 'explain <query>' again.

This index may not be a good idea, depending on how many transaction are in 
the table on a specified date.

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 22:05:58 +0800
>
>Clint Edwards wrote:
>
>>Sam,
>>
>>Can you send the following information:
>>
>>When was the last time 'analyze table <table_name>' (inventory, 
>>transaction, customer) was executed?
>>
>Hi, here is the result of the analyze command:
>mysql> analyze table inventory,transaction, customer;
>+----------------------+---------+----------+----------+
>| Table                | Op      | Msg_type | Msg_text |
>+----------------------+---------+----------+----------+
>| datacube.inventory   | analyze | status   | OK       |
>| datacube.transaction | analyze | status   | OK       |
>| datacube.customer    | analyze | status   | OK       |
>+----------------------+---------+----------+----------+
>3 rows in set (0.83 sec)
>
>>OS:
>>MySQL Version:
>
>Mysql 5.0
>
>>Available Ram:
>
>254RAM
>
>>
>>Output from 'SHOW CREATE TABLE <table_name>' (inventory, transaction, and 
>>customer):
>>
>>Output from "SHOW VARIABLES LIKE '%buffer%';":
>>
>mysql> SHOW CREATE TABLE inventory;
>+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>| Table     | Create Table                                                  
>                                                                            
>                                                                            
>                                                                            
>                                                                            
>                                                             |
>+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>| inventory | CREATE TABLE `inventory` (
>  `prodcode` varchar(32) NOT NULL default '',
>  `qty` decimal(9,2) NOT NULL default '0.00',
>  `lastupdatedate` date NOT NULL default '0000-00-00',
>  `prodname` varchar(32) default 'UNKNOWN',
>  `basename` varchar(32) default 'UNKNOWN',
>  `vendorname` varchar(50) default 'UNKNOWN',
>  `cost` decimal(9,2) NOT NULL default '0.00',
>  PRIMARY KEY  (`prodcode`)
>) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
>+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>1 row in set (0.39 sec)
>
>mysql> SHOW CREATE TABLE transaction;
>+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>| Table       | Create Table                                                
>                                                                            
>                                                                            
>                                                                            
>                                                                            
>                                                                            
>                                                                            
>                                                                            
>                                                                            
>                                                                            
>                                                                            
>  |
>+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>| transaction | CREATE TABLE `transaction` (
>  `transcode` varchar(16) NOT NULL default '',
>  `netsales` decimal(9,2) NOT NULL default '0.00',
>  `date` date NOT NULL default '0000-00-00',
>  `salesvolume` decimal(9,2) NOT NULL default '0.00',
>  `returncode` int(10) unsigned default '0',
>  `returnreason` varchar(128) default 'NONE',
>  `transtype` varchar(16) default 'Invoice',
>  `custcode` varchar(32) NOT NULL default '',
>  `prodcode` varchar(32) NOT NULL default '',
>  PRIMARY KEY  (`transcode`),
>  KEY `custcode` (`custcode`),
>  KEY `prodcode` (`prodcode`),
>  CONSTRAINT `transaction_ibfk_1` FOREIGN KEY (`custcode`) REFERENCES 
>`customer` (`custcode`) ON DELETE CASCADE,
>  CONSTRAINT `transaction_ibfk_2` FOREIGN KEY (`prodcode`) REFERENCES 
>`inventory` (`prodcode`) ON DELETE CASCADE
>) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
>+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>1 row in set (0.12 sec)
>
>mysql> SHOW CREATE TABLE customer;
>+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>| Table    | Create Table                                                   
>                                                                            
>                                                                            
>                                                                            
>                                                                            
>                                                      |
>+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>| customer | CREATE TABLE `customer` (
>  `custcode` varchar(32) NOT NULL default '',
>  `custname` varchar(50) NOT NULL default '',
>  `salescode` varchar(32) NOT NULL default '',
>  `type` varchar(16) default 'LOCAL',
>  PRIMARY KEY  (`custcode`),
>  KEY `salescode` (`salescode`),
>  CONSTRAINT `customer_ibfk_1` FOREIGN KEY (`salescode`) REFERENCES 
>`sales_rep` (`salescode`) ON DELETE CASCADE
>) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
>+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>1 row in set (0.02 sec)
>
>mysql> SHOW VARIABLES LIKE '%buffer%';
>+-------------------------------+---------+
>| Variable_name                 | Value   |
>+-------------------------------+---------+
>| bdb_log_buffer_size           | 32768   |
>| bulk_insert_buffer_size       | 8388608 |
>| innodb_buffer_pool_awe_mem_mb | 0       |
>| innodb_buffer_pool_size       | 8388608 |
>| innodb_log_buffer_size        | 1048576 |
>| join_buffer_size              | 131072  |
>| key_buffer_size               | 8388600 |
>| myisam_sort_buffer_size       | 8388608 |
>| net_buffer_length             | 16384   |
>| preload_buffer_size           | 32768   |
>| read_buffer_size              | 131072  |
>| read_rnd_buffer_size          | 262144  |
>| sort_buffer_size              | 2097144 |
>+-------------------------------+---------+
>13 rows in set (0.09 sec)
>
>
>>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
>>>
>>
>>
>>
>
>
>--
>Senior Security Architect/Consultant
>AuthTec Gateway Limited
>Mobile: +852 9839 2464
>Email: sam.wun@stripped
>Website: http://www.authtec.com
>
>


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