Subject | Re: [ib-support] Use of Index |
---|---|
Author | Svein Erling Tysvær |
Post date | 2001-09-17T07:05:02Z |
>Why is in the first query the index on the primary field of t_auftrag notused?
Because you have no WHERE clause in the main part of your query and it is
faster not to use an index. The subselect is executed once for every row of
t_auftrag, so your query is most likely very slow.
>Can I force Firebird to use it?No, but you can improve your SQL by two different approaches:
1) Quite a bit faster:
SELECT id_auftrag FROM t_auftrag
WHERE EXISTS (
select 1
from t_apos
where f_id_auftrag = id_auftrag AND f_id_auftrag = 2)
This will prevent IB from returning the entire result set from t_apos for
every row of t_auftrag. But still I recommend
2) The fastest and most normal approach:
SELECT id_auftrag
FROM t_auftrag
JOIN t_apos ON id_auftrag = f_id_auftrag
WHERE f_id_auftrag = 2
HTH,
Set