I have tried R-Tree, the execution time is even longer. For a trigger to calculate the distance, is it too much a burden if the query is executed very frequently (the table Location is of more than 600k records). I suspect that it may take even longer time if we need to do update & query.
After trying two ways to optimize (use standard query on 1 table, use R-Tree), i still cannot improve execution time (still >1min). Is there any other ways? On Tue, Jun 14, 2011 at 10:56 PM, Jim Morris <jmor...@bearriver.com> wrote: > You might also consider a trigger to calculate the distance once on > insert/update. They you could use an index. > > On 6/14/2011 4:53 AM, Simon Slavin wrote: > > On 14 Jun 2011, at 12:11pm, Martin.Engelschalk wrote: > > > >> make sure you have an index on category and distance, like > >> > >> create index MyIndex on Location (category, distance) > > Peter is calculating distance inside his SELECT, but I agree that an > index on (category) is an excellent idea. > > > > The other thing is that this is a bit like RTREEs. So Peter, you might > like to read > > > > <http://www.sqlite.org/rtree.html> > > > > I don't know if it's worth using rtrees in your particular example but > you might find them useful. > > > > Simon. > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
