It is a good idea to divide into a sector. I will try that. But according to your suggestion, using LIKE is not advisable, since if i create an index (category, sector), this index will not be applicable for LIKE.
Thanks. On Wed, Jun 15, 2011 at 11:35 AM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 15 Jun 2011, at 3:56am, Hoang Linh Duong wrote: > > > 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? > > I would like to check that you are calculating the distance to different > points most times. So the example you gave is just one example, and the > next time you do the calculation you will calculate the distance to a > different place. > > After that I notice that you are interested only in the closest hundred > points. Since you have 600K records, you can reject most of them very > quickly if they are a long way away from your target place. So what you > might do is divide up your area into sectors. Make a table of sectors, and > another table which says which sectors are nearby (in case the target is > near the edge of a sector. So for example if your sectors are > > ABCDE > FGHIJ > KLMNO > PQRST > > Then if your target point is in sector D, then you only need to look for > points in sectors CDEHIJ. You can store a 'closeSectors' column in your > Location TABLE like this > > name type category x y closeSectors > ABFG > ABCFGH > BCDGHI > … … > > Every time you save a new row in Location, work out the sectors which are > close to it (you could look them up in another table) and save the > 'closeSectors' column too. Then when you come to do your SELECT, add in > this clause: > > ... WHERE category = 17 AND closeSectors LIKE '%C%' ... > > You can calculate the '%C%' first from another table with maximum and > minimum X and Y coordinates in. > > For details on 'LIKE' expressions see half way down > > <http://www.sqlite.org/lang_expr.html> > > 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
