Index on query with both where clause and order by

What is best index for this query

Select * from Table1 whete Table1.C1 >100 and Table1.C2=23 order by Table1.C3

How many index do I need and what column order?

I would appreciate if some body can put link to complex query index guideline

Thanks for help.

It's difficult to give an optimal answer without first gaining an understanding of the distribution and volume of your data. For instance if your table contains only one row then a full table scan will be the most efficient way of returning the results. If your table contains millions of rows, yet 99% of the rows satisfy the criteria (Table1.C1 > 100 AND Table1.C2 = 23) then a table scan is still likely to be the most efficient way of obtaining the data.

As a first step, have you tried running the query past either the 'Index Tuning Wizard' or the 'Database Tuning Advisor'?

Chris

|||

Do you actually need all of the columns to be returned in the SELECT statement? Depending on the answer to that, you may be able to use a "covering index", that covers the query without having to access the base table.

In your case that means a single index would have C3, C2, C1, and all of the columns in the SELECT list included. Depending on how many columns are in the SELECT list, this may or may not be feasible. The new INCLUDED columns feature in SQL Server 2005 gives you a lot more flexibility there. You also have to consider how volatile the data is.

As far as column order for the index, it depends on the selectivity of the data in the columns. Most often, the columns in the WHERE clause are the most important (i.e. they are the first columns in the index).

I would try creating two indexes, one with C1, C2, C3, and the second with C2, C1,C3 to start.

Really, you just need to fire up a Query window in SSMS, run SET STATISTICS IO ON, turn on the graphical execution plan, and then run the query a few times with different input values, and see which gives the best results.

You would also want to parametize the query or make it a stored procedure with input parameters, so that you get one copy of it in the procedure cache. If it is hard-coded with literal values or submitted ad-hoc that way, you will have multiple copies of it in the procedure cache.

http://glennberrysqlperformance.spaces.live.com