> But I'm wondering if SQLite can deal more efficiently with a INTEGER index > (64bits) VS an 8-byte TEXT column (also 64bits). I know the INTEGERs > require less disk-space because SQLite can store smaller values in fewer > bytes, but are there any other differences that make them more preferable > as an INDEX? If there is no difference in performance, I could just take > the first 8 characters of the TEXT column as a hash-value, instead of > calculating a CRC64 each time.
My first thought was "sure, text will be compared byte-by-byte, 64-bit integers on a 64-bit CPU will be compared all in one operation". But then SQLite should read integer from database first and I'm not sure if it does some optimization to read the whole integer in one CPU operation, maybe it does that also byte-by-byte. So probably you won't notice much difference in performance of either way of indexing. Pavel On Wed, Nov 9, 2011 at 10:23 AM, Fabian <fabianpi...@gmail.com> wrote: > I'm having an issue where inserts on an un-cached database are very slow. > The reason probably is that a large part of the existing index needs to be > read from disk, to be able to insert new rows to the index. The length of > the values in the indexed column are around 60 bytes, so I'm thinking about > adding an extra column, containing a shorter hash (64bits) of the actual > value, and move the index to that column instead. This way the total size > of the index (and the database) will be much smaller, hopefully resulting > in faster inserts. > > But I'm wondering if SQLite can deal more efficiently with a INTEGER index > (64bits) VS an 8-byte TEXT column (also 64bits). I know the INTEGERs > require less disk-space because SQLite can store smaller values in fewer > bytes, but are there any other differences that make them more preferable > as an INDEX? If there is no difference in performance, I could just take > the first 8 characters of the TEXT column as a hash-value, instead of > calculating a CRC64 each time. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
