CODEWITHSUNDEEP
sql-server
Neil N
Neil N

Reputation: 25258

Can a table be "too small" to have any gain by indexing it?

Lets say I have a table that will never have more than 10 records. Does putting an index on it have any tangible benifit? Would it have negative results?

What about 20 records? 50? 500? At what point does a table actually see tangible benefits of an index, assuming modern, beefy, dedicated server hardware for the database.

Upvotes: 2

Views: 1901

Answers (5)

paparazzo
paparazzo

Reputation: 45096

If the table is in a FK relationship then yes it matters even if it has 3 values if it is reference by a table with 3 million rows. Technically the FK side only needs to have unique constraint. But if it is a clustered PK on just 3 values it has tangible benefits if it is referenced millions of times as it turns a scan into and index seek.

Upvotes: 0

Joshua Carmody
Joshua Carmody

Reputation: 13730

I don't see how a table with just 1 row could possibly benefit from indexing. So, yes.

Upvotes: 1

DaveE
DaveE

Reputation: 3647

For really small tables, indexes will probably not be used for simple access - the cost of a table scan is less than the index lookup followed by the actual data retrieval/reference.

If that table will most likely be used in sub-queries that run against significantly larger tables, index it anyway. The cost of hundreds or thousands of table scans will quickly dwarf any other costs associated with the table.

We manually unrolled a reference to one such table (approx 20 rows) that was used in a correlated subquery against a much larger table. At one client, this query used 4.2 billion reads because it was doing a table scan for every single linked row in the larger table. Unrolling that operation resulted in a 99%+ reduction in reads (approx 380 000 afterwards) and an 18 hour reduction in runtime.

EDIT: Make join reference specific to subqueries.

Upvotes: 2

JohnC
JohnC

Reputation: 842

If the table was really small the query optimizer would probably choose not to use the index. You would be incurring maintenance expense for no benefit. So in that case, it would be too small for any gain. But it's mostly moot, because if the table is that small the maintenance expense wouldn't be large to begin with.

If your table is in the might-help-might-not gray area, my feeling is this: Don't index it unless you have data indicating that it'll help (Profiler or something like it). The memory available to your database server has-- in most cases, not all-- grown relative to the sizes of databases themselves, allowing more of that database to fit in cache. Others of course disagree, and have completely valid arguments.

  • John

Upvotes: 1

JNK
JNK

Reputation: 65177

Like anything and everything SQL, IT DEPENDS.

For a 10 record table you probably will never see the index used. The optimizer will see the difference between a table scan and an index scan as being nil.

For larger tables, it's going to depend. There is no "cut off point" where it becomes beneficial for every table. It will depend on row width, selectivity of the field you index, width of the index, if it's clustered or non-clustered, etc.

I would say if you start having performance issues, see if an index makes a difference. If a table is over 1000 rows I normally index it if I will be joining on it, since the space used and time to create/maintain the index is trivial (assuming you aren't deleting/inserting a lot in a table that small).

Upvotes: 7

Related Questions