> On Feb 3, 2015, at 8:17 AM, Fulvio Senore mailing@... [firebird-support] <firebird-support@yahoogroups.com> wrote:
>
> 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.
>

Unfortunately, there's nothing in your query that limits the number of invoices you return for each customer. If you actually wanted all the invoices, the sort would be faster than the random retrieval by invoice index. But you don't. And what you want is not the first customer/invoice pair, but all customers and only the first invoice from each.

You might try something like this:

select c.name, (select first 1 i.* from invoices i
where i.customer_id = c.customer_id
order by i.year desc, i.number descending)
from customers c


If you have customers without invoices, you could add a "where exists ..." to the end of the query.


Good luck,

Ann

>