At 11:46 AM 05-12-00 +0100, you wrote:

>With the help from the ibobjects lists I created a database/table with
>about 450.000 records with all street names in the Netherlands.
>
>CREATE TABLE "POSTCODE"
>(
> "ID" INT64 NOT NULL,
> "STREET" CHAR( 50) CHARACTER SET NONE,
> "CITY" CHAR( 50) CHARACTER SET NONE,
> "POSTCODE" CHAR( 7) CHARACTER SET NONE,
> CONSTRAINT "PK_POSTCODE" PRIMARY KEY ("ID")
>);
>
>CREATE ASC INDEX "POSTCODE" ON "POSTCODE" ("POSTCODE");
>CREATE ASC INDEX "STREET" ON "POSTCODE" ("STREET");
>I created the following query :
>
>SELECT STREET FROM POSTCODE WHERE STREET CONTAINING 'BRAM'
>
>There are actually 130 streets in Holland containing my first name. But it
>takes about 10/15 seconds before its returning the 130 rows with an
>IB_Query set on active. Is there a way to check if the query is 100% optimized.
>
>Could it go faster ?

Yes, it should be sub-second. I think the problem is in the CONTAINING
clause - I always get confused about this but I *think* containing doesn't
use the index (or maybe CONTAINING does use it and STARTING WITH
doesn't). Even if it does, it's still a lot of comparing to do 4
characters across potentially 50, not matched for case (46 * 450,000 compares).

Set the query up in the IB_WISQL and look at the plan that appears in the
monitor. That will tell you what indexes are being used.

If you are still at the experimentation stage, add a proxy column for the
street that will get populated by a trigger and store the street name in
upper case. You can populate the existing rows with

update postcode set PROXYSTREET=UPPER(STREET);

put an index on PROXYSTREET and try your CONTAINING clause on that.

Cheers,
H.
All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________