db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jan Kotek <openco...@gmail.com>
Subject Index not used for complex WHERE
Date Mon, 15 Sep 2008 00:39:04 GMT
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



Mime
View raw message