When should you consider indexing your sql tables?

Question

How many records should there be before I consider indexing my sql tables?

Answer 1

When the query time is unacceptable. Better yet, create a few indexes now that are likely to be useful, and run an EXPLAIN or EXPLAIN ANALYZE on your queries once your database is populated by representative data. If the indexes aren't helping, drop them. If there are slow queries that could benefit from more or different indexes, change the indexes.

You are not going to be locked in to an initial choice of indexes. Experiment, and make sure you measure performance!

Answer 2

I would create the index entries when I create my table. If you decide to create indices after the table has grown to 100, 1000, 100000 entries it can just take alot of time and perhaps make your database unavailable while you are doing it.

Think about the table first, create the indices you think you'll need, and then move on.

In some cases you will discover that you should have indexed a column, if thats the case, fix it when you discover it.

Creating an index on a searched field is not a pre-optimization, its just what should be done.

Answer 3

There's no good reason to forego obvious indexes (FKs, etc.) when you're creating the table. It will never noticeably affect performance to have unnecessary indexes on tiny tables, and it's good to take a first cut when your mind is into schema design. Also, some indexes serve to prevent duplicates, which can be useful regardless of table size.

I guess the proper answer to your question is that the number of records in the table should have nothing to do with when to create indexes.

Answer 4

In general I agree with the previous advice. Always declare the referential integrity for the tables (Primary Key, Foreign Keys), column constraints (not null, check). Saves you from nightmares when apps put bad data into the tables (even in development). I'd consider adding indexes for the common access columns (columns in your where clauses which are used in =, <> tests), as well. Most of the modern RDBMS implementations are quite good at keeping you indexes up to date, without hitting your performance. So, the cost of having indexes is minimal. Also, most RDBMS's have query plan evaluators which look at the relative costs going to the data rows via the index, or using some sort of table scan. So, again the performance hits are minimal.

Answer 5

It depends.

How much data is in the table? How often is data inserted? A lot of indexes can slow down insertion time. Do you always query all the rows of the table? In this case indexes probably won't help much.

Those aren't common usages though. In most cases, you know you're going to be querying a subset of data. ON what fields? Are there common fields that are always joined on? Look at query plans for common or typical queries, it will generally show you where it's spending all of its time.

Answer 6

As a matter of routine I perform the following on read heavy tables:

• Create indexes on common join fields such as Foreign Keys when I create the table.
• Check the query plan for Views or Stored Procedures and add indexes wherever a table scan is indicated.
• Check the query plan for queries by my application and add indexes wherever a table scan is indicated. (and often try to make them into Stored Procedures)

On write heavy tables (like activity logs) I avoid indexes unless they are absolutely necessary. I also tend to archive such data into indexed tables at regular intervals.

Answer 7

If there's a unique constraint on the table (and there should be at least one), then that will usually be enforced by a unique index.

Otherwise, you add indexes when the query performance is bad and adding the index will demonstrably improve the performance. There are books on the subject of how to create good sets of indexes on tables, including Relational Database Index Design and the Optimizers. It will give you a lot of ideas and the reasons why they are good.

See also:

• No indexes on small tables
• When to create a new SQL Server index
• Best Practices and Anti-Patterns in Creating Indexes

and, no doubt, a host of others.

Answer 8

Two.

I'm serious. If there are two rows now, and there will always be two rows, the cost of indexing is almost zero. It's quicker to index than to ponder whether you should. It won't take the optimizer very long to figure out that scanning the table is quicker than using the index.

If there are two rows now, but there will be 200,000 in the near future, the cost of not indexing could become prohibitively high. The right time to consider indexing is now.

Having said this, remember that you get an index automatically when you declare a primary key. Creating a table with no primary key is asking for trouble in most cases. So the only time you really need to consider indexing is when you want an index other than the index on the primary key. You need to know the traffic, and the anticipated volume to make this call. If you get it wrong, you'll know, and you can reverse the decision.

I once saw a reference table that had been created with no index when it contained 20 rows. Due to a business change, this table had grown to about 900 rows, but the person who should have noticed the absence of an index didn't. The time to insert a new order had grown from about 10 seconds to 15 minutes.