db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bryan Pendleton <bpendle...@amberpoint.com>
Subject Re: Filtering using the sort order on two columns
Date Tue, 04 Aug 2009 14:16:33 GMT
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


Mime
View raw message