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

Reply via email to