Check ALTER statement in MySQL doc. It explains how to add/modify an index after a table has been created.
> -----Original Message----- > From: sam wun [mailto:[EMAIL PROTECTED] > Sent: Wednesday, January 19, 2005 10:00 AM > Cc: mysql@lists.mysql.com > Subject: Re: sub query is extermely slow > > > 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 > >> > >>> > >>> 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 <[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] Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
