indices creation.. 1

[brianpercival]

I have a basic question. we have a table with one and half million records. unfortunately, users have to do search on this table on various fields.

For faster search, how do I create indices? Should I just create individual indices for each search field? In the search form user can selectively create a combination of fields for search, but exact combinations are not known before hand. Basically what I want some light on is, if I create a clustered index, and user doesn't use that combination of fields for searching, then the index is of no use right? so should I just leave the indexing at just individual indices and be content or can I do anything better?

Hope I am making sense..

regards,
Brian

 

[gmmastros]

Having too many indexes will slow performance for inserts, updates, and deletes. So, you don't want to have more indexes than necessary.

My suggestion would be:

Use QA to run the query. Press CTRL-K OR Click Query->Show Execution Plan

Then, when you run the query, there will be an 'execution plan' tab at the bottom of the window. You can use this information to improve the performance of your queries. Look for table scans. If you find any, put an index on the table/field.

You can also use the 'index tuning wizard'. I've been 'less than satisfied' with the results, but it's worth a shot.

-George

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

 

[brianpercival]

The only action that will happen on the table I am using is, insert and select. and if I have say five indices is it too much?
I will try your query execution plan method tho..


regards,
Brian

 

[gmmastros]

five indices is it too much?

Only you can answer that question. It all depends on performance. If 5 indexes causes inserts to be unbearably slow, then 5 is too many. If it's fast, then 5 is ok.

-George

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