x

Indexes with low cardinality columns decreases the performance ?

Hi All,

In my project most of the indexes having / includes low cardinality columns (like is_deleted_ind = 'Y' / 'N' ) and some of the indexes starting with this column, is it decreases the database performance.

Kindly help me on this.

Regards, satya

more ▼

asked Mar 15 '12 at 08:32 AM in Default

satya gravatar image

satya
361 18 18 21

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Well it will depend upon the nature of the queries. But generally, it is better to have low cardinality columns as the last columns of the index and the highest cardinality column at the start of the index. This way the selectivity of the row becomes easier and hence better performance can be gained. So, I would say it is not the matter of performance decrement but it is more the case of there could be room for more performance gain.

Moreover, filtered indexes could be another suitable option depending upon your environment.

Here are two examples where the Low cardinality column could be bad as well as beneficial. You have to run the data population scripts in one batch and the examples in separate batches. You would be able to see the difference of cost in execution plans as well as through STATISTICS.

/*============== DATA POPULATION PORTION ======================================================*/

CREATE TABLE #temp
(
EmployeeId INT IDENTITY PRIMARY KEY,
EmployeeName VARCHAR(20),
EmployeeDepartment INT,
IsDeleted BIT
)

SET NOCOUNT ON;

INSERT [#temp]
        (
         [EmployeeName]
        ,[EmployeeDepartment]
        ,[IsDeleted]
        )
SELECT 'XYZ'+ CAST(ROWNUM AS VARCHAR(20)), [ROWNUM]/50 + 1, CASE WHEN [ROWNUM] % 100 = 0 THEN 1 ELSE 0 END
FROM
(
SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) ROWNUM
FROM sys.all_columns AS AC CROSS JOIN sys.all_objects AS AO
) A

CREATE INDEX IX_BIT ON [#temp]([IsDeleted])
CREATE INDEX IX_Name ON [#temp]([EmployeeName])
CREATE INDEX IX_Name_Covering ON [#temp]([EmployeeName],[IsDeleted])

GO

/*============ FIRST SCENARIO WHEN THE INDEX ON LOW CARDINALITY COLUMN COULD NOT BE THE RIGHT CHOICE ===================*/
/*====== THIS IS JUST TO LET YOU KNOW THAT THERE MAY BE A POSSIBLITY THAT OPTIMIZER MAY HIT THIS INDEX ========================*/

SELECT * FROM [#temp] AS T WITH (INDEX (1))
WHERE [IsDeleted] = 0

SELECT * FROM [#temp] AS T WITH (INDEX(IX_BIT))
WHERE [IsDeleted] = 0


GO



/*===== SECOND SCENARIO WHEN ADDING THE LOW CARDINALITY COLUMN COULD BE BENEFICIAL TO MAKE A COVERING INDEX ========*/
SELECT [EmployeeId]
,       [EmployeeName]
,       [IsDeleted]
FROM [#temp] AS T WITH (INDEX(IX_Name))
WHERE [IsDeleted] = 0 
AND [EmployeeName] = 'XYZ1022'

SELECT [EmployeeId]
,       [EmployeeName]
,       [IsDeleted]
FROM [#temp] AS T WITH (INDEX(IX_Name_Covering))
WHERE [IsDeleted] = 0 
AND [EmployeeName] = 'XYZ1022'


--DROP TABLE [#temp] --CLEANUP

But as I said earlier, everything would depend upon your environment and you have to test whether it suits you or not.

more ▼

answered Mar 15 '12 at 09:06 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(comments are locked)
10|1200 characters needed characters left

Indexes with low cardinality may be ignored by the optimizer as they offer no assistance in generating a query plan for efficient execution of your query.

Where they can affect performance, is that every update/insert that affects the data in that index is extra overhead in the operation, for very little gain further down the line.

There is also the additional storage space needed.

All, or none, of these may be a concern in your environment - you need to test the different scenarios and determine your own conclusions.
more ▼

answered Mar 15 '12 at 09:10 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

(comments are locked)
10|1200 characters needed characters left
Another thought to keep in mind is whether you are actually using the column as a predicate in your WHERE clause. No use in maintaining it unless it is a needed element.
more ▼

answered Mar 15 '12 at 01:55 PM

Blackhawk-17 gravatar image

Blackhawk-17
11.8k 28 30 35

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x122

asked: Mar 15 '12 at 08:32 AM

Seen: 2988 times

Last Updated: Mar 15 '12 at 02:14 PM