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
