Posted by Erland Sommarskog on 03/24/06 00:33

Chris Weston (chrisweston[losethislot]@ntlworld.com) writes:
> Maybe I'm just being dim, but I'm struggling to get my head around
> optimising a query with regard to indexes. If I make a select query, such
> as a pseudo-example 'select * from bigtable where foo='bar' and
> (barney>rubble and fred<flintoff)', and the table is indexed on 'foo', how
> could I make that any better? What indexes could I add, or what could I
> change in the query?
>
> I know it looks simple, but so am I.

First of all, it matters what index on 'foo' that you have. Is that a
clustered index or a non-clustered index? For this query a clustered
index is is likely to be better, but since you only can have one clustered
index on a table, there may be better choices for other queries.

It's unclear to me what

(barney>rubble and fred<flintoff)

is supposed to mean, but I assume that barney and fred are columns and
'rubble' and 'flintoff' are values.

It's difficult to cover this condition well in a single index. I don't
thinks it much use to include both in the clustered index, but you should
pick one and make it (foo, barney) or (foo, fred).

If you have to use non-clustered indexes is a little different.
(foo, barney, fred) is proabbly more effective than (foo, barney),
because SQL Server does have to access the data pages to check
the condition on fred.

Yet an idea, is to have (foo, barney) and (foo, fred) and see if
SQL Server may use index intersection.

As for changing the query, that's difficult, because I don't know what
it is supposed to mean.

Overall, it's difficult to give generic advice for performance issues,
since there are a lot of "it depends".

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]