CODEWITHSUNDEEP
mysqloracle-databaseindexingmultiple-columnsunique-key
Dave
Dave

Reputation: 769

need to create indexes for multi-column unique key columns?

For example, table FOO

          id         name         type
---------------------------------------------
         bigint(20)  varchar(30)  smallint(4)

unique key name_type (name, type).

index will be created for (name, type). But if many queries involve type such as type=aNumber, do I need to create index for type separately?

In other words, does index(name, type) include index(type)?
How about mysql and oracle db?

Upvotes: 3

Views: 568

Answers (2)

Rick James
Rick James

Reputation: 142366

These rules apply to MySQL. Probably they apply to other vendors.

INDEX(name, type) (or UNIQUE(name, type)) is useful for

WHERE name = ...
WHERE name = ... AND type = ...
WHERE name = ... AND something_else = ...
WHERE name LIKE 'Abc%'          -- note: does not start with wildcard
WHERE name BETWEEN 'x' AND 'y'
WHERE name = '123'      -- both are strings

but not useful for

WHERE type = ...        -- without `name`
WHERE name LIKE '%xyz'  -- because of _leading_ wildcard
WHERE name BETWEEN 'x' AND 'y' AND type = ... -- Only partially useful
WHERE name = 123      -- trouble between VARCHAR and number

Note: The order of the AND's clauses in WHERE does not matter; the order of the columns does matter.

So "But if many queries involve type such as type=aNumber, do I need to create index for type separately?" -- Extra index needed if you don't also have name = constant.

And "In other words, does index(name, type) include index(type)?" -- No, it is not 'included'.

See also: Index Cookbook

Upvotes: 4

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Imagine you dont have index

This query will need scan the whole table to find 'somename'

 SELECT *
 FROM yourTable 
 WHERE name = 'somename'

Now imagine you have index for name, the same query will use the index to quickly find the occurences of somename, but then will have to go back to the table to find the rest of the field.

Finally if you have a composite index with (name, type) and the following query.

 SELECT type
 FROM yourTable 
 WHERE name = 'somename'

You will find very quickly someone and you already will have type because is part of the index and dont need do the aditional look up on the table.

So is great, but what is the problem then? Composite index need more db space, and make insertions slower. So is a consideration you need to make.

Upvotes: 1

Related Questions