From: Clint Edwards Date: January 19 2005 2:51pm Subject: Re: sub query is extermely slow List-Archive: http://lists.mysql.com/mysql/178705 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; format=flowed 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 ' 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 >To: Clint Edwards >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 ' (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 ' (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 >>>To: Clint Edwards >>>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 \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 >>>>>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=msstudios@stripped >>> >> >> >> > > >-- >Senior Security Architect/Consultant >AuthTec Gateway Limited >Mobile: +852 9839 2464 >Email: sam.wun@stripped >Website: http://www.authtec.com > >