When should you consider indexing your sql tables?

Question

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

Answer 1

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 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.