db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel Noll <dan...@nuix.com>
Subject Re: Filtering using the sort order on two columns
Date Wed, 05 Aug 2009 06:07:58 GMT
On Wed, Aug 5, 2009 at 14:54, Bryan Pendleton<bpendleton@amberpoint.com> wrote:
> Great!
>
> Can you construct a UNION of the two queries, and then ORDER BY the UNION?
>
> And, in that case, does it use the index for both the union-ed subqueries?

This is the first time I've used UNION, but I tried this:

    SELECT id, field, text, docfreq FROM Terms
    WHERE field = ? AND text > ?
    UNION ALL
    SELECT id, field, text, docfreq FROM Terms
    WHERE field > ?
    ORDER BY field ASC, text ASC

And yes, it is still doing two separate index scans in this situation,
but code-wise it's far superior to doing each query separately as I
don't need to keep track of how many items I got from the first query.
 The performance is basically identical.

Daniel



-- 
Daniel Noll                            Forensic and eDiscovery Software
Senior Developer                              The world's most advanced
Nuix                                                email data analysis
http://nuix.com/                                and eDiscovery software

Mime
View raw message