>> Actually query one appears slightly faster, >> Searching the PK index is faster as that is always a COVERING index. > >I was under the impression that the opposite is true, but I wasn't sure >about that.
The primary key is only a covering index if you are only accessing fields comprising the primary key, or if the primary key is the rowid (in which case the primary key is the rowid of the entry in the table -- in which case a scan of the primary key index is the same as a scan of the table) or if the table was declared as without_rowid. >> From the secunsary indexes only a part oh the key is used. >> Note there is not much use on adding PK as second column in the >> additional indexes. It is there anyway a a pointer to the row. >You're right, that index doesn't make much sense; in my real application >it looks different, what I was showing here was just an example (one that >was not very well thought of, obviously). No. The rowid is contained in the index anyway and that is the primary key if and only if the table is declared such that the rowid is the primary key. Alternate keys (such as declare by any other method) are *NOT* included in any other index. If your primary key is declared on columns, then the rowid is what is stored in the index in addition to the named columns, in order to locate the table row. Unless of course your table is declared without rowid in which case the primary key is the declared key, not the rowid, and the declared primary key is stored in every index to allow you to located the corresponding table row. --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why.
