MySQL索引在字符串的第一部分 [英] MySQL Index on first part of string

问题描述

我在MySQL中查询一个包含category_id，subcategory_id和zipcode的非常大的表（超过3M记录）。 zip中的zip可能是也可能不是10个字符。

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.

目的是获取指定zip的某个半径内的所有cat / subcat项目。我有一个函数返回一个指定的5位拉链列表。然后将此列表提供给我的查询，如此...

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)

我在cat_id，subcat_id和zip上有一个复合索引，但在某些情况下，zip为10个字符可能会将其丢弃。我能否指数LEFT（拉链，5）不知何故？

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?

推荐答案

您应该有正常的5位数邮政编码和列列使用所有额外的数字，让SQL正常处理它。有一些方法可以做你所说的，但这是迄今为止最有效的解决方案。

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.

这篇关于MySQL索引在字符串的第一部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！