On Thu, Jan 07, 2010 at 08:18:56AM -0500, Tim Romano scratched on the wall: > 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?
"Yes, but..." In general, yes. If a condition and index can't cut a table down to about 15% or less, indexes are usually not worth it... a full table scan will be faster. This is because an index will typically provide a ROWID, which then needs to be looked up in the main table. This makes each index look-up at least twice as expensive, and typically much more due to increased I/O, scattered I/O, and cache thrash. However, in the case of multi-column indexes, if SQLite can extract the column data it needs from the index itself the second table look-up will be skipped. This means there are times when multi-column indexes and boost performance, even if the later columns aren't used as part of the index look-up. There is a insert/update/delete cost associated with that, however. Many server-based database systems keep statistics on their indexes. For example, the number of values vs unique values to measure the selectivity of the index. This meta-data is typically built during idle times. SQLite doesn't have "idle times", but you can manually build basic statistics using ANALYZE. If you built the library with the extended statistics, ANALYZE will also build a histogram to compute the selectivity of ranges. I have no idea how advanced the query optimizer is, or how much this data is used, but it is one more thing to try. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
