-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 03/26/2011 03:12 AM, Black, Michael (IS) wrote: > When you say "All an index does" don't forget that an index is also usually > smaller than the data, thereby increase cache performance and reducing disk > seeks.
That is muddied in the case (probably most common) where the index does not include all the columns needed for the query. Consequently the rowid has to be found in the index and then the main data has disk seeks to retrieve the remaining columns from the row. Seeking in the index will be random access whereas doing a table scan will predominantly be sequential access. These factors are why it is a not a trivial determination as to which is better and why analyze helps. It is also why an index can be slower more commonly than expected. Roger -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk2OWrAACgkQmOOfHg372QSNFQCgqXlZu7V09GSA0JWOq9in+JB7 u8EAn19m1Sn8RhV/grSWcIEuPJCAEU0v =LsnS -----END PGP SIGNATURE----- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
