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?

Have you got the query plan for this query? That should tell you how it is currenlty working.should be useful to have that information before thinking about other indexes. — Ed Heal
– Ed Heal, Commented Sep 21, 2011 at 22:27
No you cannot use functions in indexes. You can in Oracle and PostgreSQL, but not in MySQL. — Johan
– Johan, Commented Sep 21, 2011 at 22:27
By "query plan" do you mean run EXPLAIN... on the query? I didn't think about that. Thanks. @Johan: I was thinking about Oracle I think... did PL/SQL a long time ago and thought this idea seemed possible. — Don
– Don, Commented Sep 21, 2011 at 22:37

iateadonut · Accepted Answer · 2016-12-01 01:22:10Z

12

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));

answered Dec 1, 2016 at 1:22

iateadonut

2,27824 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

wwkudu Over a year ago

add index MySQL 5.7 Reference for those that would like to look up the detail

T.H. Over a year ago

For mariaDB, this doc describes how to specify part of column in an index definition, basically it is the same syntax as shown in the accepted answer.

Caimen · Accepted Answer · 2011-09-21 22:40:23Z

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.

edited Sep 21, 2011 at 22:40

answered Sep 21, 2011 at 22:28

Caimen

2,6182 gold badges27 silver badges43 bronze badges

Comments

Johan · Accepted Answer · 2011-09-21 22:30:06Z

1

If you want to use an index, change the query to:

SELECT whatever
FROM tblName
WHERE cat_id = 1
AND subcat_id = 5
AND (   zip LIKE '11111%'
     OR zip LIKE '22222%'
     OR zip LIKE '33333%'
     OR zip LIKE '44444%'
     OR zip LIKE '55555%')

Another option is to denormalize your table (should really be the last option) and add an extra field with that contains the 5 leftmost chars in zip. Then you can do:

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

Don't forget to put an index on field leftzip5.

answered Sep 21, 2011 at 22:30

Johan

77.4k28 gold badges204 silver badges346 bronze badges

1 Comment

Don Over a year ago

Thanks. I thought about this one. It's a table we inherited of physician data (addresses, etc) and the zip is a varchar(255) which is inefficient. I thought about creating a char(5) of the LEFT(x,5) just to make the table smaller...it's 3M records and almost 900MB right now...

Collectives™ on Stack Overflow

MySQL Index on first part of string

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related