April 21, 2009 at 3:20 am
Everyone,
I have a problem regarding creating Indexes on a table. i have 10 columns in my table and we have many searching queries which can use every column of this table. I want index my table, but on which column?
Do i create seperate 10 indexes on every column? is it a good approach?
and what could be the possible harms if i do this?
Thanks,
Noman
April 21, 2009 at 4:08 am
If you have a lot of inserts/updates/deletes happening, then you could take a hit as each index would have to be updated when these happen.
Also you would have increased filesize as more indexes will take up more space.
Are all the fields searched upon separately, or in combination - for example "WHEN col1 = 3 and col2 = 'abcd'"
Other considerations are the uniqueness, or not, of the data in the columns.
April 21, 2009 at 4:12 am
Thanks for your reply, Actually its an archived table so no direct insert and updates on it all the time. we use it for searching only.
but in where clause we may have a single column or we may have the combination of our columns in our queries.
April 21, 2009 at 4:25 am
If it's only selects, and you have the space for more indexes, I'd certainly consider adding them.
Of course, as with all these things: testing, testing, testing 🙂
Edit: and of course you'd have to make sure that each field was being searched on individually, otherwise combined column indexes would be the best option
April 21, 2009 at 4:26 am
Thankyou Atif,
Need recommendations please??
which approach is the better in this case.
--Noman
April 21, 2009 at 4:30 am
Noman Tariq (4/21/2009)
Do i create seperate 10 indexes on every column? is it a good approach?
That's only really useful if you have queries that filter on only one column at a time. If your queries filter on multiple columns, it's far better to go for multi-column indexes. SQL's quite unlikely to use 2 or more nonclustered indexes on a table to satisfy a query.
Have a read through this - http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 21, 2009 at 4:42 am
You's need to analyze the queries that are being used and then create the indexes accordingly. The link below would be a good place to start off with indexes:
http://www.sqlteam.com/article/sql-server-indexes-the-basics
April 21, 2009 at 4:44 am
Seconding the Gila, i will suggest the covering indexes. Also you may analyze the SQL Server recommendations about missing indexes http://blog.dbdigger.com/2009/04/get-information-about-missing-indexes.html
DBDigger Microsoft Data Platform Consultancy.
April 21, 2009 at 4:57 am
AShehzad (4/21/2009)
Also you may analyze the SQL Server recommendations about missing indexes http://blog.dbdigger.com/2009/04/get-information-about-missing-indexes.html
Just bear in mind that the missing index info is a suggestion. It's not always the best index, it's often way too large, it doesn't take into account other suggestions or existing similar indexes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 21, 2009 at 4:59 am
i am not in a favour of recommending covering index. As according to your information you are fetching a records by specifying single column filter. by creating separate index will be the solution for your problem.
Manoj
April 21, 2009 at 7:52 am
The one index per column solution, better saying one bitmap index per dimension key column on fact tables is a fairly accepted, pretty useful solution for Oracle data warehouse environments because of Oracle's Star Transformation feature.
In the SQL Server world -as far as I know - you better examine your ten most used queries against the target table and custom design the indexes that would better serve those queries.
Over time you keep an eye on queries hitting the table and fine tune your original indexing strategy.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 21, 2009 at 8:18 am
The one thing no one seems to have mentioned is the selectivity of the data. Just because there are 10 columns and just because each of the 10 is used in an individual search doesn't mean that each of the 10 becomes a good candidate for indexing. You may have a column with only four values across 10,000 or more rows. That index is unlikely to ever be used in a seek and scans against it will be very costly. So, no, all 10 columns should not be indexed just because.
All the other advise of determining the querying methods advocated by Gail, Paul, mazzz, and Atif are the way to go.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 21, 2009 at 8:30 am
Grant Fritchey (4/21/2009)
The one thing no one seems to have mentioned is the selectivity of the data. J
"selectivity" - that's the word, it's been driving me crazy all day trying to remember it!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply