On Fri, 30 Apr 2010 17:14:06 -0500, mos <mo...@fastmail.fm> 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
