"Nemanja Corlija" <[EMAIL PROTECTED]> wrote: > This issue has been raised before, but I haven't found a solution in > list archives. If I missed it, please point me in the right direction. > > I have a database with a single table that has only one text column. > This table has ~10M rows at the moment but it's growing by a 300-400K > rows per week. On average there's 20 characters in each row. File size > (without index) is ~285MB. > > I need to do two things with this database: > - insert new rows while maintaining uniqueness > - do a fast lookup to determine if given string exists in db > > UPDATE and DELETE are never run on this db. > > After some experimentation I found that it's actually faster, at least > for INSERTs, to keep db on disk without index and load it to memory db > on request and create unique index there. After performing all > INSERTs/SELECTs memory db is dumped back to an on-disk db without > index. >
This is probably a locality of reference problem. Try keeping an indexed version of the database on disk, but when you do your batch insert, do it in sorted order. CREATE TEMP TABLE toadd(str TEXT); -- insert 300-400K rows into toadd. INSERT INTO maintable SELECT str FROM toadd ORDER BY str; Selecting a larger page size such as 16K or 32K might also help. Running the queries in sorted order will also possibly help. I think it is at least worth a try. -- D. Richard Hipp <[EMAIL PROTECTED]> ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
