--- In firebird-support@yahoogroups.com, "svanderclock" <svanderclock@...> wrote:

>
>
> > Once again: you don't.
> > Indexes are useful for data filtering. For ordering they are less useful.
>
> what???? of course not !! for ordering index can be very very important !!

I would ALSO imagine that the optimizer would take advantage of indexes for ORDER BY operations, not just for data filtering. If this is not the case, it is extremely discouraging. I have not done any performance testing yet but I sure as heck will do as I have some time available.



In my case, I have parent and children records and I want to show a single parent record and a subset of it's children for example:

PARENT RECORD
- CHILD 1
...
- CHILD 20

The web application currently has a user interface that has these options:

Dropdown Menu Options:
- Show oldest child records first
- Show newest child records first
- Only child records where total > 100

PREVIOUS PAGE 1, 2, 3, 4, 5, 6, 7, 8, 9, 10... NEXT PAGE


* I use the ORDER BY clause to order the child recs by MYTIMESTAMP field in either ASC or DESC order.

* I use the WHERE clause to select children with TOTAL > 100.

* I use the ROWS clause to display to 20 records at a time and navigate from page to page.


These are my assumptions and please correct me if I am wrong:

1. Creating 2 indexes for the MYTIMESTAMP field will optimize both the ORDER BY MYTIMESTAMP ASC and ORDER BY MYTIMESTAMP DESC clauses.

2. Creating an index on the TOTAL (integer) field will optimize the WHERE TOTAL > 100 clause.

3. The ROWS clause is not optimizable. ROWS is simply be used to limit the selection of records such as recs 1-20, 20-40, 40-60, etc.

Again, please correct me if I am wrong about this.

Thank you