|
Home > Archive > MS SQL Server > March 2006 > index included column help other indexes lookup?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
index included column help other indexes lookup?
|
|
|
| example:
table: employee (name, age and gender)
indexes: age and gender
query: select name from employee where age = 30 and gender = 'm'
question: when i create the index on the age column, would including the
gender column makes the query faster? or including the age column in the
gender index?
thanks.
| |
| Evergray 2006-03-09, 7:23 am |
| Index on gender is useless (if gender can be only 'm' or 'f', as it usually
does on this planet ;-)) because of it's very low selectivity. So answer
is - one (nonclustered) index on 'age', 'gender' or clustered index only on
'age' column. Nonclustered index on 'age', 'gender' and 'name' columns will
be more faster because it covers the query. In SQL Server 2005 index on
(age, gender) with name included will be even more faster and will take less
disk space.
--
WBR, Evergray
--
Words mean nothing...
"tin" <tin@discussions.microsoft.com> wrote in message
news:13822F9F-8BC0-439C-A204- AE3815A0D9D0@microso
ft.com...
> example:
>
> table: employee (name, age and gender)
> indexes: age and gender
> query: select name from employee where age = 30 and gender = 'm'
>
> question: when i create the index on the age column, would including the
> gender column makes the query faster? or including the age column in the
> gender index?
>
> thanks.
| |
|
| great. thanks a lot :-)
"Evergray" wrote:
> Index on gender is useless (if gender can be only 'm' or 'f', as it usually
> does on this planet ;-)) because of it's very low selectivity. So answer
> is - one (nonclustered) index on 'age', 'gender' or clustered index only on
> 'age' column. Nonclustered index on 'age', 'gender' and 'name' columns will
> be more faster because it covers the query. In SQL Server 2005 index on
> (age, gender) with name included will be even more faster and will take less
> disk space.
>
>
> --
> WBR, Evergray
> --
> Words mean nothing...
>
>
> "tin" <tin@discussions.microsoft.com> wrote in message
> news:13822F9F-8BC0-439C-A204- AE3815A0D9D0@microso
ft.com...
>
>
>
|
|
|
|
|