Optimize query selection

Question

I am using SQL 2000 and I am running a simple select statement on a table containing about 30 million rows. The selection request looks like this:

select col1, col2, col3 from Table1 where col4=@col4 and col5=@col5 and col6=@col6 

The table has a clustered index (i.e. primary key), but this is not used as criteria. All criteria mentioned above are not indexed in them.

How can I optimize this query?

If I add indexes for each column in the where clause, will this have any meaning?

If I have 10 columns in the where clause, should all of these 10 columns have an index in them?

Edit: This is probably one of the most common interview questions :)

Answer 1

Yes, that will make a huge difference.

Instead of adding one index for each field, you should add one index that has three fields. (As it is usually used in practice, it depends on how unique the fields are and what other queries you intend to use in the table.)

Note that adding an index also has a slight negative effect when inserting or deleting records in a table or updating indexed fields of a record.

Answer 2

How can I optimize this query?

You can make a coverage index:

 CREATE INDEX ix_table1_456__123 ON table1 (col4, col5, col6) INCLUDE (col1, col2, col3) 

and the query doesn’t even have to search the table.

If I add indexes for each column in the where clause, will this have any meaning?

This is likely to improve the query compared to no indexes at all, but creating a composite index with coverage is likely to be better.

However, if each of your columns has high power (i.e., or next to UNIQUE ), then creating separate indexes can even improve the query compared to a composite index.

This is especially true if some of the columns are large (for example, VARCHAR(400) ), and another, small column has high power.

If I have 10 columns in the where clause, should all of these 10 columns have an index in them?

If you have columns 10 , there is, as I said, a trade-off between increased key size (which degrades performance) and increased selectivity.

If, say, the first columns of 3 unique or almost unique, then adding additional columns will not increase selectivity, but will increase the key size.

The index will become larger, which will require additional time to search in it.

You cannot create an index in all columns 10 if columns 3 offer selectivity that is high enough since moving a larger index will be more expensive than reading some additional keys.

You can read this article on your blog:

• Creating Indexes

Answer 3

If I add indexes for each column in the where clause, which will make any difference?

Yes, adding an index will make a huge difference in performance. This is due to the significant use of disk space by index and very little impact on the INSERT and UPDATE commands.

If I have 10 columns in the where section, if all these 10 columns are index in them?

It is not always so. If we take the SQL query you presented as an example and you create an index for col4 only, it is possible that select * from Table1 where col4=@col4 returns only a few records. In this case, you are unlikely to benefit from the index on col5 and col6, because the database engine will only need to scan those few records that were returned where col4=@col4 .

Therefore, as you can see, it depends on the type of data that you store. In addition, the index may also not be very useful for any low power column: that is, columns with several unique values.

Answer 4

Which column of criteria is the most selective? Creating an index in this column will affect performance the most. Whether you add other columns to the same index or not, it depends on selectivity. You need to study query plans to find out :)