Re: Filtering using the sort order on two columns

[Bryan Pendleton]

Daniel Noll wrote:

   SELECT field, text, docfreq
   FROM Terms
   WHERE (field = ? AND text > ?) OR field > ?
   ORDER BY field ASC, text ASC

This does the right thing as far as the output is concerned, but Derby

does a table scan.

What happens if you do two separate queries:

    SELECT field, text, docfreq    FROM Terms
    WHERE field > ?
    ORDER BY field ASC, text ASC

    SELECT field, text, docfreq    FROM Terms
    WHERE (field = ? AND text > ?)
    ORDER BY field ASC, text ASC

Sometimes the "OR" will really throw off the optimizer.

Does each query, separately, use the index as you expect?

thanks,

bryan