MYSQL: LEFT() not using any index

Question

I just ran into an issue with my mysql using a where condition such as WHERE LEFT(field, 4) = "abcd". I'm currently trying to optimize my queries and i noticed it isn't using any of my defined indices. I thought LEFT() has the potential to use indices while SUBSTRING is not? (i.e. mentioned in the answer to this question: MySQL Left() or SUBSTRING()?)

So in my case the field is called category CHAR(6), in the table image. For testing i have defined a variety of indices:

ALTER TABLE image ADD INDEX `cat` (`category`);
ALTER TABLE image ADD INDEX `cat2` (`category(2)`);
ALTER TABLE image ADD INDEX `cat4` (`category(4)`);

and an primary index on id, a simple index on field type as well as one FULLTEXT on other columns, i don't think they mattassaer.

Although i get the follwing results:

EXPLAIN SELECT * FROM image i0_ WHERE LEFT(category,4) = "0000" LIMIT 0,30

+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | i0_   | ALL  | NULL          | NULL | NULL    | NULL | 617359 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+

Answer 1

Use LIKE instead:

SELECT * FROM image i0_ WHERE category LIKE "0000%" LIMIT 0,30

This will allow it to use the index. From the manual:

The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character. For example, the following SELECT statements use indexes:

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';