SQLite Forum

find in b+-tree node
Login
When your query is

select * from test where name='tom';

It is likely that the database engine is answering that by doing something similar to:

select * from test where rowid = (select rowid from test where name='tom');

Your index on name is used to answer the inner query.
The primary key index on rowid is used to answer the outer query.

However, your original query did all that for you. For this example, you didn't explicitly need to know anything about rowid, or any tree-node (or even to know that those things were used in the implementation).

In principle, SQLite, might notice that the test table is small enough that using the index won't speed things up, and thus decide to avoid it entirely. All you are supposed to care about is that the query was performed correctly and efficiently.