>Hi all, > >Is it the designed behavior that indexes are not used in subquery? >If it is desined at present, the function for index in subquery will be >implemented in versions of the future?
Of course indexes can be used in subqueries, Shingo, if not, Firebird wouldn't be much of a database. As Karol says, we may be able to help if you show us query and plan. I think it also would be good to know the number of records in all tables involved and the selectivity of the index you want to be used and other indexes used in your query. Having said all that, I was somewhat surprised about one query I issued yesterday: SELECT max(MyField) FROM TableA UNION SELECT max(MyField) FROM TableB used my descending indexes on MyField. However: SELECT 'TableA' as TableName, max(MyField) FROM TableA group by 1 UNION SELECT 'TableB', max(MyField) FROM TableB group by 1 used NATURAL for both tables (although surprised, it is understandable since my descending index on MyField would be difficult to use (and maybe useless) if I did SELECT <NonIndexedField>, max(MyField>...). Hence, I changed my query to with TA(MyField) as (SELECT max(MyField) FROM TableA), TB(MyField) as (SELECT max(MyField) FROM TableB) SELECT 'TableA' as TableName, MyField FROM TA UNION SELECT 'TableB', MyField FROM TB to get the indexes back. Set
