Tuesday, April 29, 2014

mySQL optimization. Index not used. Why?

Recently  i  have found that index is never  used  for searching  in a slow mySQL query,  despite the fact  that  it would be very usefull.  FORCE INDEX did nothing.   The  explain command  still shows ALL.

After  digging  the web i have finally got what  is happening.
While the  field  was VARCHAR  the value in  the  query was integer.
where field_name =123

So  after  quoting  the  value like this [where field_name ="123"]  the  query  runs instantly. 0.001 s on a million of rows.

Epic win .