Hi Helen,

> >Just looking at the query, both will be very poor performers. Both will
> >require the SQL engine to evaluate the columns for every row in the
> >table by brute force.
>
> This isn't so. The Firebird optimizer will convert the first query:
>
> select * from planoconta where nivel like '01.01%'
>
> to this:
>
> select * from planoconta where nivel STARTING WITH '01.01'
>
> and will use an index on nivel, if there is one.
>
> The second query has an expression for its search operand, ruling out use
> of the index (for now...expression indices are coming...but the expression
> index would have to exist).

On the other hand, a substring(1, x) could be converted to a
STARTING WITH as well :-)

No need for expression indices there.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com