What is an automatic covering index?

问题:

When using EXPLAIN QUERY PLAN in SQLite 3 it sometimes gives me output such as

SEARCH TABLE staff AS s USING AUTOMATIC COVERING INDEX (is_freelancer=? AND sap=?) (~6 rows)

Where does the index come from and what does it do? The table has no manually created indices on it.

回答1:

"Automatic" means that SQLite creates a temporary index that is used only for this query, and deleted afterwards.

This happens when the cost of creating the index is estimated to be smaller than the cost of looking up records in the table without the index.

(A covering index is an index that contains all the columns to be read, which means that the record corresponding to the index entry does not need to be looked up in the table.)