is there any problem if same column in two type of Index

[abdfahim]

I have a table with 100 columns. Now I have made column1 and column2 as unique

 

unique index(column1,column2)

 

Now, I have to run queries on column1 and/or column2 frequently. So I made column1 and column2 indexed to speed up my query.

like

CREATE INDEX _incol ON customer (column1(10));

 

Now my question is

 

1) Is there any problem as I include column1 and column2 in two types of index?

2) Should I really need to make that two columns indexed separately or the unique index will do the task I want (speed up the query)?

[fenway]

The question is really if you ever query column2 without column1.

[abdfahim]

Sorry for late reply, I was out .. Anyway,

The question is really if you ever query column2 without column1.

the answer is No for 99% time.

[fenway]

Then you should simply have the two-column index instead.

[Flayra]

MySQL uses the index that best matches the query. If you are searching on 2 columns, an index that covers those 2 columns will be used first. Next is an index for either of the columns - if there is an index on both, MySQL will use the index that returns the least results.

 

So as said, if you search on both columns 99% of the time, you really only need the one index that has both columns.

[abdfahim]

But what is the side effect of indexing. I mean is there any problem if I make all kind of possible index (though sounds wired!!)??? The reason is I dont have any problem with space, rather my project is very sensitive with speed, even for 1% time!!

[fenway]

Just space and speed on insert/update.