Don Don - 1 year ago 69
MySQL Question

MySQL Index on first part of string

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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download