I do have Index for category, but im wondering how to create index (category, distance) since distance is a new column created in a query.
Thanks. On Tue, Jun 14, 2011 at 7:11 PM, Martin.Engelschalk < engelsch...@codeswift.com> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
