Hello!

Using DBISAM 4.24, and digging into query performance.  We have noticed that DBISAM seems
to show much lower cost when we have two discrete indices.


We have a nice simple query we are trying to make FLY (cause when the table underneath has
200K records, and is over a network.... performance matters!).   We are testing on a
small, 3000 record table a query that returns 177 rows.

Here is the nice simple query:


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

Easy, no?

Our original table spec has an index:

  CREATE INDEX ix_Shipment_ShipDate ON OurTable (ShipDate, Carrier, recID);

When we look at the query plan, we see a cost of 140kb

When we drop that index, and create two discrete indices:


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


We see the cost drop to 40kb

What is going on?


Thank you!

Sam Jones <sam@acme.com> wrote on Wed, 8 Nov 2006 21:00:43 -0400

>Hello!
>
>Using DBISAM 4.24, and digging into query performance. We have noticed that DBISAM seems
>to show much lower cost when we have two discrete indices.
>

Sam


You have "noticed" what it says in the manual

1) DBISAM only uses the first field of any given index for optimization. This means that if you have an index containing the fields LastName and FirstName, then DBISAM can only use the this index for optimizing any conditions that refer to the LastName field.

See:- http://www.elevatesoft.com/dbisam4d7_optimizations.htm

Cheers

Jeff

--
Jeff Cook
Aspect Systems Ltd
Phone: +64-9-424 5388
Skype: jeffcooknz
www.aspect.co.nz

Thank you!

A quick read of this leads me to think that multi-column indexes are of no real value.

Is this a fair assesment?

"Sam Jones" <sam@acme.com> wrote in message
news:6400A9E3-FE7B-4E9E-8597-2126F4D16975@news.elevatesoft.com...
> Thank you!
>
Not so fast.

Looks like a bug to me. Should use the most efficient index, regardless of
creation order. If, for whatever reason, DBISAM  processes the query much
more efficiently using the single field index, then the query optimization
should know that and use the correct index, if available.

> A quick read of this leads me to think that multi-column indexes are of no
> real value.
>

They are of no value if all your access is via SQL, that is true. If you use
tTables, they are very useful, especially when using master/detail
relationsips, since they allow you to display details in the right sequence.

Robert

Thank you for the clarification.

Yes, all our access is via SQL. So it appears that compound indices are of no use.

As you can see here:
http://www.elevatesoft.com/scripts/newsgrp.dll?action=openmsg&group=5&msg=56982&page=1#msg56982

We have determined that DBISAM is not using the smallest index. Rather it is using the
first index found that has the needed column as the first element.

This does in fact look like an optimizer bug, I agree.

Sam


Multi column indices are useful, but only in the ORDER BY clause

Roy Lambert

Sam Jones wrote:
> Thank you!
>
> A quick read of this leads me to think that multi-column indexes are
> of no real value.

They are if you use ranges with TDBISAMTables, or indexes to sort
TDBISAMTables eg. in a grid. Other than that, you are better off with single
field indexes.

Ralf

Robert,

<< Looks like a bug to me. Should use the most efficient index, regardless
of creation order. If, for whatever reason, DBISAM  processes the query much
more efficiently using the single field index, then the query optimization
should know that and use the correct index, if available. >>

Yes, it should, but at this time it doesn't.  However, unless the index key
is completely unique, duplicate key compression will remove almost any
differences in the two in terms of the actual I/O cost.

--
Tim Young
Elevate Software
www.elevatesoft.com