Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (nike.apache.org: local policy)
Message-ID: <4A7842C1.2030800@amberpoint.com>
Date: Tue, 04 Aug 2009 07:16:33 -0700
From: Bryan Pendleton <bpendleton@amberpoint.com>
User-Agent: Thunderbird 2.0.0.22 (Windows/20090605)
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: Filtering using the sort order on two columns
References: <bb132a4a0908032330l40a116fds2f6ab88adc95e9a1@mail.gmail.com>
In-Reply-To: <bb132a4a0908032330l40a116fds2f6ab88adc95e9a1@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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