MySQL is Not Using Index Key on Field - How do I resolve?

If you need to speed up your MySQL DB, you're adding indices on your columns. You will see a massive, immediate improvement in performance.

But sometimes you won't, and there are a number of ways to diagnose and resolve those problems.

1) Run the Analyze command on the table. For instance...

MyDB@sys mysql> ANALYZE `MyTable`

This tells your MySQL storage engine to look at the possible ways to join or select this table.

2) MySQL is using a different keys than the one you specified indices for. If your where clause is on multiple statements, you may want a compound index to make it more clear to the storage engine...

MyDB@sys mysql> ALTER TABLE `MyTable`
ADD INDEX `ComboIndex` (`Column1`,`Column2`)

And this will work with...

MyDB@sys mysql> SELECT .... WHERE
Column1 = "ABC" OR COLUMN2 = "DEF";

3) Your query may be simply too complicated for MySQL to grasp. In this case, simplifying the query into multiple UNION'd statements will produce the desired output at amazing speed...

MyDB@sys mysql> (SELECT .... WHERE
Column1 = "ABC") UNION (SELECT .... WHERE COLUMN2 = "DEF");