Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've found a lot of resources that mention that adding an index to a table makes searches faster and inserts slower, but only if the table is large. This creates a tradeoff, which is a design decision, but there should be an approximate table size before which using an index is absurd. (10 rows, for example, is probably way beneath that limit)

Does anybody know about where this limit would be, or know of a resource that would point me in the right direction?

share|improve this question
    
What is the reads/writes ratio for your application? If you're really write intensive, then maybe it's the point where you need to take into consideration the writes tradeoff, but if it's an usual application, I'd add the needed index in 99% cases (the tables usually grow, they hardly go back in size). –  Marian Jan 17 '13 at 17:08
add comment

2 Answers 2

up vote 12 down vote accepted

The exact limit is really hard to determine ahead of time.

One thing most people underestimate is the high requirements that an index must fulfill, before it becomes a candidate to be used in a query.

An efficient (nonclustered) index

  • offers great selectivity, e.g. returns only a very small percentage (< 1%, < 2%) of the total rows. If the selectivity is not a given - SQL Server's query optimizer will most likely ignore this index

  • should ideally cover the query, i.e. return all teh columns required by the query. If you can create an index that has 1 or 2 index columns, and includes another handful (2-4) columns as included columns and thus you can cover a query - then chances are the query optimizer will use this index. Which also means: if your code is always using SELECT * ..... to fetch all columns, the likelihood of indices being used goes down - quite dramatically, actually

I'm sure there are a ton of other criteria as well - but I would believe these two are the most critical ones. Of course, you should always keep your indices properly maintained (reorganize, rebuild) and make sure the statistics associated with your indices are up to date.

PS: nonclustered indices on foreign key columns are a special case; by default, I would always recommend adding those, since they help speed up both referential integrity checks, as well as JOIN's on those FK constraints. But even here, it's absolutely valid to "extend" those FK column indices by adding some additional "include" columns to make them even more useful.

share|improve this answer
2  
While this answer may not directly answer the question, it does much better by giving the important design principles for the index, and answers the question that I should have asked in the first place. –  SeanVDH Jan 17 '13 at 16:52
add comment

You might see an improvement from an index with only 10 rows.

In the following test on my machine the version without an index completed in 10.5 seconds and the version with an index in 9.8 seconds (consistent over 3 runs).

The index in this case only consists of 1 leaf page but as the slot array is ordered in index key order it's presence allows SQL Server to just return the single row of interest rather than perform an aggregation on all 10.

CREATE TABLE T
(
X INT,
Y CHAR(100) NULL
)

INSERT INTO T (X)
SELECT number 
FROM master..spt_values
WHERE type='P' AND number BETWEEN 1 AND 10

set nocount on;

DECLARE @I INT, @X INT

DECLARE @Time DATETIME2(7) = SYSUTCDATETIME()

SET @I = 1
    WHILE (@I < 1000000)
    BEGIN
    SELECT @X = MAX(X)
    FROM T
    SET @I += 1
    END

SELECT DATEDIFF(MICROSECOND, @Time, SYSUTCDATETIME())

CREATE CLUSTERED INDEX IX ON T(X)
SET @Time = SYSUTCDATETIME()
SET @I = 1
    WHILE (@I < 1000000)
    BEGIN
    SELECT @X = MAX(X)
    FROM T
    SET @I += 1
    END

SELECT DATEDIFF(MICROSECOND, @Time, SYSUTCDATETIME())

DROP TABLE T
share|improve this answer
    
Are inserts affected similarly, or is the slowdown minimal? –  SeanVDH Jan 17 '13 at 16:02
    
@SeanVDH - The example in my answer is comparing a clustered index to a heap. It would stand to reason that inserts between existing rows would be slower as the rows have to go into a specific place and the slot array rewritten also possibility of page splits. For larger inserts the data may be sorted into CI key order too which is unnecessary when inserting to a heap. Kimberley Tripp argues here though that sometimes inserting to a CI can be better than inserting to a heap. –  Martin Smith Jan 17 '13 at 16:12
    
Thank you for the article, she presents some interesting points. I was wondering if the inserts would be affected as dramatically as the selects in the small table, but you're right, the tradeoff should be similar at the beginning as it would be later. –  SeanVDH Jan 17 '13 at 16:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.