www.greg-wright.co.uk

Running the following query:

SELECT orderId FROM orders where orderRef=737357947879;

Was taking 2 seconds, despite the key on orders.orderRef
Explain showed it was ignoring the possible key and FORCE INDEX did nothing.

It turned out that orderRef was a varchar (This was not my schema I hasten to point out - storing integers in varchars is definitely a bad idea!)

SELECT orderId FROM orders where orderRef='737357947879';

was almost instantaneous

MySQL was doing a full table scan casting orderRef to an integer at each step then matching with the integer I was passing in.

Doh!

Which begs the question, why didn't MySQL just cast the integer in the query to a varchar?