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:sam.wun@stripped]
> Sent: Wednesday, January 19, 2005 10:00 AM
> Cc: mysql@stripped
> 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 <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
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.
