Clint Edwards wrote:
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.
May I ask how to add index to a field after a table is created?
Thanks Sam
Clint
From: sam wun <[EMAIL PROTECTED]> To: Clint Edwards <[EMAIL PROTECTED]> CC: mysql@lists.mysql.com 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
mysql> SHOW CREATE TABLE inventory;
Output from 'SHOW CREATE TABLE <table_name>' (inventory, transaction, and customer):
Output from "SHOW VARIABLES LIKE '%buffer%';":
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 <[EMAIL PROTECTED]> To: Clint Edwards <[EMAIL PROTECTED]> CC: mysql@lists.mysql.com 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 <[EMAIL PROTECTED]> To: mysql@lists.mysql.com 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/[EMAIL PROTECTED]
