28.07.2014 06:15, Robert martin wrote: > > Have the following (cut down) ordered SQL > > SELECT * > FROM Document d > LEFT JOIN DocumentCreator dc ON dc.DocCreatorRef = d.DocCreatorRef > ORDER BY dc.Description > > There is an Index on dc.Description but it is not used. Plan shown below.. > > PLAN SORT (JOIN (D NATURAL, DC INDEX (DOCUMENTCREATOR_PK)))
Correct, such a query cannot use an index for ordering. > 555431 fetches, 0 marks, 2206 reads, 0 writes. > 0 inserts, 0 updates, 0 deletes, 77830 index, 79193 seq. > Delta memory: 680 bytes. > Total execution time: 0.962s vs > SELECT * > FROM Document d > JOIN DocumentCreator dc ON dc.DocCreatorRef = d.DocCreatorRef > ORDER BY dc.Description > > PLAN JOIN (DC ORDER DOCCREATOR_DESCRIPTION, D INDEX > (DOCUMENT_DOCCREATORREF)) > > 527 fetches, 0 marks, 30 reads, 0 writes. > 0 inserts, 0 updates, 0 deletes, 242 index, 0 seq. > Delta memory: -3141376 bytes. > Total execution time: 0.062s 7000 records inside DocumentCreator don't match 242 index reads. I bet you didn't fetch all the records produces by the query. Performing a complete fetch or testing with select(*) instead of select * may show you quite different timings. Dmitry ------------------------------------ ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 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: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
