I have a table: CREATE TABLE vals2d (x INTEGER, y INTEGER, val INTEGER); CREATE UNIQUE INDEX xy ON vals2d (x,y);
EXPLAIN QUERY PLAN SELECT * FROM vals2d WHERE x = 1 OR y = 1; produces 0|0|TABLE vals2d The index is unused. x,y are unique together so I built these indexes: CREATE INDEX x ON vals2d (x); CREATE INDEX y ON vals2d (y); I run the explain again and get the same results: no indexes are used. Is there any index I could build that would get used in that situation? EXPLAIN QUERY PLAN SELECT * FROM vals2d WHERE (x > 1 AND x < 10) AND (y > 1 AND y < 10); produces 0|0|TABLE vals2d WITH INDEX y I drop the y index and then it uses the x index. I drop the x index and then it uses the xy index. Can it not use two indexes simultaneously? And if it can only use one, wouldn't the xy index be the best choice as it has information about both x and y? Thanks for your time. ______________________________ Brannon King ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
