Clustered Index with multiple columns

[webuser]

I want to know if there is a "Best Pracice" to what I am doing.

I have a table with several fields and I know that 2 of the fields should ideally be sorted for best performance, so I added a cluster index that uses both of those fields.

What I'd like to know is, do I need to add additional individual indexes on both or either one of those fields? Will they help? Will they potentially SLOW things down?

Thanks in advance!!!

 

[Gixonita]

Hello,

A clustered index is not only a sort process, its a usable index, it would be redundant to add more indexes on the same fields and would degrade performance.

Hope this helps

 

[webuser]

What if the index is on two columns, for example, a last name and then a first name. So on disk, the table would be sorted with all the last names first, and than the first names. Now, what if I had many queries that needed to find the First name for some reason, wouldn't an additional index on just the first name be helpful?

 

[fredericofonseca]

That depends only on what use you/your users make of the data.

I have seen many applications where the master cluster index was never used, and the most common WHERE was not using any index.

So before adding any indexes (other than the absolute required to give a unique identity to each record) look at how the data is going to be accessed, how often per each type of query, and then base the indexes on that information.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[webuser]

In my example, I mentioned a clustered index on Lastname then Firstname. If I tell you that the application has many many WHERE clauses and/or joins on the first name column, I want to know if an additional index on the First Name column would be helpful or if the clustered index (on Last Name, then First Name) would be enough and the additional index redundant. Please let me know if you have any additional thoughts on the subject. Thnaks.

 

[gmmastros]

webuser

If you test your query in Query Analyzer, then I recommend you press CTRL-K before running your query. CTRL-K will cause Query Analyzer to display an execution plan. There will be a new tab (at the bottom of the window below where the results are shown).

You will see a bunch of icons representing the execution plan. When hovering your mouse over an icon, you will get more information regarding what was happening during that stage of the execution plan.

My best suggestion would be to run the query without the additional index. Then look at the execution plan. Then, add an additional index and re-run the query. Look at the execution plan again to determine if the new index was used.

With a little dilligence, you should be able to tweak the indexes for optimal performance. Please consider, though, that the more indexes you have, the slower your performance will be on inserts, updates and deletes. Creating indexes is a balancing act between fast performance for selects and fast performance for inserts, updates and deletes.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[webuser]

Thanks. That's a good suggestion. I will try it.