Re: [GENERAL] SQL optimization - WHERE SomeField STARTING WITH ...

[Tino Wildenhain]

Bill wrote:

Tino Wildenhain wrote:

Hi Bill,

Bill wrote:

The SQL database servers I have worked with cannot use and index for a SELECT of the form

SELECT * FROM ATABLE
WHERE AFIELD LIKE ?



SELECT * FROM ATABLE
WHERE AFIELD STARTING WITH ?

because there is no way to know the location of the wild card until the parameter value is known. InterBase and Firebird allow which is equivalent to LIKE 'ABC%' and will use an index on AFIELD. Is there a similar syntax in PostgreSQL?

Yes, its actually: LIKE 'ABC%' and it will use an index.

Regards
Tino

Are you saying that a parameterized query whose WHERE clause is AFIELD LIKE ? will use an index on AFIELD if the parameter value is 'ABC%'. I

...
no, I'm not saying that anymore (nor did I intend to do :-) I was just
misreading your question. Sorry.

Regards
Tino

smime.p7s
Description: S/MIME Cryptographic Signature