On 18 Jan 2011, at 2:08am, Sam Carleton wrote: > I am adding some indexes to an existing database to improve performance. I > am 99.9% sure they are unique, but... it was a while ago that I was in that > code. Are there any performance reasons to make them unique or make them > not unique? From the stand point of risk, my inclination is to make them > not unique.
Making an index UNIQUE is a tool you can use to make sure your data is sane. If the real-life situation ensures that no duplications can ever exist, then it can be nice to have a new record rejected at INSERT time (which is what will happen) rather than sneaking into the table to mess things up later. If your data really is made up of unique records then it won't slow things down: no extra sorting will be required. > While I am on the topic of indexes. One of the indexes is for a systems > lookup table, just has category, name, and value. The size is fixed in > development. Currently there are about 25 rows. Is there any advantage to > an index or because of the small size, will that only hurt performance? You are weighing stuff up but the changes are very small. I don't think it will do you much harm, though it may not do you much good either. But keeping your design principles even if they tables are small seems sensible. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
