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

Reply via email to