Stephen Woodbridge wrote: > > Well if the index requires fewer page reads then it should be > proportionally faster. For example if you can only get 5 rows on a page > but 25 index entries, you have 1/5 the number of pages to read. >
Yes, that is true, but this effect is offset by the fact that these index pages compete with the table pages for the finite space in the page cache. The speed of counting is usually only an issue with large databases where the table doesn't fit in the cache. When loading index pages to speed the count() you are also ejecting table pages that might be used for the next data query, and hence slowing that data query down because it now has to reload the data pages. Adding such an index also slows down all insert, update, and delete operations because they must modify the index as well as the table. Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
