Re: [sqlite] SQLITE run slow

On 15 Jun 2011, at 4:55am, Hoang Linh Duong wrote: > 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. I am not sure whether

Re: [sqlite] SQLITE run slow

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

Re: [sqlite] SQLITE run slow

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

Re: [sqlite] SQLITE run slow

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

Re: [sqlite] SQLITE run slow

You might also consider a trigger to calculate the distance once on insert/update. They you could use an index. On 6/14/2011 4:53 AM, Simon Slavin wrote: > On 14 Jun 2011, at 12:11pm, Martin.Engelschalk wrote: > >> make sure you have an index on category and distance, like >> >> create index

Re: [sqlite] SQLITE run slow

On 14 Jun 2011, at 12:11pm, Martin.Engelschalk wrote: > make sure you have an index on category and distance, like > > create index MyIndex on Location (category, distance) Peter is calculating distance inside his SELECT, but I agree that an index on (category) is an excellent idea. The

Re: [sqlite] SQLITE run slow

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

Re: [sqlite] SQLITE run slow

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

[sqlite] SQLITE run slow

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