Thank you, using a left outer join did the trick! Now the query uses the descending index and it is much faster.
I agree that it is not an optimal solution but for this database an outer join will return the same rows as an inner join (no nulls) so it solves my problem. Again, thank you for your quick help. Fulvio Senore Il 03/02/2015 13:21, Tim Ward [email protected] [firebird-support] ha scritto: > 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 [email protected] [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 > ------------------------------------ ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 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/
