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
>
>
