Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 8 of 8 total |
DBISAM appears not to use multi-column indexes... or am I seeing things? |
Wed, Nov 8 2006 9:00 PM | Permanent Link |
Sam Jones | 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! |
Wed, Nov 8 2006 9:14 PM | Permanent Link |
Jeff Cook | 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 |
Wed, Nov 8 2006 9:27 PM | Permanent Link |
Sam Jones | 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? |
Wed, Nov 8 2006 11:18 PM | Permanent Link |
"Robert" | "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 |
Thu, Nov 9 2006 1:09 AM | Permanent Link |
Sam Jones | 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. |
Thu, Nov 9 2006 4:07 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Sam
Multi column indices are useful, but only in the ORDER BY clause Roy Lambert |
Thu, Nov 9 2006 7:57 AM | Permanent Link |
"Ralf Mimoun" | 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 |
Thu, Nov 9 2006 7:30 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Sunday, May 5, 2024 at 07:30 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |