MySQL Index on first part of string

Question

I'm querying a very large table (over 3M records) in MySQL that has a category_id, subcategory_id and zipcode. The zip may or may not be 10 characters in the db.

The purpose is to get all the cat/subcat items w/in a certain radius of the specified zip. I have a function that returns a list of 5-digit zips for one specified. This list is then fed to my query like so...

SELECT whatever
FROM tblName
WHERE cat_id = 1
AND subcat_id = 5
AND LEFT(zip,5) IN (11111,22222,33333,44444,55555)

I have a compound index on cat_id, subcat_id and zip, but the zip being 10 characters in some cases may be throwing it off. Can I index the LEFT(zip,5) somehow?

Answer 1

To answer your question directly: yes, you can index left(zip, 5).

alter table tblName add index (zip(5));

And if you want the query to be able to use the index to search all columns:

alter table tblName add index (cat_id, subcat_id, zip(5));

Answer 2

You should have a column with the normal 5 digit zip and column with all of the extra digits and let SQL handle it normally. There are ways you could do what your talking about, but this is by far the most efficient solution.