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 it is only a guess) is that, because of the ORDER BY > clause, SQLite is loading all rows into memory, then sorting them > in-memory. >
Yup your guess is right, as Richard has already explained. I am running on linux, and I will need this to scale to at least 200,000 rows (Ram will be higher naturally, but it should scale logarithmically). As usual, I expect the database to do the dirty work and return to me only the 10-20 rows that will be shown to the user. My processing is very inefficient, since I am abstracting out the database almost entirely. The database rows are automatically converted to classes which have the same name as the table, and some of the columns are kept serialized. These are automatically unserialized when this particular variable is requested. (i am doing this via the __get overloading ... All in all, a very complex and inefficient setup at my end). But the overheads on my end are not supposed to be an issue since I will only be workin on max 20 objects at a one particular time, so I just went for transparent handling of database rather than efficiency. I guess, I will have to go for another database. Since the database code is only around 30 lines, out of 80,000 lines of total code, (though the application is 100% database based one) I think that is the easiest method here. Thanks.
