CODEWITHSUNDEEP
mysqldatabase-indexes
clarkk
clarkk

Reputation: 1

mysql and indexes with more than one column

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)

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`),

Upvotes: 2

Views: 125

Answers (1)

Karl T.
Karl T.

Reputation: 502

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.

Upvotes: 1

Related Questions