Re: [sqlite] Re: sqite2.8 performance snag when filesize grows more than 70MB on a 256MB ram

2005-08-21 Thread Khamis Abuelkomboz
D. Richard Hipp wrote: I am running on linux, and I will need this to scale to at least 200,000 rows If you upgrade to the vary latest code in CVS (version 3.2.2+) and you create an index like this: CREATE INDEX idx ON table(parent_name, name); Then the query above should be very

Re: [sqlite] Re: sqite2.8 performance snag when filesize grows more than 70MB on a 256MB ram

2005-08-20 Thread D. Richard Hipp
On Sun, 2005-08-21 at 04:24 +0530, Ligesh wrote: > On Sun, Aug 21, 2005 at 12:30:25AM +0200, Ulrik Petersen wrote: > > > > > >select * from table where parent_name = 'parent' order by name limit 10 > > > > I am running on linux, and I will need this to scale to at least > 200,000 rows If you u

[sqlite] Re: sqite2.8 performance snag when filesize grows more than 70MB on a 256MB ram

2005-08-20 Thread Ligesh
On Sat, Aug 20, 2005 at 06:36:19PM -0400, D. Richard Hipp wrote: > On Sun, 2005-08-21 at 03:21 +0530, Ligesh wrote: > > I am running a very simple sql query with only one instance. The > > query is something like: > > > > select * from table where parent_name = 'parent' order by name limit > > 1

[sqlite] Re: sqite2.8 performance snag when filesize grows more than 70MB on a 256MB ram

2005-08-20 Thread Ligesh
On Sat, Aug 20, 2005 at 06:36:19PM -0400, D. Richard Hipp wrote: > On Sun, 2005-08-21 at 03:21 +0530, Ligesh wrote: > > I am running a very simple sql query with only one instance. The > > query is something like: > > > > > > In order to implement the ORDER BY clause, SQLite reads the > entire

[sqlite] Re: sqite2.8 performance snag when filesize grows more than 70MB on a 256MB ram

2005-08-20 Thread Ligesh
On Sun, Aug 21, 2005 at 12:30:25AM +0200, Ulrik Petersen wrote: > Ligesh, > > Ligesh wrote: > > >I am running a very simple sql query with only one instance. The query is > >something like: > > > >select * from table where parent_name = 'parent' order by name limit 10 > > > > > > My guess (and

Re: [sqlite] Re: sqite2.8 performance snag when filesize grows more than 70MB on a 256MB ram

2005-08-20 Thread D. Richard Hipp
On Sun, 2005-08-21 at 03:21 +0530, Ligesh wrote: > I am running a very simple sql query with only one instance. The > query is something like: > > select * from table where parent_name = 'parent' order by name limit > 10 > > The query takes around .3 seconds when the total number of matches >

Re: [sqlite] Re: sqite2.8 performance snag when filesize grows more than 70MB on a 256MB ram

2005-08-20 Thread Ulrik Petersen
Ligesh, Ligesh wrote: I am running a very simple sql query with only one instance. The query is something like: select * from table where parent_name = 'parent' order by name limit 10 My guess (and it is only a guess) is that, because of the ORDER BY clause, SQLite is loading all rows i

[sqlite] Re: sqite2.8 performance snag when filesize grows more than 70MB on a 256MB ram

2005-08-20 Thread Ligesh
I am running a very simple sql query with only one instance. The query is something like: select * from table where parent_name = 'parent' order by name limit 10 The query takes around .3 seconds when the total number of matches are around 7,000, but just jumps to 11 seconds, at 13,000 matc