Speed issues - I dont understand...

[sandy771]

I have two issues I dont understand and wondered whether someone could cast some light on them

1. I have a large table (16 Million rows when populated) each containing a sha1 and MD5 hash (and a little other data)

If I create an index on the sha1 column the process takes about 1 minute or so

If I immediately then index on the MD5 column this process takes about 10 minutes

Is this normal - and if so is there anyway of speeding it up



2. The above hashes are loaded into my table approximately 4 million at a time (from text files). Initially the table is unindexed.

If I load a couple of sets of data I get a load speed of about 12,000 rows per second.

I then create an index do some comparisons on the tables (but dont alter them)

I then drop the indexes and load a new table, the new table loads at about 5,000 rows per second.

Any ideas why this might be

Again using sqlite with the data file stored locally

[bork]

Hello

1.
The duration of creating index can depend on data in the column. Creating index for column containing long string can take more time then creating of the index for column with short strings. Index is created by the SQLite database and only SQLite developers know why it takes so much time and how to speed it up.

2.
You haven't specified if the new table has the same structure or not. Maybe a new table has constraint, unique keys, primary keys or index that the first table doesn't have, it can be cause of different loading duration. If a new table has the same structure then you should ask the SQLite developers about this behavior.