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