At 04:54 PM 4/30/2010, you wrote:
I have a table with over 8 million rows of lat / long data all currently in
decimal(12,8) format (actually some in float(12,8)).
First question - should I have these all in decimal or float? I can't
think of any reason why I would actually do any math via mysql with this
data. I am just running queries and dumping the reults into a webapp.
I would use decimal because float may round the values slightly so it may
not show up properly in a search.
Example: Is it 55.05 or 55.049999?
Second question - my data is actual distinct rows with a north, east, south
and west lat / long for the boundary of a specific area. I want to search
based on a point lat / long (i.e. WHERE n > lat, s < lat, e > long, w <
long) to find the specific area that the point is located in.
Use Explain in front of your Select statement to see how many indexes it is
using.
You could shorten the sql to something like:
select .... from table where lat between s and n and long between w and e;
For me, this is simpler to read, although MySQL may optimize it to what you
had before.
(Assuming of course you have normalized the lat and long in the table and
don't need to specify east longitude or south latitude etc..)
I tried a simple index with my n,e,s & w but it still takes along time to
run such a query i.e. phpmyadmin times out. Any idea on the best structure
for such a query?
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.
Mike
Thanks,
Dan T
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
