I have SELECT relating two tables with INNER JOIN , in each of the tables I have about 600,000 records.
In the main table of SELECT , I have two fields of type Date . None of the fields have null values, and there are indexes for both the fields that we'll call here DATA1 and DATA2 .
Look at the situations below:
- When you filter both fields in SQL, the query takes 13 seconds.
-
The two filters are ranges . Example:
WHERE DATA1 >= '04/01/2018' AND DATA1 <= 04/18/2018' -
When you filter only one of the date fields, the query takes only 171 milliseconds.
- Deleting one of the indexes (regardless of whether it is from the DATA1 or DATA2 field) the query is fast too, even filtering the two fields in the SELECT.
- When the query is slow, I noticed that a FULL access is made to both tables.
- When the query is fast, the indexes are used correctly and it does not FULL access in any of the tables.
- I tried to re-index the indexes, but it did not work.
The question is:
- Is it Firebird's problem?
- Or is it a default behavior for any SGDB?
- If I'm using the indexes conceptually in the wrong way, which would be the correct way to index the fields?