On Sun, 28 Jul 1996, Bojan Trafela wrote:

> We have tables with approx. 10-15 columns and over 200.000 rows and
> secondary indexes on them.
> We don't know what is better to use to get the best posible
> preformance in our queries?
> Btree tables and btree indexes or
> Btree tables and isam indexes
> or isam tables and isam indexes.

As ever, it depends, and the best strategy is to just try the various 
possible combinations and benchmark them.  Since you give us no 
information about the queries you generally run I can't really say if you 
should be excluding HASH as an index structure, but that might be another 
one to consider if you are doing exact key matches.

Just as a rule of thumb, if a table and its indices are relatively static,
or if you can afford to re-modify relatively frequently, then ISAM can get
you some benefits because it takes less I/O to get to the data than a
B-tree.  On the other hand, if the table is very dynamic, or you can't
afford to re-modify frequently (or there is no one on-site who can be
trusted to do it) then a B-tree is probably a better choice. 

If in doubt, and if you can't take the time to benchmark, opt for a
B-tree.  

========================================================================
Roy Hann                                   rhann@tnc.com
BKB Engineering Ltd.,                      NAIUA Member-at-Large
11211-76th Avenue,                 
Edmonton, Alberta                          http://www.tnc.com/bkb
T6G 0K2                                    Tel: (403)497-2409
Canada                                     FAX: (403)436-6055
========================================================================

Ingres Q & A

To William's Home Page

© William Yuan 2000

Email William