Skip navigation links

MySQL Forums :: Newbie :: Why Isn't This Index Being Applied?


Advanced Search

Why Isn't This Index Being Applied?
Posted by: Andrew West ()
Date: June 07, 2009 09:14PM

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.

Options: ReplyQuote


Subject Written By Posted
Why Isn't This Index Being Applied? Andrew West 06/07/2009 09:14PM
Re: Why Isn't This Index Being Applied? Andrew West 06/08/2009 02:57PM
Re: Why Isn't This Index Being Applied? Rick James 06/09/2009 11:17PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.