When should you consider indexing your sql tables?

Question

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

Answer 1

There is no reason to discard obvious indexes (FK, etc.) when creating a table. This will never noticeably affect performance, to have unnecessary indexes on tiny tables, and it’s good to take the first shot when your mind is in circuit design. In addition, some indexes serve to prevent duplication, which may be useful regardless of the size of the table.

I assume the correct answer to your question is that the number of records in a table should not have anything to do with creating indexes.

Answer 2

I create index entries when creating my table. If you decide to create indexes after the table has grown to 100, 1000, 100000 records, it can simply spend a lot of time and, possibly, make your database inaccessible while you do it.

Think of a table first, create the indexes you think you need, and then go.

In some cases, you will find that the column has been indexed, if so, correct it when you find it.

Creating an index in the desired field is not a preliminary optimization, but exactly what needs to be done.

Answer 3

When the request time is unacceptable. Even better, now create some indexes that might be useful, and run the EXPLAIN or EXPLAIN ANALYZE query when your database is filled with representative data. If indexes do not help, leave them. If there are slow queries that can benefit from larger or different indexes, change the indexes.

You will not be tied to the initial choice of indexes. Experiment and make sure you measure performance!

Answer 4

In general, I agree with the previous tip. Always declare referential integrity for tables (primary key, foreign keys), column constraints (nonzero, validation). It saves you from nightmares when applications put bad data in tables (even in development). I would think about adding indexes for shared columns (columns in your sentences that are used in =, <> tests). Most modern RDBMS implementations do a good job of updating indexes without impacting performance. Thus, the cost of indices is minimal. In addition, in most DBMSs there are query plan evaluators who consider the relative costs that go to the rows of data through the index, or using some kind of table scan. So again, performance hits are minimal.

Answer 5

Two.

I'm serious. If now there are two lines, and there will always be two lines, the cost of indexing is almost zero. This is indexed faster than thinking about whether you need to. The optimizer will not take very long to understand that scanning a table is faster than using an index.

If there are two lines now, but in the near future there will be 200,000, the cost of indexing can become prohibitive. The right time to consider indexing is now.

Having said that, remember that you automatically get the index when declaring the primary key. Creating a table without a primary key requires most problems. Thus, the only time you really need to consider indexing is to specify an index that is different from the index of the primary key. You must know the traffic and expected volume for this call. If you make a mistake, you will find out, and you can cancel the decision.

I once saw a referenced table that was created without an index when it contained 20 rows. Due to a business change, this table grew to 900 rows, but the person who was supposed to notice the lack of an index did not. The insertion time of a new order increased from approximately 10 seconds to 15 minutes.

Answer 6

It depends.

How much data is in the table? How often is data inserted? Many indexes can slow down insertion time. Do you always query all rows of a table? In this case, the indices probably will not help.

However, these are not ordinary customs. In most cases, you know that you are going to request a subset of the data. What fields? Are there common fields that are always joined? Look at query plans for regular or typical queries, it will usually show you where it spends all its time.

Answer 7

As a routine, I do the following when reading large tables :

• Create indexes in 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 the table scan is indicated. (and often try to make them into stored procedures).

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

Answer 8

If there is a unique constraint for the table (and there must be at least one), then this will usually be enforced by a unique index.

Otherwise, you add indexes when query performance is poor, and adding an index will clearly improve performance. There are books on creating good sets of indexes on tables, including a relational database and optimizers . This will give you many ideas and reasons why they are good.

See also:

• No pointers on tables
• When to Create a New SQL Server Index
• Recommendations and anti-patterns for creating indexes

and no doubt many others.