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.

MAX will be fast if you add a descending index.

COUNT is slow because it can't be resolved without looking at all
the records you're counting. Basically, the index holds more
entries than your transaction can see, and Firebird can't decide which
you can see without looking at the actual records.

CONTAINING is case-insensitive LIKE, but doesn't use indexes, so
avoid it with large data sets.

> In which category belongs my database with 100 million records in
> the table ... a small, medium, or large database?

Medium

>
> I found that condition 'STARTING WITH' is much better than 'LIKE'...Is
> there some other condition and method suitable for large database?

Check the query plans to be sure whatever you're doing uses indexes.

> Is it true that for user interface the best practice is using Cursors
> not Querys?

That depends on the interface you're using - there are lots.
Generally, the best practice is to use prepared queries and pass
in parameters, except, as mentioned above, if there's a chance
to make LIKE work like STARTING WITH.

Cheers,

Ann