Tom -

First, as always, I may be wrong, so anyone is free to override my incorrect assumptions here. I may also simply be stating the obvious.

A SELECT or sort cannot use the index if it does not specify field values for the FIRST n columns in the index. So, if you sometimes select on column A and sometimes on column B, then using multiple, single-field indices is the only way. If you always use both columns A & B, then a multi-column index is the right choice.

I would think that a single, multi-field index is quicker on an update or insert, but this is also an assumption on my part.

Thus, in a free-form query environment, use single-field indices (where appropriate -- reasonably high selectivity). In a fixed query environment, using multi-field indices should be more efficient especially if there isn't significant overlap among the columns being indexed. By "overlap" I mean that one column is being used in many indices.

Most of the time I opt for single-field indices, then use multi-field ones when specific performance issues can be addressed by them.

As always, your mileage may vary.

HTH

Louis Kleiman
SSTMS, Inc.

-----Original Message-----
From: Thomas Miller [mailto:tmiller@...]
Sent: Thu 6/3/2004 12:41 PM
To: firebird-support@yahoogroups.com
Cc:
Subject: [firebird-support] Indexes and optimization


Hi,

As a point of reference, I have an extremely strong background in Oracle
(and the way Oracle Indexes work).

Compound or Complex Indexes (indexes with more then one column)

With Oracle it is recommend to use these. Years ago, I was told not to
use them for
IB. That I should just index each column individually and the optimizer
would
do the rest. These indexes are primarily used for complex joins between
multiple tables. So which is the best way for FB

a) Multiple single column indexes
b) One multi-column index

Is it different when primarily using the index to speed up sorts / searches?

Thanks in advance.

--
Thomas Miller
Delphi Client/Server Certified Developer
BSS Accounting & Distribution Software
BSS Enterprise Accounting FrameWork

http://www.bss-software.com
http://sourceforge.net/projects/dbexpressplus




Yahoo! Groups Sponsor
ADVERTISEMENT
click here <http://rd.yahoo.com/SIG=1296t6q98/M=298184.5022502.6152625.3001176/D=groups/S=1705115386:HM/EXP=1086368058/A=2164330/R=0/SIG=11eamf8g4/*http://www.netflix.com/Default?mqso=60183350>
<http://us.adserver.yahoo.com/l?M=298184.5022502.6152625.3001176/D=groups/S=:HM/A=2164330/rand=181096840>


_____

Yahoo! Groups Links


* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

* To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com <mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>

* Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service <http://docs.yahoo.com/info/terms/> .




[Non-text portions of this message have been removed]