MySQL Forums :: Newbie :: Index structure


Advanced Search

Index structure
Posted by: Shailendra mudgal ()
Date: March 09, 2010 01:09AM

Hi,

I have a table of two columns.
CREATE TABLE `tab1` (
`col1` int(11) NOT NULL,
`col2` varchar(20) NOT NULL DEFAULT '',
PRIMARY KEY (`col1`,`col2`),
KEY `col2_index` (`col2`),
KEY `col1_index` (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


This table may contain 20 Million rows.
The queries to this table are doing self join eg:
select distinct e1.col1 from tab1 e1, tab1 e2 where (e1.col2='<somevalue1>') and (e2.col2='<somevalue2>') and e1.col1=e2.col1;

These queries are taking a lot of time (around 40 sec).

Is there a way by which I can optimize the structure of table OR query so that it executes in minimum amount of time.

Thanks in advance...

Options: ReplyQuote


Subject Written By Posted
Index structure Shailendra mudgal 03/09/2010 01:09AM
Re: Index structure Rick James 03/10/2010 08:16AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.