On Sat, 22 Oct 2011 15:17:23 +0200, Fabian <fabianpi...@gmail.com> wrote:
>I have a very simple table: it just contains one single (text) column with >an index. This table contains million of hash-values, and because of the >index, SQLite is storing all the data twice. Behind the scenes it creates a >second table, containing all the same values in sorted order, causing the >database size to double. > >Because all the data I need is also in this second index-table, is there >some kind of way to get rid of my original table, and still be able to >insert new items? My initial thought was to change the schema of my table so >that it only has a TEXT PRIMARY KEY and no other columns, but SQLite >internally still creates an INTEGER rowid, so the end-result made no >difference. > >So is there some way to have a 'stand-alone index', which doesn't store >everything twice? If the hash is small enough to fit a 64bit signed integer, you could store it as INTEGER. Make sure you define the table as CREATE TABLE Hashes (hash INTEGER PRIMARY KEY NOT NULL); In this case the hash is an alias for ROWID and the table BTree is its own index, no extra index BTree is created. -- ( Kees Nuyt ) c[_] _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
