InnoDB 列中文本的高效 MySQL SELECT
我有一个大型表（InnoDB），它本质上有一个位置名称以及该位置的纬度和经度。我的查询类似于
SELECT columns FROM table
WHERE latitude BETWEEN latMin AND latMax
AND longitude BETWEEN longMin AND longMax
AND location LIKE '%mcdonalds%'
唯一索引是自动递增列的主索引。
我更喜欢使用 InnoDB，所以 FULLTEXT 不是一个选项
我尝试的一个选项是将索引添加到纬度和经度，然后
SELECT id FROM table WHERE
latitude BETWEEN latMin AND latMax
AND longitude BETWEEN longMin AND longMax
我就这样做了
$stringOfIds = implode(",",$result);
SELECT columns FROM table WHERE id IN ($stringOfIds)
AND location LIKE '%mcdonalds%'
但是它的可扩展性不是很好，因为 $result 可以有数千个条目并占用太多内存
目前，我已采用全表扫描（上面的第一个查询），但每个查询需要 0.5-1 秒，因此我们将不胜感激。
谢谢
I have a large large table (InnoDB) that essentially has a location name with the location's latitude and longitude. my query is something like
SELECT columns FROM table
WHERE latitude BETWEEN latMin AND latMax
AND longitude BETWEEN longMin AND longMax
AND location LIKE '%mcdonalds%'
the only index is the primary index for the auto incrementing column.
I would prefer to use InnoDB so FULLTEXT isn't an option
One option I tried was adding indexes to the latitude and longitude, and doing
SELECT id FROM table WHERE
latitude BETWEEN latMin AND latMax
AND longitude BETWEEN longMin AND longMax
then i do
$stringOfIds = implode(",",$result);
SELECT columns FROM table WHERE id IN ($stringOfIds)
AND location LIKE '%mcdonalds%'
But it's not very scalable because the $result can have thousands of entries and takes too much memory
For now, I have resorted to a full table scan (the first query above) but it takes 0.5-1 seconds per query so any help would be appreciated.
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
标准 B-TREE 索引不太适合此类查询。我建议更改您的设计以使用地理类型，然后 创建空间索引。然后，您可以将此索引与
MBRContains快速查找位于边界框内的所有点。更新：正如评论中指出的那样，创建空间索引需要 MyISAM。
The standard B-TREE index is not well suited to this sort of query. I'd recommend changing your design to use the geography type and then creating a SPATIAL index. You can then use this index with
MBRContainsto quickly find all the points that lie within your bounding box.Update: Creating a spatial index requires MyISAM as pointed out in the comments.