Dear sir,

I have got a table of about 10 fields, there is a ID (INT, Primary key)
field, but not automatically increased. I only use 5 out of 10 fields in the
WHERE or join clause. What kind of index should I create?

For example, 5 fields, A,B,C,D,E. 30% of query use A,C,E, 30% use A,B,C, 30%
use B,D,E, 10% use others...

How should I create the index... One index covering all 5 fields or seperate
index for each field?

Thanks in Advanced!

regards,

Guoqi Zheng
http://www.ureader.com

guoqi zheng a écrit :
This mean 90% use a filter on A !

Set an index on A and retest... You will see later wich new column you
can index then


Multicolumns index had 3 disavantages :
1) maximize the amount of data storage
2) stats are only on the first col
3) (A, B, C) index can only be use for A or A + B or A + B + C queries
not A + C, not B + C...

A +

Indexes should be created in COLUMNS which are somewhat unique.

Male/Female is a bad column to index on.  Unique id's are usually great
index candidates.

if there is not a lot of changes to the table, then more indexes don't
actually hurt that much. If lots of changes, like a transaction table,
then minimize indexes.

Look in the where clauses. Indexes should mirror the columns in the
where clauses.

Deadlocks "again"
Move DB by backup/restore or SP_Detach?
How to "Grant" a Trigger ???
IN clause with comma seperated values in select query
Table comparison
Add User to Log-Shipped Database
sqlserver 2005 express license
Licensing by the month?
MSDE 2000 problems with linked server
SQL Server Integration Services