CODEWITHSUNDEEP
sql-serverindexing
RakeshP
RakeshP

Reputation: 141

Indexing all columns for a table

I have a huge database which has only select statements on that. And there are different applications which use this. Out of 15 columns, 12 are being used in these applications. Is it recommended to use all these 12 columns in Index ? If No, what is the issue?

Please note, inserts or updates will happen once or twice a week only. And the idea is to delete all these indexes and recreate after the table load.

Thanks for your help.

Upvotes: 0

Views: 187

Answers (2)

Vidya
Vidya

Reputation: 30310

@JustinHui gave some great insight, and since your situation is readonly, you can definitely go for indexing all the columns if you have the space.

But before you do that, test with a smaller but sizable fraction of data. Start with those columns referenced more often than others in WHERE, JOIN, GROUP BY and ORDER BY and see what improvements you find. Keep gradually increasing if needed. I would guess certain indexes are overkill, but only tests would prove it.

Finally, if you want to index everything and space is an issue, you could always look outside the database with Apache Solr. Once you get the hang of it, you could index everything and even provide users with cool faceted searching. And you would only need to rebuild the Solr index once or twice a week.

Hope that helps.

Upvotes: 2

JustinHui
JustinHui

Reputation: 729

It depends on what theses columns are being used for:

Indexing columns uses more disk space, memory and decreases the INSERT and UPDATE speeds as the SQL engine has to update the index.

Indexing gives you big speed gains when you are retrieving data and the indexed fields are used in WHERE, JOIN, GROUP BY or ORDER BY.

If the table is huge, then 12 indexed columns could potentially use a lot of disk space and memory and effectively slow down any retrieving of data. The best thing to do is to use a performance tuner to identify which indexes will give you the most gain.

However it really depends your applications. Although it is unusual to have such a large percentage of columns indexed, it may work in your situation.

Upvotes: 2

Related Questions