If you want performance too, make as many of your join criteria
numerical and of the same type. I try to search on strings but join
tables on integers (or bigints or shorts, doubles and floats are OK but
not as good)

You can also play with the play and tell the optimizer a better one.

On Mon, 2012-04-30 at 21:39 +0000, hvlad wrote:
>
>
>
> --- 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
>
>
>
>
>