You could try an outer join ... mucky in the extreme but it sometimes
forces the optimiser to look at the sane table first ... (yes I know one
shouldn't have to do that sort of thing).

On 03/02/2015 12:17, Fulvio Senore mailing@... [firebird-support]
wrote:

> I have a database containing(simplifyinga little) a CUSTOMERS table
> andan INVOICES table.
> INVOICES contains a foreign key to CUSTOMERS, as you can expect.
>
> I want to retrieve rows showing INVOICES data and some CUSTOMERS data so
> I use an inner join, and I want to see newer invoices first so I add an
> ORDER BY clause. I only need a few rowssince I will show only the latest
> invoices in a grid.
> The query is something like
>
> SELECT INVOICES.*, CUSTOMERS.NAME
> FROM INVOICES INNER JOIN CUSTOMERS ON INVOICES.CUSTOMER_ID =
> CUSTOMERS.CUSTOMER_ID
> ORDER BY INVOICES.YEAR DESC, INVOICES.NUMBER DESC
>
> I have created a descending index on INVOICES.YEAR, INVOICES.NUMBER
>
> The problem is that if the tables are large the query is rather slow.
> Looking at the plan I see:
>
> PLAN SORT (JOIN (CUSTOMERS NATURAL, INVOICES INDEX (FK_INVOICES_CUSTOMERS)))
>
> so the database loads all rows and then it sorts them.Of course it is slow.
>
>
> If I test a query like
>
> SELECT * FROM INVOICES
> ORDER BY INVOICES.YEAR DESC, INVOICES.NUMBER DESC
>
> the plan is
>
> PLAN (INVOICESORDER IDX_INVOICES_DESC)
>
> and the query is much faster since it uses the index and it only reads a
> few rows.
>
>
> So it looks like if there is an inner join the optimizer does not use
> the descending index on INVOICES to speed up things.
>
>
> Is there a way to force the optimizer to use the descending index
> instead of sorting all the resulting rows?
> I am using Firebird 2.1 on Windows.
>
> Thanks in advance.
>
> Fulvio Senore

--
Tim Ward