October 19, 2004 at 9:27 am
Hello,
I have a db where I have a table with mobileID, accID, period, year, store, region, transaction and name. A user could make searches on any of these fields and also combine different fields to do the search. For ex: he could search for store. then he could search for Store+mobileID+period. How do you go about indexing a table in this situation so the results return as quickly as posible.
Also this is a common table, transCommon (holds common fields from different transactions) and all the uncommon fields from different transactions have their own table, transXXX. in transCommon I only have a clustered index (transCommon_ID - PK) and In tables transXXX I have transXXX_ID (PK) and transCommon_ID (FK also indexed).
After all this, I also have to run reports in which I need to get data from both tables (i have created views to recreate the full transaction). I have to run reports to summarize based on year+period+region, year+period+store, year+period+transaction etc.
Your help will be much appriciated.
October 19, 2004 at 12:27 pm
Do you know the selectivity of the various fields? If some are more selective, i.e., a query will return very few rows, then these are great candidates for the first key of compound indexes. You also should do some research into which ones are most often queried by (or which combinations) and index for those. If the table doesn't change too much, I'd index them all.
October 20, 2004 at 11:30 am
I would suggest creating 8 indexes, one for each of those columns. However the users query, SQL should use one of those indexes.
If you know that they will frequently query on columns a, b, & c, for example, then you should also create an index that covers all 3 of those. You would need to specifiy the columns for that index in the order of their cardinality in the table - in other words the column that has the most unique values in the table comes first.
-Ken
October 21, 2004 at 8:50 am
Agree with Ken. The best practice is to create a individual index for each column in this scenario. If you know that some exact combinations of columns will mostly be used, create indexes for them as well.
However, this will slow down the insertion/deletion/modification if the table is highly involved in OLTP.
October 21, 2004 at 10:59 am
I will add an index for all the searched fields with a fill factor of 90%. Do you think this is a good fill factor as the table could get into millions of records quickly. Also, do you think this will help in reporting where I have to join this table w/ other tables (other tables have this tables id as a foriegn key is indexed) and do group by and sum queries? Also is there any other SQL server configuration that I can change around to make it run faster?
Thank you for all those who responded. This gives me a better idea of which way to go.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply