Hi, i have the following problem: in my database is one specific table that gets quite a lot of inserts..the table consist only of a primary key and three integer values. I need to index, besides the primary table, 2 more columns on the table. there is no option to put a 'unique' index.
if i put an index on the table while creating, inserting will go very slow if table grows just some larger (at 50.000 records => about 10ms per insert). that is unacceptable because it is unsufficient to store the incoming data flow. so, as i understood from the documentation is, that it is faster to put the index on the table afterwards, and so i did. with no index, inserting goes very rapidly (about 0.9 - 1.5 ms/insert). now, i tried to put the index on the table afterwards. i tried this when the table was real big (600Mb, about 40.000.000 rows).. After six hours, sqlite had read and wrote 150Gb (!) to disk (that is: reading 150Gb, writing 150Gb according to windows taskman) the job still wasn't done.. i had to kill the job. to circumvent this, i made a mechanism that splits up the table into multiple tables. it will slow down fetching the results a bit, but i need them indexed. querying multiple tables should not be the problem, since coherent data is always in one table. now, if i put an index on 200.000 records, it takes about 15 seconds to complete (PIII-667). On 1.000.000 records this will we about 3-4 minutes. On 1.500.000 records it climbs to 15 minutes, 2.000.000 records about half an hour and so exponentially climbing it seems. i cannot think of another table structure, nor can i put less data in it.. i picked sqlite because my tables can grow to 2Tb, i expect the database easily to grow much larger than 4Gb. My questions are: *is this a bug? *why does indexing take that long? *will it be fixed in 3.0? *should i consider using embedded mysql instead (not tested that if it got better performance though)? *would it help if i create a seperate database for this particulair table? (there is about 30% space used by other tables) regards, rene --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
