Hi 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))) 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 If I make it an inner join instead the index is used and performance is great... 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 Document table as about 80000 records and DocumentCreator has about 7000 records. The DocumentCreator table is a lookup field. Documents don't have to have a creator so it has to be a left join, most documents however do have a creator. What can I do with my SQL to 'encourage' Firebird to use this index? Thanks Rob
