asked
Table design for searchable fields
I have some 12 searchable fields in UI, based on which the sql query gets formed in and executes in sql server. If I add 12 fields for all these, I have to create indices on each columns for better performances. But my DBA is not convinced with this approach, since the maintenance for 12 text field indices. Can you please give me some other approach to resolve this? Regards, --Rudra
Hi Rudra Indexes always have a cost: the space they take up and the work the engine has to put into maintaining them as data changes. It's a trade-off between those costs and the benefits you gain if you have the indexes. I think you need to test whether the searches will perform adequately without those indexes ideally on a similar server with a full set of the data to be searched. If performance is OK, no problem. If not, test what indexes will make the performance OK and discuss with the DBA. The DBA can examine the index size and usage stats on the test server. If the DBA still feels the system can't take the load of those indexes then you can work together. Prepare an estimate of what's required and take it to the business: "If you want the nice quick search, here's what you need to provide us so we can deliver it."
15 People are following this question.
