SEARCH TABLE vs SCAN TABLE

Question

sqlite> .schema actor
CREATE TABLE actor (
  id INTEGER PRIMARY KEY, name TEXT, gender TEXT
  );
sqlite> explain query plan
   ...> select * from actor where id = '305453';
0|0|0|SEARCH TABLE actor USING INTEGER PRIMARY KEY (rowid=?)
sqlite> explain query plan
   ...> select * from actor where name = 'Connery, Sean';
0|0|0|SCAN TABLE actor
sqlite>

SEARCH TABLE actor USING INTEGER PRIMARY KEY (rowid=?) is taking significantly less time compared to SCAN TABLE actor

It is obvious that SCAN TABLE actor is an exhaustive scan of actor table due to chance of duplication, but,

1) Are SCAN TABLE & SEARCH TABLE scanning B-TREE or sequence of records?

2) If it is B-Tree, then how SEARCH TABLE query faster?

The primary key is an index, it doesn't need to read every row. — Barmar, Dec 21 '17 at 21:12
@Barmar Firstly, are these queries scanning sequence of records or B-tree, in both cases.Because B-Tree with primary key is pretty faster. — overexchange, Dec 21 '17 at 21:15

Barmar · Accepted Answer · 2017-12-21T22:37:04.040

SEARCH TABLE means it uses an index, which is a fast way to find rows with a specific column value. The primary key is automatically indexed, for other columns you need to add an index explicitly with the CREATE INDEX command. Searching a B-tree like this is O(log n).

SCAN TABLE means it has to perform an exhaustive search, reading all the rows in the table. Scanning the entire table is O(n). This is done when you're matching a non-indexed column, like name in your example.

For more information about SQLite indexes see Squeezing Performance from SQLite: Indexes? Indexes!. And detailed documentation of the design of SQLite indexes is in Query Planning.

Comments are not for extended discussion; this conversation has been [moved to chat](http://chat.stackoverflow.com/rooms/161784/discussion-on-answer-by-barmar-search-table-vs-scan-table). — Bhargav Rao, Dec 22 '17 at 05:46

SEARCH TABLE vs SCAN TABLE

1 Answers1