List:General Discussion« Previous MessageNext Message »
From:dan Date:May 1 2010 5:06am
Subject:Re: Best index for searching on lat / long data i.e. decimal vs. float
View as plain text  
On Fri, 30 Apr 2010 17:14:06 -0500, mos <mos99@stripped> wrote:
> At 04:54 PM 4/30/2010, you wrote:
> 
> Use Explain in front of your Select statement to see how many indexes it
is

mysql> explain SELECT * FROM `grid` WHERE n > 49.012 AND s < 49.012 AND e >
110.0244 AND w < 110.0244;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  |
rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | grid  | ALL  | section       | NULL | NULL    | NULL |
4155232 | Using where | 
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
 
> using.
> You could shorten the sql to something like:
> 
> select .... from table where lat between s and n and long between w and
e;

Nope that results in an empty set.  I don't think you can do that - it has
to be... n between 49 and 50.

> What version of MySQL are you using? MySQL may be using only one index so

> I'd recommend making a compound index of all 4 columns:
> n,s,e,w columns. Now MySQL only has to traverse the single index and
won't 
> have to access the data records to satisfy the query.

v5.0.89

I have that index already named 'section'

This query works:

SELECT * FROM `grid` WHERE n > 49.012 AND s < 49.012 AND e > 110.0245 AND w
< 110.0245; but takes an average of 15 seconds

Dan
Thread
Best index for searching on lat / long data i.e. decimal vs. floatdan30 Apr
  • Re: Best index for searching on lat / long data i.e. decimalvs. floatmos1 May
    • Re: Best index for searching on lat / long data i.e. decimal vs. floatdan1 May
Re: Best index for searching on lat / long data i.e. decimal vs. floatdan2 May