On 7 Jan 2010, at 1:18pm, Tim Romano wrote: > I don't claim any SQLite expertise, but indexes on values like > True/False, Male/Female -- i.e. where there are only a couple or a few > different values possible in the column -- are normally to be avoided > because low-cardinality indexes tend to be inefficient. What is the > advice of the SQLite gurus in this respect?
It's complicated because more factors are involved than that. For instance, if you have a database where INSERT is used thousands of times a day but you only SELECT records once a week, all indexes will be inefficient: you want INSERT to work as quickly as possible and it doesn't matter as much if your SELECT works slowly. For a situation like this you often make no indexes besides the primary one. This lets each INSERT operation finish faster. However, it is true that if data in a column is 'chunky' then an index on that column is less useful. Some database systems like MySQL use chunkiness as part of a profiling system to work out what the most efficient indexes will be. Certainly if you need two columns in a WHERE clause, you tend to put the chunkier one second when you're making the index the SELECT will use. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
