--- In firebird-support@yahoogroups.com, "Dmitry Yemanov"
<dimitr@u...> wrote:

> "aleskahanek" <akahanek@a...> wrote:
> >
> > This query takes about 20 seconds
> >
> > SELECT PERSON.LNAME, COMPANY.COM_NAME
> > FROM PERSON
> > LEFT JOIN COMPANY ON PERSON.COM_ID=COMPANY.COM_ID
> > ORDER BY PERSON.LNAME
> >
> > PLAN SORT (JOIN (PERSON NATURAL,COMPANY INDEX (RDB$PRIMARY4)))
> >
> > while this returns almost immediately (without sorting)
> >
> > SELECT PERSON.LNAME
> > FROM PERSON
> > LEFT JOIN COMPANY ON PERSON.COM_ID=COMPANY.COM_ID
> >
> > Why Firebird does not use the LNAME index for table PERSON?
>
> Because it's not smart enough. Although I doubt this index would

help you

> much unless you need only a few rows instead of all 300K.
>

I know, that selecting 300K rows is not all right but users of the
application generally do not know about SQL and other things and what
is smart or not. They are always asked to enter a search criteria
before the SELECT is issued, but sometimes they open the form with no
search criteria and then the SELECT with no WHERE clause is used. And
when they select ordering by some of the column, the query lasts long.

> > I need to use LEFT JOIN and also order the result set according

to

> > LNAME.
>
> SELECT
> PERSON.LNAME,
> ( SELECT COMPANY.COM_NAME FROM COMPANY WHERE COMPANY.COM_ID =
> PERSON.COM_ID )
> FROM
> PERSON
>
> ???
>
>
> Dmitry

Thanks, this is very fast, but there is one question: suppose that
generally I select more than one field from table COMPANY, so the
query would look like

SELECT PERSON.LNAME,
(SELECT COMPANY.COM_NAME FROM COMPANY WHERE
COMPANY.COM_ID=PERSON.COM_ID),
(SELECT COMPANY.COM_TOWN FROM COMPANY WHERE
COMPANY.COM_ID=PERSON.COM_ID),
(SELECT COMPANY.COM_STREET FROM COMPANY WHERE
COMPANY.COM_ID=PERSON.COM_ID),
(SELECT COMPANY.VAT_ID FROM COMPANY WHERE
COMPANY.COM_ID=PERSON.COM_ID),
(SELECT COMPANY.NOTE1 FROM COMPANY WHERE
COMPANY.COM_ID=PERSON.COM_ID),
(SELECT COMPANY.NOTE2 FROM COMPANY WHERE
COMPANY.COM_ID=PERSON.COM_ID)
/* ... and more COMPANY fields*/
FROM PERSON

this is not very good too, isn´t it?
Ales