How to create programmatically an index in a SQLite database with Xcode ?

Question

I use in my iPhone application a very big database. An index is used on one of its columns in oder to make the searchs in this database faster. The problem is that this index increases the size of the database : my application is now bigger than 20 Mo requiring a wifi download (I would like to remain below 20 Mo in order to allow 3G download).

Consequently, in order to reduce the size of this database, I would like to create the index on the iPhone of the user when the application is launched for the first time.

The SQLite query I want to execute is the following one :

"CREATE INDEX INDEX_ON_MYCOLUMN ON MY_TABLE (MYCOLUMN)"

Do you know how to include this query in my XCode code ? sqlite3_something ?

By the way, how to compact programmatically the database after this operation ?

Thanks a lot for your help !

Answer 1

sqlite3_exec() will probably do what you want. You can run it on every launch instead of just the first launch (the additional times will just fail harmlessly). Of course, your launch times might become terrible.

See also VACUUM ANALYZE.