Effecient MySQL SELECT for text in columns in InnoDB

Question

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

Answer 1

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 MBRContains to quickly find all the points that lie within your bounding box.

Update: Creating a spatial index requires MyISAM as pointed out in the comments.