Difference between creating a single index on two columns and creating two seperate indexes on those two columns
-
Can anyone explain the difference between creating a single index on two columns and creating two seperate indexes on those two columns. Which one would be efficient.
Thx.
-
Hi!
Let us work with an example:
---
use tempdb
GO
create table threecol (
uid int identity primary key,
val1 int,
val2 int)
GO
create index idxval1 on threecol (val1)
GO
create index idxval2 on threecol (val2)
GO
declare @i int
select @i = 0
while @i < 1000
begin
insert into threecol (
val1,
val2
 
values (
@i,
@i * 2
 
select @i = @i + 1
end
GO
---
You can see that we have a table with two columns, and two indices, one on each column.
If we run the following (stupid) query and profile it:
---
use tempdb
GO
select uid
from threecol
where val2 > 1000
and val1 > 750
and val1 % 2 = 0
---
The execution plan is as follows:
---
Filter(WHERE
[threecol].[val2]>1000)) |--Bookmark Lookup(BOOKMARK
[Bmk1000]), OBJECT[tempdb].[dbo].[threecol]))|--Index Seek(OBJECT
[tempdb].[dbo].[threecol].[idxval1]), SEEK[threecol].[val1] > 750), WHERE[threecol].[val1]%2=0) ORDERED FORWARD)---
You can see that it uses only the first index on idxval1, and not the one on idxval2.
Let us add an index on the two columns:
---
create index idxval12 on threecol (val1, val2)
---
And rerun the same query. The execution plan now is:
---
Index Seek(OBJECT
[tempdb].[dbo].[threecol].[idxval12]), SEEK[threecol].[val1] > 750), WHERE[threecol].[val2]>1000 AND [threecol].[val1]%2=0) ORDERED FORWARD) ---
This time, the two-column index is used. It is straightforward to see that this second execution is simpler, and has better performance.
So, don't bet that SQL Server will use your two indices on the two different columns to make your query. It will preferably select the better index (the one that returns the minimum number of rows), and them match the first result set to the clustered index, to calculate the final result.
On the other hand, if you have queries targeting a single column sometimes, and both columns some other times, having multple indices may help you.
There is no rule of thumb: try what seems natural, and use the profiler to check your results.
HTH,
Xavier
-
Hi xavier,
Thx for ur reply. But here in my case sometimes I use the query on a single column and some other times I use for more than one column. So as per ur suggetion it's better to go with multiple indexes.
Thx.
-
check out BOL - Designing an Index - what you need is a composite index.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply