Ann W. Harrison wrote:

> coderefectory wrote:
>> I tested my new Firebird 2.1 database to 100 mil. records (16.9GB size).
>> I created indexes and everything work perfect, even through USB 1.0
>> extern HDD.I realized that LIKE, COUNT, MAX conditions work to slow
>> and I avoid them.
>
> LIKE is faster if you don't start the string compared with a wild
> card because it can use an index. If, you use a parameter, the
> search will be slow even if you eventually pass in a string that
> doesn't start with a wild card. STARTING WITH can use an index
> because its semantics require that the beginning of the pattern
> string be first. It is case sensitive.

Just to add that there are workarounds if you need it to be case
insensitive. You can create a expression index on UPPER(column) and use
that in your STARTING WITH clause. A select like this will use such index:

select ... where UPPER(column) starting with 'XYZ';

Make sure you always up-case 'XYZ' before running the statement.

Alternatively, you can use case-insensitive collations.

Be careful, this only applies to STARTING WITH and not CONTAINING.

Regards,

--
Milan Babuskov

==================================
The easiest way to import XML, CSV
and textual files into Firebird:
http://www.guacosoft.com/xmlwizard
==================================