Monday, March 12, 2012

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.
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@.microsoft.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@.microsoft.com...
>
>

No comments:

Post a Comment