Il 03/02/2015 14:41, Ann Harrison [email protected] [firebird-support] ha scritto: > >> On Feb 3, 2015, at 8:17 AM, Fulvio Senore [email protected] >> [firebird-support] <[email protected]> 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 >>
Thank you for your help. I did not explain myself well enough: I simply want to retrieve the newest invoices in descending order, no matter who is the customer. The optimizer did not use an index that I created for that purpose so the query was slow. Tim Ward has already suggested a possible solution. Fulvio Senore ------------------------------------ ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ------------------------------------ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: [email protected] [email protected] <*> To unsubscribe from this group, send an email to: [email protected] <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
