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 (athena.apache.org: domain of opencoeli@gmail.com
 designates 66.249.92.171 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=subject:from:reply-to:to:content-type:date:message-id:mime-version
         :x-mailer:content-transfer-encoding;
        b=Hmu4Qet81GRdjJWlyVfFpvSkg/cHbnKoDC9xMVTRudMEWdzI7MizD9xrX/IR6O25gD
         Rz6pn7JvK2FvWJUpoZDO+OJ/7TelFCkrzuWWMFdbzKLkRrNlrUiUURS5sANJXL5+9niQ
         Kf489W1eiDEiux+R1gADpK/TFzxAirUzEvRzQ=
Subject: Index not used for complex WHERE
From: Jan Kotek <opencoeli@gmail.com>
Reply-To: opencoeli@gmail.com
To: derby-user@db.apache.org
Content-Type: text/plain
Date: Mon, 15 Sep 2008 01:39:04 +0100
Message-Id: <1221439144.8279.16.camel@artemis>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

Hello,

it seems to me that Derby is not using index for more complex WHERE
cause.

Table have only 3 colums, all number. There is only one index on IPIX
column (BIGINT). There is ~100000 records in table.

With simple query:
SELECT obj FROM MilkyWayPixel obj 
  WHERE  (obj.ipix BETWEEN 15284 AND 33739) 

result is returned immediately (0.1s).  But with more complex query it
takes around 3 seconds. This time also grows with number of records. And
it did not change if there is index on IPIX or not. Derby is clearly not
using index in this case. Instead it makes full table scan.

Is there any way how I can optimalize query? 

Complex query where index is not used:
SELECT obj FROM MilkyWayPixel obj 
  WHERE 
 (obj.ipix BETWEEN 15284 AND 33739) 
 AND 
  obj.ipix NOT BETWEEN 15308 AND 15535 AND 
  obj.ipix NOT BETWEEN 15569 AND 15789 AND 
  obj.ipix NOT BETWEEN 32468 AND 32684 AND 
//snip, 50 lines in total...
  obj.ipix NOT BETWEEN 32723 AND 32941 AND 
  obj.ipix NOT BETWEEN 32979 AND 33197 AND 
  obj.ipix NOT BETWEEN 33234 AND 33455 AND 
  obj.ipix NOT BETWEEN 33489 AND 33715

Thanks for advices.
Jan Kotek
http://kotek.net/opencoeli