Well, I hadn't known about the spatial features of MySQL. If you're ok
using vendor extensions then that definitely looks like the way to go:
http://dev.mysql.com/doc/refman/5.0/en/gis-introduction.html
A
On Apr 24, Nick Hill wrote:
Hello Adam
Adam Wolff wrote:
Actually runs through the table four times instead of twice, and maybe
can't even use the index for the whole query.
Assuming my results are not typical of MySQL query times, this would explain
the sqrt() relationship of returned rows to query time.
I have tried your suggestions of using a sub-query and have had trouble
getting the syntax valid. But on using explain, it seems that 4 bytes of the
index (either lat or lon) are being used and a brute force search on the index
for the other constraint.
If the query is returning 25600 points from a 100m dataset, it is brute
seaching through 1.6m records in the second part of the index.
If it were an option of creating 2 1.6M lists then looking for commonalities,
it may be faster to instead use 1 1.6m item list then brute force constraint
search.
I have received suggestions to use spatial indexes, which I am looking into.
Alternatively, I could optimise queries by creating multiple slices of the
data set accross one axis then use a key on the other axis. MySQL 5.1
partitioning scheme may help.
7 messages in com.mysql.lists.mysqlRe: Optimising for many rows and retu...