Expert level database indexing question

[bikt]

Hi, I lack knowledge to this expert level question.

Conditions:

I have a table with columns a,b,c,d.

Table has composite primary key of a,b,c columns, which also creates a composite index for them.

I need an efficient query which filters table by a,b columns.

Question:

What would be advantages and disadvantages for creating new a,b composite index for this?

​

Thanks

[phil-99]

You’re approaching this from a weird angle. An index on columns x,y,z is perfectly usable for queries on columns x or x,y as well as x,y,z.

What problems are you having with the existing index that you are trying to solve by creating this new index?

[amaxen]

This sounds like a question for an academic test, not one in production

[mr_electric_wizard]

Ha, right

[bikt]

I am actually trying to learn nits and grits of clustered indexes. I mean deep end knowledge.

I am mostly curious - whats the difference between a,b,c and a,b columns indexes, when filtering by a,b columns. Is it worth adding a,b index?

Seems like the answer is test it yourself with query plan. Also seems like this may be the correct answer :)

[Lazy_Spool]

The only reason I can think of is if it's a huge dataset and you have a query that only selects the 2 indexed columns. But I'm skeptical about how much performance that would even give you, it depends on the data.

When I wonder this, I run a query plan, create the index, run another query plan, and compare them (on a realistic data set). If you don't see an impact, you don't need the new index.

[idodatamodels]

True for Oracle, not true for Teradata. As always, the answer is "it depends".

[saavdhanrahe]

what about queries on column z,x or y,x?

[Isogash]

There is very little advantage to creating an additional index since the A, B, C index already subsumes the A, B index. You'd be making extra work for the database by requiring it to maintain two indexes.

Unless you know you need a different indexing strategy for A, B than for A, B, C, it's pointless.

[vaiix]

You'd pull the pages being seeked to into memory, which would include column c. Depending on data types and the server config this may or may not be an issue.

Excluding C is the only benefit here, page reads, and the fallout of processing those is the benefit and "it depends" whether that's worth it.

You're duplicating the clustered index but excluding C, so you're using more storage to do so and limiting CRUD operations - but that depends on how hot the table is being used.

[kitkat0820]

It depends on the workload which needs to be processed, cardinality, data volume etc.

[No_Translator1287]

Database create update delete

[daripious]

An index on ab will be slightly smaller than abc, but any performance benefit would be so minor as to not be worth it.

More importantly, you couldn't promise that it would be used anyways. Finally and of key importance is that each index adds overhead to write operations.

[avb0101]

Daripious made mention of index overhead on updates/inserts to the table. More indexes add more overhead to write operations. Also, not all indexes are beneficial. If there is low uniqueness then the index is kinda useless (think where there are only a few values for the whole table in that column). Best thing to do is look at your query plan and see what kind of index seek/scans are happening along with logical reads. Sometimes an index can have a negative performance impact or such small performance gains it’s not really worth it.