CREATE INDEX has two problems: 1) poor default location of temporary storage. 2) gets wedged on very large indexes.
I'm using the sqlite that came with Xubuntu 14.04, I think it's version 3.8.2. I created a table, and used .import to populate it with records, about 1.4 billion of them. The resulting database is 232 GB. All seemed well. I then went to CREATE INDEX on two fields, one CHAR(64) and the other an INT. This initially died quickly, but I determined that it was because it was attempting to use my smallest partition for workspace. It was in my root partition, but not in /tmp which is separate. I submit that /tmp would be the natural choice, as it is what the sort command uses by default. That's problem 1. Fortunately, it honors the $TMPDIR environment variable, but gets wedged after a bit. I have an idea why. While it was still actively adding space, there were no filenames showing, so I infer that it's using "anonymous" files, perhaps by calling tmpfile(). This could be bad news, as anonymous files have to be held open, but there are limits on how many open files any process is allowed. Unless your merge process starts with at least 1.4 million keys in each buffer, this is going to fail on my data. I suggest using tempnam() and closing the files when you're not actively using them. -- #define QUESTION ((bb) || (!bb)) /* Shakespeare */ _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
