mysql and indexes with more than one column

Question

How to use indexes with more than one column

The original index has an index on block_id, but is it necesarry when it's already in the unique index with two column?

Indexes with more than one column

(a,b,c)

you can search for a, b and c
you can search for a and b
you can search for a
you can not search for a and c

Does this apply to unique indexes too?

table

id
block_id
account_id
name

indexes origin

PRIMARY KEY (`id`)
UNIQUE KEY `block_id` (`block_id`,`account_id`)
KEY `block_id` (`block_id`),
KEY `account_id` (`account_id`),

indexes alternative

PRIMARY KEY (`id`)
UNIQUE KEY `block_id` (`block_id`,`account_id`)
KEY `account_id` (`account_id`),

Karl T. · Accepted Answer · 2012-11-10 10:59:00Z

1

The rules you describe above have to my knowledge always held whether an index is unique or not. You might run explain on the query you have in mind and observe when the index is used and when it is not used under various circumstances.

answered Nov 10, 2012 at 10:59

Karl T.

5023 silver badges3 bronze badges

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

Collectives™ on Stack Overflow

mysql and indexes with more than one column

table

indexes origin

indexes alternative

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

table

indexes origin

indexes alternative

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related