MySQL Forums
Forum List  »  InnoDB

Whether Multiple index on Single table is necessary!??
Posted by: Akshath Hegde
Date: October 21, 2015 01:19AM

Hi,
I have table with 60 columns. and its a huge table having more than 1 crore records.

mysql>desc dummy_table;
col1 int
col2 varchar(10)
col2 datetime
.
.
.
col60 int


i have two select queries from this table.

1. select col1,col4,col5 from dummy_table where col1=1 and col6=8 and col10='abc';

2. select col21,col30,col50,col51 from dummy_table where col1=1 and col6=8 and col10='abc' and col20='123' and col13='12';


Now my doubt is whether i have to create two different index for above queries :

create index dummy_table_index1 on dummy_table (col1,col6,col10);

create index dummy_table_index1 on dummy_table (col1,col6,col10,col20,col13);

OR
Single Index is enough :

create index dummy_table_index1 on dummy_table (col1,col6,col10,col20,col13);

because second index includes all columns from first and second query.


Please suggest.

Thanks,
Akshath

Options: ReplyQuote


Subject
Views
Written By
Posted
Whether Multiple index on Single table is necessary!??
1346
October 21, 2015 01:19AM


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.