I have a table with customers. Name and GivenName are fields in this table.
When searching the user can enter a part of the name AND/OR the GivenName.

Now my question: is it generally faster to use one composed index (Name;
GivenName) or two different indexes, one on the field Name, one on the
field GivenName?

Thanks. Uli

Uli
If the user can enter only part of either then you can't use a compound index since there's no equivalent to lam*+ro*

Roy Lambert [Team Elevate]

> If the user can enter only part of either then you can't use a compound index since there's no equivalent to lam*+ro*

I don't understand what you mean. Off course I use two fields, one for
the name and one for the given name.
That's the code I am using:

PREPARE Stmt FROM
      'select ...
      from Rechnungen R
      left outer join Patienten P on R.PatientenID = P.PatientenID
      where P.name COLLATE DEU_CI like ? and P.vorname COLLATE DEU_CI
like ?
      order by P.Name, P.Vorname';
OPEN Result using Name + '%', Vorname + '%';

The question now is whether or not two single indexes are faster than
one compound index.

Regards Uli

Uli

>OPEN Result using Name + '%', Vorname + '%';

As things stand ElevateDB will only use the Name part of the index and do a brute force scan for the second item if you use a compound index. If you want both to be used have an index on Name and an index on Vorname

Roy Lambert [Team Elevate]

Roy,

> As things stand ElevateDB will only use the Name part of the index and do
> a brute force scan for the second item if you use a compound index. If you
> want both to be used have an index on Name and an index on Vorname

Do you know it or just suppose?

Regards Uli

Uli


Not being Tim and not having the source I'm only guessing, but I'm guessing from other posts by Tim.


Roy Lambert [Team Elevate]

Roy,

> Not being Tim and not having the source I'm only guessing, but I'm
guessing from other posts by Tim.

I am sorry, but this kind of "help" is not useful. So I hope Tim will
read it

Regards Uli

Uli


Sorry I'm not helpful. Try searching the elevatedb ng's for the word compound and you'll see what I'm basing my opinion on.

Roy Lambert [Team Elevate]

Uli,

EDB only uses the first column of any index for optimization.
This is more or less the same Roy as said but if you don't believe us,
you can always read the manual:

http://www.elevatesoft.com/manual?action=mantopic&id=edb1sql&category=0&topic=16

--
Fernando Dias
[Team Elevate]

Fernando


Dammit! I always forget the manual

Roy Lambert