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