<div>So this is what's happening here.  I'll explain what it's doing for each query, then show you the optimal way.</div><div><br></div><div>SELECT MAX(<a href="http://sample.id">sample.id</a>) FROM sample JOIN interface ON (sample.interface=<a href="http://interface.id">interface.id</a>) WHERE <a href="http://interface.name">interface.name</a>='accounting-total'</div>
<div>-- This is just a symptom of the optimizer being stupid.  It starts looking at the keys for the table, sees the first one, and notices that it is a covering index - and therefore it choses to use it without considering that there might be a better one.  It is a covering index because in innodb, the primary key is appended to every secondary key, so KEY (`interface`,`timestamp`) actually stores (`interface`,`timestamp`,`id`)...also note that where you have KEY (`interface`,`id`), you really only need to say KEY (`interface`), for this same reason.  The problem is that it's caring so much about using a key to filter that it doesn't notice there's one that could be used to filter AND get the max(id) easily.  That's just the mysql optimizer for you.  Newer versions tend to be a bit smarter, but you'll always see this kind of thing.  I bet if you switched what order those keys were in in the file, it'd find the correct one first and use it.</div>
<div><br></div><div>SELECT MAX(<a href="http://sample.id">sample.id</a>) FROM sample WHERE interface=(SELECT <a href="http://interface.id">interface.id</a> FROM interface WHERE <a href="http://interface.name">interface.name</a>='accounting-total')</div>
<div>-- This one is using a one-time (independent) subquery, or "derived table."  As it only has to execute that statement once, it's very fast.  This is basically what mysql would do if the optimizer handled it right, but without join syntax optimization, so it's still not perfect.  It's the closest you can get without index hints or changing the indexing.</div>
<div><br></div><div>SELECT MAX(<a href="http://sample.id">sample.id</a>) FROM sample WHERE interface IN (SELECT <a href="http://interface.id">interface.id</a> FROM interface WHERE <a href="http://interface.name">interface.name</a>='accounting-total')</div>
<div>-- This is a BIG no-no.  It's using a dependent subquery - note how saying IN implies multiple values could be returned and a range check is needed.  It also implies that the subquery cannot be solved before the main table is accessed, so no "derived table" use.  In this case, it has to scan the ENTIRE key on the sample table, and for EVERY record it must run that subquery - you're basically executing over 6 million separate queries in this one statement.  This is the slowest method.  Beware when you see "Dependent subquery" in an explain plan - there is usually a better way to go about it.</div>
<div><br></div><div>So in conclusion, the best way to fix it is to just tell the optimizer to use the correct index.  Either of the following two should work:</div><div>SELECT MAX(<a href="http://sample.id">sample.id</a>) FROM sample FORCE INDEX (sample_interface_id) WHERE interface=(SELECT <a href="http://interface.id">interface.id</a> FROM interface WHERE <a href="http://interface.name">interface.name</a>='accounting-total')</div>
<div>-- explicitly specifying the key</div><div>SELECT MAX(<a href="http://sample.id">sample.id</a>) FROM sample IGNORE INDEX (sample_timestamp) WHERE interface=(SELECT <a href="http://interface.id">interface.id</a> FROM interface WHERE <a href="http://interface.name">interface.name</a>='accounting-total')</div>
<div>-- telling it to skip that first key and find the next usable one</div>