Without going into details of what your structure is, I suggest you look into "without rowid" option when creating the table. It reduces the overhead drastically.
---- On Thu, 29 Nov 2018 18:59:26 +0530 Dominique Devienne <[email protected]> wrote ---- > On Wed, Nov 28, 2018 at 6:03 PM AJ M <[email protected]> wrote: > > > [...] The data comes out to 10 billion rows of an 8 byte signed integer > > (~200-300 gb pre-index), and while insertion takes ~6 hours, indexing takes > > 8 hours by > > itself. [...] query speed is fine as-is. [...] > > > > Hi AJ. Your message is quite intriguing, because you make it sound like > your row > is composed of a single 8-byte signed integer. Even multiplied by 1e10 > rows, that's only > 80GB ideally, so 200-300GB pre-indexing means a large 3x overhead in the > DB, which doesn't sound right. > > Also, a row composed of a single integer column is not that interesting at > first sight, and a SQL > DB does not seem appropriate for such a simple data "structure". What kind > of query would > you be running on that one signed integer? Surely you have other columns in > your DB? > What's the natural or primary key of those rows? > > So far you got answers on your specific question, but if we backed up a > little and got more context > on what you are trying to achieve at a higher level, your exact table(s) > structures and indexes, > and the kind of queries you are running? I'm sure you'd get a different > perspective on your > problem, which may even not be related to SQLite at all I kinda suspect. My > $0.02. --DD > _______________________________________________ > sqlite-users mailing list > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
