William Jiang

JavaScript,PHP,Node,Perl,LAMP Web Developer – http://williamjxj.com; https://github.com/williamjxj?tab=repositories

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

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.

Advertisements

One response to “MySQL: a column with a unique index as well as a normal index

  1. articles 09/21/2012 at 2:27 am

    Awsome site! I am loving it!! Will be back later to read some more. I am taking your feeds also

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: