Why Isn't This Index Being Applied?

[Andrew West]

Hi everyone, it's kind of hard to search to see if this kind of thing has been covered before, so I'll try to precisely/concisely explain my situation.

First the table in question:

CREATE TABLE `S-BGP`.`pfx2as` (
`ASN` int(10) unsigned NOT NULL,
`IP_BEG` int(10) unsigned NOT NULL,
`IP_END` int(10) unsigned NOT NULL,
`HOMES` smallint(5) unsigned NOT NULL,
`ACTIVE` tinyint(1) NOT NULL,
`TS_IN` int(10) unsigned NOT NULL,
`TS_OUT` int(10) unsigned NOT NULL,
KEY `update_ind` USING BTREE (`ACTIVE`,`IP_BEG`),
KEY `match_ind` (`IP_BEG`,`IP_END`,`TS_IN`,`TS_OUT`)
) ENGINE=MyISAM DEFAULT CHARSET=latin

And now the query code (Note that question marks 1 and 2 will always be the same value, and likewise with marks 3 and 4):

String query_pfx = "SELECT ASN FROM " + utils_db.table_pfx;
query_pfx += " WHERE IP_BEG <= ? AND IP_END >= ? AND ";
query_pfx += "TS_IN <= ? AND (TS_OUT >= ? OR TS_OUT=0)";
pstmt_qry_pfx = con.prepareStatement(query_pfx, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

So when I run the query, I'd expect index 'match_ind' to be used in the query. When I run a test query using "EXPLAIN" -- its a possible index, but it isn't actually being used. Why not?

Your help is appreciated. Remember, I'm a bit of a newbie at this...
Thanks, -A



Edited 3 time(s). Last edit at 06/08/2009 01:50PM by Andrew West.