How do you force MySQL to use a better-performing secondary index without using FORCE INDEX?

It's not necessarily going to be better-performing to use a secondary index over PRIMARY in InnoDB, even if it was better in MyISAM.  In MyISAM, any index lookup is approximately the same (primary vs. secondary).  In InnoDB, primary index lookups are significantly better than a secondary index lookup.  The optimizer could be making the correct choice. Do profile your queries; don't merely guess which query is faster. http://dev.mysql.com/doc/refman/5.5/en/show-profiles.html Do use ANALYZE TABLE as Brian Rue suggests, to make sure the optimizer's statistics are current.  But it doesn't matter how large the table is -- ANALYZE TABLE simply samples 8 random InnoDB pages in any case.  The time it takes is about the same time it takes to run SHOW TABLE STATUS, because both commands do the same sampling (with respect to InnoDB).

It's an interesting situation if you have Primary key in possible keys. That means it is involved in the filtering which as Bill said is very quick in InnoDB due to its storing mechanism (much faster than in MyISAM). MySQL tries its best to quickly return the results and in most cases it does a pretty good job with it. Have you tried to profile the query with and without FORCE INDEX? If you could paste the query and the table schema and the output of 'show indexes from table;' here we could say more about that. By the way you can override the sampling behaviour with innodb_stats_sample_pages which is a dinamyc parameter so you can actually increase it before the analyze table and change it back after it finished. http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_stats_sample_pages