Is it possible to create a temp index in sqlite?

Question

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.

If I may suggest (for the future, as the question is a bit old already), adding and index, deleting the index and then running VACUUM, would not alter the database in any way. There's also very little chance of data corruption, which may only occur should your disk have bad sectors. — Michał Leon, Dec 22 '14 at 17:17

score 4 · Accepted Answer · answered Apr 04 '13 at 10:45

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.

Is it possible to create a temp index in sqlite?

1 Answers1