Hi, make sure you have an index on category and distance, like
create index MyIndex on Location (category, distance) Because many records fulfill the category=17 condition, and if database size is an issue, an index only on distance might help also. Martin Am 14.06.2011 13:01, schrieb Hoang Linh Duong: > Hi all, > > I have one SQL as below: > > SELECT name, type, category, x, y, ((x-645529)*(x-645529) + > (y-1494293)*(y-1494293)) AS distance, > FROM Location > WHERE category=17 > ORDER BY distance > LIMIT 100 > > I run this SQL to retrieve data from my SQLITE database and it takes more > than 1 minutes. Noted that my program is running on Win CE 6.0 device (ARM > 532MHz, 128MB RAM). And there are 600000 records in Location table, among > that 110000 records of type=17. > > I am just wondering if there is any way to speed up the SQL. I appreciate > all advices and suggestions. Thank you! > > Regards, > Linh > _______________________________________________ > 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
