CODEWITHSUNDEEP
mysqllatitude-longitude
rikkitikkitumbo
rikkitikkitumbo

Reputation: 976

mysql geolat geolng multi indexed query

I have a table with fields 'lat', and 'lng'. Both are pretty much continuous, meaning they don't repeat themselves much. This has led me to believe that making a multi-column index for lat and lng wouldn't really help me. What I'd LIKE to do is this:

Make an index on both lat and lng, and then perform a query like:

select from tableName where 
lat >= 13.1232 and lat <=14.123 and 
lng >=-80.123 and lng <=-79.232 and 
name like '%greg%'

and have mysql perform this process:

  1. select all LATs between 14.1232 and 13.123 (this should be indexed, and fast)

  2. within the group that step#1 found, perform step#2: find lngs <=-80.123 and lngs>= -79.232 (this should also be indexed and very fast)

3.within the group created by steps #1 and #2... perform the more time-consuming key-word search.

How can I do this? I'm pretty sure that the first part of the query (the indexed lat) is narrowing things down for me... but after that I'm not sure... and this is what I've been struggling to find in the docs

Upvotes: 2

Views: 132

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562260

MySQL handles conventional B-tree indexes like most implementations: The index helps only the range condition on the leftmost columns in the index.

The analogy I use is to a telephone book. If I search for a specific last name, first name pair like "Smith, John" the index helps. My search for the last name "Smith" is quick, and within the Smiths, the search for "John" is quick.

But if I search for a range condition like "all people whose last name begins with 'S'", then I get a subset of the telephone book but not all the people named "John" are sorted together. They're scattered through the subset I selected based on last name.

It's for this reason that MySQL searches a B-tree index up to the first range condition, and then does not use the index any further. You can still make conditions for the other dimension, but it will do a manual search through all the rows matched by the first dimension.

In other words, even if you have a compound index on (lat, long), MySQL will not use the long part of the index:

select ... from tableName 
where lat >= 14.1232 and lat <=13.123 /* index-assisted */
  and lng >=-80.123 and lng <=-79.232 /* full scan */
  and name like '%greg%'              /* pattern search never uses index anyway */

(By the way, your lat condition can never be true as you have written it, but I'll assume you mean the numbers to be reversed.)

This makes it inefficient to do latitude & longitude conditions, since both are search for a range of values.

For this reason, MySQL has another type of index, which is not a B-tree index. It's a SPATIAL index, which does support multiple range conditions.

CREATE TABLE mytable (
  name TEXT NOT NULL, 
  coord POINT NOT NULL,
  SPATIAL INDEX (coord)
);

INSERT INTO mytable (name, coord) 
VALUES ('name', ST_GeomFromText('POINT(14.0 -80)'));

SELECT name FROM mytable 
WHERE MBRContains(
  ST_GeomFromText('Polygon((
    13.123 -80.123,
    14.1232 -80.123,
    14.1232 -79.232,
    13.123 -79.232,
    13.123 -80.123))'),
  coord);

Yes, this is more complex, but it's the only way you can get truly index-optimized latitude/longitude searches.

Read more about it here: http://dev.mysql.com/doc/refman/5.7/en/using-spatial-data.html

Upvotes: 2

mba12
mba12

Reputation: 2792

If you absolutely want each where clause to limit the result set in order you could try something like this but an sql optimizer might change things under the cover. I think a good index or two is still your best bet but I believe this is what you are asking for. I recommend Explain Plan to optimize your queries.

select * from
(
    select * from 
        (
        select * from tableName 
        where lat >= 14.1232 and lat <=13.123 
        )
    where lng >=-80.123 and lng <=-79.232
)
where name like '%greg%'

Upvotes: 1

Related Questions