MySQL Forums
Forum List  »  Newbie

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


I have a table of two columns.
`col1` int(11) NOT NULL,
`col2` varchar(20) NOT NULL DEFAULT '',
PRIMARY KEY (`col1`,`col2`),
KEY `col2_index` (`col2`),
KEY `col1_index` (`col1`)

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

Written By
Index structure
March 09, 2010 01:09AM
March 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.