MySQL: a column with a unique index as well as a normal index

In MySQL, can a table field owns different indexes? Yes, in some case, we can do that to increase the performance.

I have a table ‘keywords’, and want to add 2 different indexes for keyword field in the table: 1 is for its unique (no duplicated keyword), 2 is for speeding up (in the case of input-field auto-complete). Like this:

alter table `keywords` add unique index (`keyword`);
ALTER TABLE  `keywords` ADD INDEX (`keyword`);

This table is used for search-form auto-complete. Originally it is only with unique index, and is slow when user inputs keyword. After adding the index on `keyword` for speed-up purpose, which runs ‘select keyword from keywords where keyword like ’%‘._GET[‘k’].‘%’, I checked the result and it becomes much faster indeed. So adding a normal index besides unique index works. The conclusion is With or without the normal index is quite different.

It is somewhat confused for hiring 2 indexes on the same field. However, it works in the real-world. Without the second normal index on `keyword`, it is pretty slow.

Having several indexes on one field are very useful in some case, essentially, they do index different things.