--- In firebird-support@yahoogroups.com, "jankowalsky825" wrote:

>
> Hi guys.
>
> I've read whatever I could find about this problem. Suggestion is to create a proper indices and that should speed up the query but query optimizer do not take my indices into account and generates natural plan. I do not know what should I do?
>
> Here are my two tables:
>
> CREATE TABLE CLIENT
> (

...

> SYMFONIANUMBER varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,

...

> );

...

> CREATE INDEX CLIENT_SYMFONIANUMBER_A ON CLIENT (SYMFONIANUMBER);

...

> CREATE GLOBAL TEMPORARY TABLE IMPORT_TEMP_LOCATION
> (

...

> CLIENT_OUTID integer,

...

> )
> ON COMMIT PRESERVE ROWS;

...

> In CLIENT table I have 167502 rows and in IMPORT_TEMP_LOCATION I have 203 rows and it takes 1:30 minutes to execute my query.
>
> SELECT C.ID_CLIENT FROM IMPORT_TEMP_LOCATION T
> LEFT OUTER JOIN CLIENT C ON (T.CLIENT_OUTID = C.SYMFONIANUMBER)

You are joining INTEGER with VARCHAR. It couldn't use index for such condition.
You can :
- change type of CLIENT_OUTID to VARCHAR, or
- change type of SYMFONIANUMBER to INTEGER, or
- CAST(CLIENT_OUTID AS VARCHAR) in join condition (if it makes sence for your data)

Regards,
Vlad