A program I'm working on can import data from another program (not ours) that uses sqlite files. I need indices that don't already exist in the sqlite files, but I don't want to have to modify someone's data just to let them import it. The index is used by many queries. Thus, I would like to create a temp index that lives only as long as the connection
I've seen the following obvious syntax referenced (in mailing list messages from 2003...):
$ sqlite3 thirdparty.sqlite
SQLite version 3.7.4
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TEMP INDEX foo ON SomeTable(SomeCol);
Error: near "INDEX": syntax error
Is it possible to do something like this? Right now, we're just making a copy of file before the import, but these files can be quite large so that's rather impractical.
TEMPORARY indices were temporarily added but removed soon, because consistency could not be guaranteed. While this should not be a problem if the index only lasts for a transaction (which would cause an exclusive lock and thus prevent modification), this is not the way they were implemented. So as of this writing temporary indices are not available to sqlite. For further details see http://permalink.gmane.org/gmane.comp.db.sqlite.general/4827.