Re: [sqlite] Stand-Alone INDEX
On Sat, 22 Oct 2011 15:17:23 +0200, Fabian 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
Re: [sqlite] Stand-Alone INDEX
On Oct 22, 2011, at 3:17 PM, Fabian wrote: > So is there some way to have a 'stand-alone index', which doesn't store > everything twice? Not in SQLite, no. Other databases (such as Oracle) sometime offer so-called Index Organized Table (IOT). http://www.orafaq.com/wiki/Index-organized_table ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Stand-Alone INDEX
On 22 Oct 2011, at 2:17pm, Fabian 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? Nope. That's the way an index works in a database. However, your needs for this particular hash index are so simple, you may not actually need a proper database system. Especially if you don't have to insert new rows in this giant list. All you may need is a text file of the hash-values, sorted into order. You could search this file using the binary chop method or some similar fast searching method. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Stand-Alone INDEX
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? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
