mrobi...@cs.fiu.edu wrote: > this is how I create the db, table and index. > > > rc = sqlite3_exec(db, "CREATE TABLE probes (probe STRING unique);", > > rc = sqlite3_exec(db, "CREATE INDEX probe on probes (probe); > ", NULL, > In addition to what the others have said you should also see a substantial speedup, and reduction in your database size, if you eliminate the redundant probe index. The unique constraint on the probe column in your table causes sqlite to automatically create an index nearly identical to the one you are adding manually. It uses that index to efficiently enforce the uniqueness constraint.
As it is, your database contains one table and two indexes, each of which contains a copy of every probe string your table (i.e. three times several million strings). If you know your data doesn't contain any duplicate strings, the fastest approach would be to remove the unique constraint from the table. Then add all your data to the table (inside one or more transactions), and finally create a unique index on the strings after all the inserts. CREATE TABLE probes (probe STRING); BEGIN; INSERT ... INSERT ... COMMIT; CREATE UNIQUE INDEX probe on probes (probe); Of course this won't work if you are relying on the unique constraint to eliminate duplicate strings in your data. HTH Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
