March 3, 2006 at 10:19 am
MyTable (labeled "TBL1") contains 1M rows. Need best suggestion for creating an index where predicated columns are spread across the INNER JOIN -and- WHERE clauses. SQL:
SELECT TBL1.col_1,
TBL1.col_2,
TBL2.col_444,
TBL2.col_555
FROM MyTable TBL1 INNER JOIN AnotherTable TBL2 ON TBL1.col_1 = TBL2.col1
WHERE TBL1.COL_666 = 'ABC'
AND TBL1.COL_777 = 'XYZ'
I want to CREATE an index on MyTable (TBL1). Which index is better suited to accomodate this query:
create index IDX1 on dbo.MyTable (Col_1, Col_666, col_777)
-OR-
create index IDX1 on dbo.MyTable (Col_1)
create index IDX2 on dbo.MyTable (Col_666, col_777)
March 3, 2006 at 10:34 am
Impossible to say really without knowing what the distribution of data is like in each of the columns. If more than about 10,000 rows in COL_666 match the value 'ABC' then there's not much point in having it as the first column in your index, especially if it's non-clustered as in your example. The data won't be selective enough for the index to ever be used. Ditto for COL_777.
As a general rule, I'd probably go for 2 indexes - one on the columns in your WHERE clause (with the column with the highest cardinality as the leading one in the index), and one on Col_1. This gives the optimizer the choice of first restricting the number of qualifying rows in TBL1 as if it can before passing the intermediate result set into the join.
Best suggestion of all is to try it and check the query plans (no need to run the query). Building non-clustered indexes shouldn't take too long on a 1M row table.
March 3, 2006 at 11:51 am
Why not use that query and run it through Index Analyzer? You don't have to accept the results, just see what it recommends.
Mark
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply