I have a table with a definition something like this….
CREATE TABLE foo ( A CHAR(6), B TIMESTAMP, Lots more fields…… )
It contains about 600 million records.
Originally it had these two indexes on it….
CREATE INDEX i1 ON foo (A); CREATE INDEX i2 ON foo (B);
Most of the queries I run look something like this…
SELECT * FROM foo WHERE A = ‘ABCDEF’ AND B > ‘some date’ AND B < ‘some other date’
I wanted to improve the performance so ( following advice on this forum) I added this compound index (previous two indexes still remain)….
CREATE INDEX i3 ON foo (A,B);
When I do an EXPLAIN it’s apparent that the new index isn’t being used, but index 'B' is being used.
This leads to some questions… 1) Why isn’t the new index being used? 2) Can I disable my first two indexes to try and force the compound index to be used? (I don’t want to DROP the indexes because they’re likely to days to re-create them) 3) Does the order of the WHERE clauses make any difference? Would a compound index on A,B be any different from one on B,A? 4) how can I improve performance on this query?