Hello!

We are doing some performance analysis of our app with DBISAM 4.24.

Issue: DBISAM appears to choose its index based on index creation sequence, not on the
smallest index that will do the job.

In our work, we are looking at the 'cost' value. Our test bed is a simple 3000 row table
with several indexes. However in the wild this table can have 200k or more rows, and be
over a network (on a server share).

So performance really matters.

Here is the query we are using to investigate:

SELECT OurTable.recID, OurTable.recTYPE
FROM OurTable
WHERE (( shipDate >= '2006-10-08' and carrier = 0 ))


We get the absolute lowest cost for this query with the table has these two indexes:

 Create Index ix_shipment2 ON shipment (carrier);
 Create Index ix_shipment3 ON shipment (shipdate);


But we have another index, and when it is present, the cost goes WAY up. If the index
creation order is this:

 CREATE INDEX ix_Shipment_MarkAsUploaded ON Shipment (Carrier, Account, dtClosed,
UPSServiceType, BOLD_ID);
 Create Index ix_shipment2 ON shipment (carrier);
 Create Index ix_shipment3 ON shipment (shipdate);

The query cost goes WAY up, because DBISAM is using the first index as the 'carrier' index.

This table has 22 indexes on it (19 of which are single column indexes).

Must we follow the rule of all single column indexes are created first?

(seems a bit.... interesting....)



Thank you!

Sam,

<< Issue: DBISAM appears to choose its index based on index creation
sequence, not on the smallest index that will do the job. >>

Just make sure that the composite indexes have at least duplicate key
compression, and that should take care of most, if not all, of the actual
differences in the actual I/O (provided that the composite keys are not
completely unique, which is doubtful).  The optimizer's costs does not take
into account index key compression because it can't - the index key
compression is a context-sensitive calculation and it cannot be reflected in
the statistics that the optimizer uses.

--
Tim Young
Elevate Software
www.elevatesoft.com