tags:

views:

39

answers:

2

Say for example I have a table like this, with a primary key on the first 3 fields:

itemid | type | value | other_field [etc]

If on my web pages I am nearly always selecting by the type field only, it is worth having a separate index on type (i.e. will performance improve), or does the primary key index cover the field anyway?

+3  A: 

The order of the fields in an index / key matters.

If your primary key is on (type, itemid, value) or (type, value, itemid) then an extra index will probably make little difference. An index on multiple columns can also be used on any complete prefix of that index. One advantage of creating a new index in this situation is that is slightly narrower so there will be more rows on each page, but this difference will probably be small. Also you should consider the benefits for queries against the extra cost for modifications to your data.

If your primary key is anything else, such as (itemid, type, value) then an extra index on (type) will probably help because the primary key cannot be used as an index on type when type is not first in the list.

The usual remarks about premature optimization also hold here. The best way to be sure what is fastest is to test it yourself on realistic data. There are some conditions where an index will not be used even if you think it should be, or other times when it uses an index other than the one you thought it would. If you want to improve the performance of your application you should collect (or randomly generate if that is not an option) some realistic quantity of data and measure the actual performance of your queries on this data with the different indexing strategies.

Mark Byers
+1  A: 

It depends on the order of the fields listed in the primary key.

It also depends on the queries you will make against the table.

If the primary key is on '(item, type, value)' in that order, then a query which expresses a condition only on 'type' cannot make good use of the primary key index. In that case, a separate index on 'type', or on '(type, ...)' might make some sense; it could probably be used by the query. However, if you never make any queries that only list type (they always list item and type), then the extra index is unlikely to be of any use.

It is silly to try to decide whether to add an index to a table without knowing (a) the indexes already present on the table and (b) the types of queries that run too slowly against the table. If none of the queries are too slow, you probably don't need the index.

Jonathan Leffler