I'm really not sure what to try next. ps -aux shows MySQL as hogging
the CPU, not PHP or Terminal:
When this happens, do a 'SHOW PROCESSLIST' in mysql to see what it's
doing.
I have, and I can't see anything unusual. There are a few scripts
that loop with very slow overhead (with sufficien
Not being very strong at math, I have a little problem that I'm not
sure how to solve. Maybe someone can help me.
Basically, given a point (latitude, longitude) and a radius (100
meters) (think circle), I need to compute an equivalent square: That
is, two points that would correspond to two
Sometimes, when I run a query on a table, the index will be used. But
if I run the SAME query, but on a larger set of rows (e.g., where id
BETWEEN 1 AND 10 vs WHERE id BETWEEN 1 AND 500), the index is not
used. (EXPLAIN tells me this.) And as a result, the query runs slow.
The question
Spatial Extensions in MySQL 5.0.16 (the version I'm on) seem really
slow. I must be missing something, but can't figure out what. Some
background:
I'm trying to speed up a query that fetches the nearest 10 records
(essentially, latitude/longitude pairs, stored in a Geometry point
column)
Okay, now that I have your attention. :-) What I actually mean is,
Spatial Extensions in MySQL 5.0.16 (the version I'm on) seem so slow
as to appear broken. Why:
I'm trying to speed up a query that fetches the nearest 10 records
(essentially, latitude/longitude pairs, stored in a Geometry
Here's the table:
CREATE TABLE `lsd` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`latitude` decimal(10,8) NOT NULL,
`longitude` decimal(12,8) NOT NULL,
`coordinates` point NOT NULL,
PRIMARY KEY (`id`),
SPATIAL KEY `latlng` (`coordinates`(32))
) ENGINE=MyISAM DEFAULT CHARSE
( [startlng] - longitude ) * COS( latitude /
57.3 ) , 2 ) ) AS distance FROM foobar ORDER BY distance ASC
where [starlat] and [startlng] is the position where to start
messuring the distance.
On 2/3/07, M5 <[EMAIL PROTECTED]> wrote:
Thanks for the reply... It returns results, but not exactly
tude<=givenvalue AND a.longitude<=givenvalue
LIMIT 0,5)
UNION
(SELECT * FROM (SELECT * FROM tab_name ORDER BY latitute ASC,
longitude ASC) b
WHERE b.latitude>givenvalue AND b.longitude>givenvalue
LIMIT 0,5)
;
Thanks,
ViSolveDB Team
- Original Message - From: "M5" &l
Simply put, I have a table of ~800,000 records containing, among
other things, latitude and longitude values. Given a lat/lng pair, I
would like to SELECT from this table the 10 rows containing latitude/
longitude values closest to the given lat/lng pair. Possible?
...Rene
--
MySQL General
