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?

dj_segfault · Accepted Answer · 2010-08-28 05:26:23Z

1

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.

answered Aug 28, 2010 at 5:26

dj_segfault

12.5k4 gold badges33 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Markus Winand · Accepted Answer · 2010-08-28 05:20:37Z

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

Collectives™ on Stack Overflow

MySQL INDEXES - Adding multuple columns to one index

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related