>secondary index on ID,ADATE,NUMBER > >First example (slow) > >SELECT * FROM table >WHERE (ID BETWEEN 1000 AND 1000) > AND (ADATE BETWEEN '01.01.2013' AND '01.01.2013') >ORDER BY ID,ADATE,NUMBER >Elapsed time = 9 seconds resultset 8 records. >Plan shows firebird use correct index INDEX ID,ADATE,NUMBER. > >Second example (rather quick) > >SELECT * FROM table >WHERE (ID = 1000) > AND (ADATE = '01.01.2013') >ORDER BY ID,ADATE,NUMBER >Elapsed time = 0.09 seconds
One thing you cannot see by using a composite index, is which part of the index is in use. In your case, it means that you cannot see why example A is slow. Ann has shown you how Firebird actually would use the index and why example A is slow. Using separate indexes for each field has the benefit of making it easier to see why things are slow. In your particular case, it probably wouldn't have seen the difference, since I'd expect both examples to be quick with separate indexes (unlike most other databases, Firebird can use several indexes for each tuple in a query), maybe not 0.09 seconds, but I'd be greatly surprised if it took more than 0.2 seconds. Another thing that can hide things from you, is your use of ORDER BY. Generally, the way you use ORDER BY may be good for production, but when trying to see if the index is used for the WHERE or JOIN clause, it might be better either not to include the ORDER BY or adding +0 or something to avoid seeing indexes used for ordering the result set (if you delete your WHERE clause in your examples, the index might still to be used due to the ORDER BY). HTH, Set
