MySQL INDEXES - Adding multuple columns to one index

Question

I am still getting my head around MySQL INDEXES... A quick question...

I have a table that stores a members location. It has a member_id and location_id columns... I do a MySQL query to find all the locations for a specific member...

Would it be better to setup an INDEX like this:

ALTER TABLE `members_locations` ADD INDEX `member_location` ( `member_id` , `location_id` )

Or should I separate them like this>

ALTER TABLE `members_locations` ADD INDEX `member_id` ( `member_id` );
ALTER TABLE `members_locations` ADD INDEX `location_id` ( `location_id` );

Does it make any difference?

Answer 1

Well,

I guess it would be better to have one index, but it actually depends on how you query it.

If you have both columns (member_id, location_id) in the where clause, they must definitely go into one index.

if you query them independently, e.g. sometimes by member_id, sometimes by location_id only, you might consider two indexes. However, even in that case, one of those index should probably include the second column as well to support queries where both columns are present.

At the end, it all depends what queries you would like to tune.

Although not for MySQL, but for Oracle, my new Web-Book "Use The Index, Luke" describes this in detail. AFAIK all databases are rather similar in that respect.

http://use-the-index-luke.com/where-clause/the-equals-operator/concatenated-keys

Answer 2

This article should be helpful.

Here's an example from it: ALTER TABLE buyers ADD INDEX idx_name_age(first_name,last_name,age);

Here's another article showing the difference between using a multi-column index and several single-column indexes.