EXPLAIN EXTENDED

How to create fast database queries

Archive for June 22nd, 2009

Indexing tiny tables

Comments enabled. I *really* need your comment

From Stack Overflow:

Hypothetically, in a SQL Server database, if I have a table with two INT fields (say a many-to-many relation) that participates in joins between two other tables, at what approximate size does the table become large enough where the performance benefit of indexes on the two INT fields overcomes the overhead imposed by said indexes?

Very nice question.

Imagine we have a table table1 (col INT NOT NULL, value1 INT NOT NULL, value2 VARCHAR(100) NOT NULL, …) with an index on col.

In SQL Server, an index can improve the following things.

  1. Index lookups (range scans). An index can be used to limit the number of rows scanned:

    SELECT  *
    FROM    table1 t1
    WHERE   t1.col BETWEEN @start AND @end
    
  2. Secondary data source. This query will get all data it need from the index:
    SELECT  t1.col
    FROM    table1 t1
    
  3. Ordered rowsets. This query will not need to sort:
    SELECT  *
    FROM    table1 t1
    ORDER BY
            t1.col
    
  4. Efficient MIN / MAX:
    SELECT  MIN(col)
    FROM    table1 t1
    

The latter case is probably the best illustration of the fact that even a table of 2 rows can benefit from creating an index.

Let's create the sample tables:
Read the rest of this entry »

Written by Quassnoi

June 22nd, 2009 at 11:00 pm

Posted in SQL Server