Creating and using index

[MusaItopa]

I have a very large table of over 30 million records. my query is usually very slow. In my queries i refernce four fields in the where clause. how doi create my index. should i create index based on each field or just one index on all the fields

 

[Crowley16]

if you are not going to change things, then go with a covered index containing all 4 columns...

of course, if you have the space and the system isn't transactional, then you can consider indexing all 4 columns, and having the covered index...

--------------------
Procrastinate Now!

 

[MusaItopa]

Thanks for your sugesstions. though i have tried that i didnt see any significant improvement. Let me state again what the issues are: I have a table MIS and i have four fields that i normally use in my where clause. Sometimes i use just one ofthe fields and other times i use 2 or 3 or all the field depending on what i want to fetch out.

How do i create my index. Should i create four indexes with each field having its own or should i create one index with all the field. Speed is of the essence in this project.

Thanks

 

[r937]

one index with all the columns (a covering index) isn't necessarily going to help

for example, (a,b,c,d) covers WHERE a=9 and b=3 and c=7 and d=42

but it's absolutely useless for WHERE c=7 and d=9

analyze the various WHERE clauses in all your different queries, they will tell you what you need



r937.com | rudy.ca

 

[MusaItopa]

Sorry are you sugesting i identify all the various where clause combinations and create indexes baed on those fields

 

[r937]

yes, that is what i'm suggesting

r937.com | rudy.ca

 

[MusaItopa]

Thanks. I will try that and get back to you
I ma most grateful

 

[MusaItopa]

Again i'm back. I think it is going to be very complex for me. i discovered that i willrun into serious permutation of columns to create indexes on. There where claue is created dynamically based on request. Let me also inform you that this is a dataware house which does only read-only query. i may run into ober 50 indexes just on one table because the columns that make the where clause depends on teh user selection from the query interface. The user choses the filter. What is the effect of creating indexes on each of th fields seperatlysince this is what iam sured of.

 

[r937]

create at least one or two covering indexes -- for the sequence(s) of columns that you feel will be most commonly used

e.g. no sense in asking for all products with size=10 when that would yield many unrelated products, whereas products with category=3 would be a more common query

r937.com | rudy.ca

 

[MusaItopa]

Thanks a million. I will keep you posted