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
