Are these good indexing guidelines?

Question

I use indexes regularly but it's still hard for me in certain conditions to know if they are helping or hurting. There are a few guidelines I follow, but I am not sure if they are good nor am I sure of their justification.

1. It is better to create an index on a narrow datatype than it is on a wide datatype (e.g. INT over DATETIME).

2. It is better to create an index on multiple columns than it is on a single column.

3. It is better to create an index on a column that is never (or rarely) updated than it is to index frequently changing columns.

Are these good guidelines? Since I'm not entirely sure why I follow these guidelines, can you help explain what is the justification for each and when would they not apply?

Answer 1

Other points (as noted, "How do I index?" is a topic for a book not a single post - also, so many of the answers come down to "it depends on your database and your workload"):

• Index selectivity is key. If you try and index a field that doesn't have many distinct values (i.e. a true/false bit field) then using that index is actually slower than just doing a table scan (but it still has to be maintained, thereby slowing DML calls [insert/update/delete] down to no benefit).
• In general, you're right about indexing narrow fields, but I'd rephrase it to "be very careful about indexing wide fields". If the index field is too narrow, you run into the selectivity problem above, but the wider the field the bigger the index is ("how much bigger?" depends on which DBMS you use).
• Indexing updated fields - If you have an index on a frequently-updated field, then yes that index will slow down updates to that field. However, if you're using that field quite often in query criteria it may still be worth it to index it. (see above: "it depends")
• Multi-column indexes: This is a tricky one:
  • Covering indexes (an index that contains all the fields for a query) can speed up queries (because then the query only has to look at the index - it doesn't have to refer to the base table).
  • Multi-column indexes generally have higher selectivity.
  • Multi-column indexes require more space.
  • They're only useful if the query filters on either the full index, or a leading subset. I.e. if you have an index on (State, County, ZIP), then queries filtering on (State), (State, County) or (State, County, ZIP) can use that index. Queries that filter on (County, ZIP), (County) or (ZIP) cannot use that index.
  • Corollary: The order of columns in a multi-column index is very important.
  • If you have a multi-column index (State, County, ZIP), then a single-column index on (State) would be redundant (since State is the first column, the multi-column index can be used for that). Note that SQL Server and Sybase (not sure about other RDBMS systems) don't prevent you from creating completely redundant indexes.

As always, the best indexing strategy is to analyze the workload your database is under and index to suit that. If you're indexing a data warehouse your indexes are going to be radically different from the indexes on an audit history database.

Answer 2

Basically, SQL Server will automatically create an index for your primary key.

Other than that, I would recommend to create indices on your foreign key columns - if you have a foreign key relationship, that's most likely because you need to JOIN two tables together, and your JOIN operations will benefit significantly if an index is present on the foreign key column.

From there on - observe your system, check your system performance, gather server traces to see what queries cause the biggest CPU and/or I/O load on your system. Armed with that information, design additional indices - but sparingly! An "over-indexed" system is often worse than one without any indices!

If you see an index that would make sense and that would help one or multiple of your "heavy" queries, add that index, and observe again.

Repeat this "observe - gather information - analyse information" cycle - basically forever...

See Kimberly Tripp's excellent Indexes: just because you can, doesn't mean you should! blog post on the dangers of over-indexing

Answer 3

Have a look at my SQL indexing guide: http://use-the-index-luke.com/

It is a very broad tutorial, as broad as your question.

However, some short notes:

1. Dates - you can, in principle, index every type. Date types have, however, some temptations that render indexes useless very often: Date Anti-Patterns.

2. One vs. multiple indexes:

   • Concatenated Indexes
   • Index Merge - Combining Multiple Indexes

3. Insert/Delete/Update: not yet published at use-the-index-luke.com (chapter 9). However, index maintenance is expensive. Whenever possible, try to re-use existing indexes, e.g. by re-ordering the columns so that multiple queries can use the index.

Answer 4

I'm going to approach this answer with a different context to the others, not sure if it will work!

A very short answer is that no, they are not good guidelines. You are attempting to reduce a broad, complicated topic to a simple yes/no decision tree that will fit into a forum Q&A. It can't be done.

Why not?

• @MarkusWinand has a website dedicated to indexing that you could spend days studying.
• The indexing section of Kimberly Tripp's blog is 66 pages long.
• To appreciate the finer detail of the above you need to grasp 60+ pages of material on internals and on-disk structures and a half dozen whitepapers on statistics and the query optimiser.

Since I'm not entirely sure why I follow these guidelines...

This is the problem you need to address. Don't blindly accept recommendations or guidelines without taking the time to understand the why behind them.

Answer 5

Are these good guidelines?

I might not understand your question correctly.

But no, I don't think those are good guidelines. Indexing should be guided by usage patterns. If your users need to find rows that match the value 'ACH-3442-AD-2011-15-A-36' in a VARCHAR(64) column, then an index on a narrower integer column won't help them at all.

For dbms guidelines, I'd say you usually need an index on every column (or set of columns) commonly used in a WHERE clause, a JOIN clause or a FOREIGN KEY constraint.

But indexes are a space/time tradeoff. You increase the use of disk space to reduce the time taken by some subset of your queries.

Now, in a production database, every column might eventually be used in a WHERE clause. But it might not make sense to maintain a 5 gigabyte index on a column that's used in a WHERE clause only once a year, especially if you can predict that usage.

For dba guidelines, I'd say you need to read the CREATE INDEX documentation for your dbms at least once a year. It's by reading the documentation (or, sometimes, here or on StackOverflow) that you learn about (or are reminded about)

• functional indexes,
• partial indexes,
• moving indexes (or tables) to tablespaces on faster SSD disks,
• using a different collation in the index,
• changing the fillfactor

and so on.