On 2 Aug 2010, at 5:31pm, Paul Sanderson wrote: > I have a table with just a few columns, one is a SHA1 hash and the > second an MD5, there are about 17 Million rows in the table > > if I create an index on the SHA1 column using "create index if not > exists sha1index on hashtable(sha1)" the process takes about 3 > minutes, if I follow this immediately by creating a second index on > the md5 column "create index if not exists md5index on hashtable(md5)" > this process takes over 30 minutes.
Did you try doing those in the opposite order ? In other words, is that the index for SHA1 is always faster, or is it that the first index you create is always faster ? SHA1 should give you 20 bytes. MD5 should give you 16 bytes. I assume that both columns contain TEXT and only TEXT values. Since both indexes are on the same table, they have the same number of values. But do they have the same number of different values ? For instance, your SHA1 hashes might give you 16 million different values, but MD5 might give you only 14 million different values. The index on the column with fewer different values may be faster to create, depending on how SQLite works internally. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
