Compound indexes

[Ruslan Zasukhin]

on 6/23/03 17:08, Bryan McCormack at bmccormack at artsystems.com wrote:

> Thanks for the advice, Ruslan.  I thought that was how I was going to
> have to construct a "compound index" with Valentina.  One last question,
> though.  You mentioned:
> 
>> A BaseObject method can have TYPE the same as normal fields. In your case you
>> join 4 ULONG values. So you get 12 bytes key. As you know there is no such
>> numeric type, so you need to use String for such key.
>> 
>> In case you'd need join only 4 ULONG fields you could use method of type
>> ULLONG and build its value using bit operations:   (v1 << 32) + v2
>> 
>> In your case you need do:
>> (btw, in ValentinaSQL.pdf you can see that Valentina for command
> CREATE
>> TABLE have extension METHOD )
> 
>>  CREATE TABLE tblCustomerInvoices (
>>  CustomerInvoiceID ULONG NOT NULL UNIQUE INDEXED,
>>  CustomerID ULONG INDEXED,
>>  SalespersonID ULONG INDEXED,
>>  InvoiceID ULONG INDEXED,
>>  Notes TEXT(255)
> 
>>  idxCustomerSalespersonInvoice STRING(12) INDEXED
>>                   METHOD('concat(CustomerID, SalespersonID,
> InvoiceID)')
>>  );
> 
> Would making this field a numeric type of field, for instance ULONG, and
> performing a numeric operation as the method, for instance adding the
> values of the 3 fields together, speed up the performance of the index?

Yes of course.

> For example, would defining the field as:
> 
> idxCustomerSalespersonInvoice ULONG INDEXED
> METHOD(CustomerID + SalespersonID + InvoiceID)

But this is not correct, Bryan.

Because you will not get unique key by '+'.
Look

        1 + 2 = 3
        2 + 1 = 3


> boost the performance of the index?  Or would there be no noticable
> increase in performance in Valentina over an indexed string field as in
> the example you've given?

Of course numeric is better.
But sinse you need join 3 ULONG fields...

In the best case I think you need analyze your data, and may be you will be
able "compress" all into ULLONG (8 bytes)

You need analyze how many MAXIMUM records will be in your tables.
Then shift values,  for example:

        (x << 20) + (y << 13) + z
     

> Furthermore, how would such a field, either
> defined as a string or an integer datatype, be calculated if the value
> in any one of those fields for a particular record was null?

Result will be NULL.

> For example, if a particular record had a CustomerID = 2, SalespersonID =
> NULL, InvoiceID = 678, would the idxCustomerSalespersonInvoice calculate to
> null?

Yes of course.

-- 
Best regards,
Ruslan Zasukhin      [ I feel the need...the need for speed ]
-------------------------------------------------------------
e-mail: ruslan at paradigmasoft.com
web: http://www.paradigmasoft.com

To subscribe to the Valentina mail list go to:
http://lists.macserve.net/mailman/listinfo/valentina
-------------------------------------------------------------