Fabian <fabianpi...@gmail.com> wrote: > I have a column with a normal INDEX, and I would like to turn it into an > UNIQUE index, but I'm a bit worried about the performance implications for > inserts. Can someone give some insight into how UNIQUE is implemented in > SQLite, does it create extra tables compared to a normale index, are there > many extra checks?
No, a UNIQUE index and a regular index are implemented the exact same way. It's just that, at INSERT and UPDATE time, after finding a proper place to insert the new value, an additional check is made that the place isn't already occupied. > And a related question: I need to insert 1 million rows, and currently I > create the INDEX afterwards, because that should be faster. I cannot create > the UNIQUE INDEX afterwards, because there's a slight possibility there will > be 1 or 2 duplicates, which will make the creation fail. I now have the > possibility to specify UNIQUE upfront (in the TABLE definition) or manually > filter out any doubles before the insert (which will also take CPU time). > Would there be any advantage doing this manually, or will SQLite do it just > as efficiently? I suspect the way you are going to manually filter duplicates will involve inserting them into a moral equivalent of a UNIQUE index. The performace is probably going to be similar to that of just creating a UNIQUE index up front. But only measurements with your particular data can tell for sure. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
