Brannon King <brannonking-/[EMAIL PROTECTED]>
wrote:
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.
Index on (x, y) allows an efficient search on x, and on x and y
together, but it does not help at all when searching on y alone. You
would probably be better served by two separate indexes, one on x and
another on y.
However, even with separate indexes, OR operator generally precludes the
engine from using an index. The difficulty is in not double-reporting
rows that have both x=1 and y=1. If you know your table does not have
such rows, you would get better performance with
select * from vals2d where x=1
union all
select * from vals2d where y=1
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?
No. Try doing it yourself with pen and paper, and you'll see that having
two indexes does not help at all in this case. You can use indexes to
quickly find all records where x is in range, and all records where y is
in range, but indexes don't help at all in finding the intersection of
these two sets.
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?
Again, try it with pen and paper. An index on (x, y) can help with the
following checks:
x = A
x between A1 and A2
x = A and y = B
x = A and y between B1 and B2
but not with any others.
Igor Tandetnik
