Ability to specify index for query build from

Something like "use index" should be extremely rare in your queries, because it indicates that somehow you've found a situation in which your database engine is doing it wrong. In most cases, you would be better off optimizing your query or adding another index.

Also, I found a rather old article (2006) which mentions this situation (migrating to InnoDB and having noticeably slower count() queries) and points out that this should only apply to count() queries without a where clause. A little more digging turned up a slightly more recent article (2009) which more or less points people in the direction you seem to have taken.

Generally, not specifying a where clause would also be a common reason for a query optimizer to not use an index for your query (especially in combination with not specifying the indexed columns in the select clause).

Another item I found was a StackOverflow answer which mentioned creating an additional non-primary index on the primary key column for tables with potentially large fields (like text columns) when InnoDB uses clustered primary keys. ( http://stackoverflow.com/questions/51182...ere-clause ). I even managed to find a (still-older) post defining the row size which is likely to cause a secondary index to be faster than the default index on the primary key: > 450 bytes ( http://optimmysql.blogspot.com/2007/07/i...dexes.html ).

Bonfire

Practical CodeIgniter 3
CodeIgniter Testing Guide