> In the past I have always create indexes on single fields that were going to be part of my WHERE clause, except for fields like boolean or with little varitions in values.
>
> I was recently on a beginners course with Oracle and the instructor spent a bit of time talking about indexes and b-tree and was quite adamant that if you had a primary key made up of multiple fields or regularly did searches on a group of fields that to get the best results you should create a compound index using those field and then use that expression in your where clause.
>
> With that in mind, say I have a table with Customer,Account,Invoice Date. In the past I would have created individual indexes on these fields and then used a where clause like:
>
>

 Customer = 'MyCustomer'
>  AND Account = 1234
>  AND Invoice Date = {01/02/2009}
> 

>
> However if I go by the instructors advice I would be better served with compound index, in the same order, with a where like:
>
>

Customer+Account+Invoice Date =  'MyCustomer 1234 {01/02/2009}'

>
> I know the above isn't syntactically correct but you know what I mean. The way the instructor explained it, it did make sense.
>
> So assuming I have a table where 90% of my queries will be on all three fields, another 5% be on customer+account and maybe a further 3% on just customer, my questions are:
>
> 1)Was the instructor taking rubbish in terms of Oracle - I am very new to Oracle .
>
> 2)Does this index strategy also work in VFP or does the Rushmore technology make the individual field approach more efficient. I am looking more towards efficiency than flexibility these days
>
> 3)How about with SQL Server?
>
> I do understand that these type of things are different depending on the size of the tables and various other criteria and that one must fully test for all alternatives but I am just looking for some guidelines
>
> Thanks much,
>
> Steve Dingle
> http://weblogs.foxite.com/SteveDingle/
> www.dsbusinesssolutions.co.uk

1) Couldn't quite understand what does "taking rubbish" mean. Instructor was right.

2) That index strategy also works in VFP. Actually you can observe yourself that with individual indexes VFP's Rushmore optimization is not much of a helper. OTOH creating compound index the physcial index size increase. VFP is not a Client/Server engine, downloads indexes to client first. That is a catch 22 situation. What I have observed so far is with VFP:
-Search only on a single index and get a subset to client
-On the client do your second and third filtering
Sounds strange? Yes it is strange I agree but that was the optimization I need to do when customers complained about speed. My original indexes and query was very similar to yours:

** I needed two indexes for the most done search

index on PersonId tag PersonId
index on LoginTime tag LoginTime

A select like:

select * from myTable ;
  where personId = 1 and LoginTime between {^2009/1/1 12:00} and {^2009/2/1 12:00}

took considerable time.

Instead of a compound index I did:

select * from myTable ;
  where personId = 1 ;
  into cursor xx nofilter
select * from xx where LoginTime between {^2009/1/1 12:00} and {^2009/2/1 12:00}

was almost instant.

I published full demonstration code here and/or on another forum before.

When it is VFP IMHO individual indexes are still the way to go. Small 2 fields indexes might be of value based on your needs.

The situation is however very different with C/S systems like Oracle, SQL server .... Even the index creation is different in those systems. ie: You don't need to convert the types to character and create a single compund expression matching exact index signature while searching. ie:

In SQL server an index like yours is created:

(Customer, AccountID, InvoiceDate)

and search is done plainly:

where Customer = @p0 and accountId = @p1 and InvoiceDate = @p2

(@p0 is character, @p1 is int and @p2 is datetime exactly as it is on data source).
This index would be beneficial for this one too:

where Customer = @p0

If it were VFP you would need something like:

index on cast(Customer as c(15)) + padl(AccountID,10) + dtoc(InvoiceDate,1) tag myTAg

and when you needed to search you needed your search expression be in a specific style and search exression be an EXACT MATCH OF INDEX SIGNATURE:

lcSearch1 = cast('MyCustomer' as c(15)) + padl(1234,10) + dtoc(Date(2009,2,1),1)
lcSearch2 = cast('MyCustomer' as c(15)) + padl(1234,10)
lcSearch3 = 'MyCustomer'

SET ANSI OFF && default
... where cast(Customer as c(15)) + padl(AccountID,10) + dtoc(InvoiceDate,1) = ?m.lcSearch1
... where cast(Customer as c(15)) + padl(AccountID,10) + dtoc(InvoiceDate,1) = ?m.lcSearch2
... where cast(Customer as c(15)) + padl(AccountID,10) + dtoc(InvoiceDate,1) = ?m.lcSearch3

Note that there is a match to index expression even if you are interested just with Customer and/or AccountID. Also with such expression you should be on your toes against bugs like padl() with NULL doesn't produce the result in a fixed size.

3) With SQL server compound indexes have value like it has in Oracle (I am more MS SQL oriented rather than Oracle). SQL server also have additions like "include ( non-key columns list )" which may create indexes that you don't need to refer to the table for result (all data you need is obtained directly from index) - full index definition is wider, this one is one of the features I like. ie: If you are most frequently searching an accountID and getting back accountant's firstName, lastName then with an index like:

create index mySampleIndex on myTable (accountID) include (firstName,lastName)

you can get the result of this select w/o going to table (all is read from index):

select firstName,lastName from myTable where accountId = 124

To summarize it:
When it is not VFP think of well choosen compound indexes.
When it is VFP think twice creating compund indexes and/or even indexes more than needed.

Cetin Basoz

.Net has got better.Think about moving - check my blog:
Blog (main)
Blog (mirror)