>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