Is it a way to know whether Index was used for a particular sql query

Question

Sometimes one when creating SQL queries one assumes that one of the Index should be used by the engine when getting the data. But not always. If some of the necessities are absent the engine will probably process the rows one by one. Is it a way (for sqlite) to know for sure that the index was used? I mean in cases more complex than SELECT rowid from Table order by rowid The problem also arises since if we test with a simple base, the time measuring won't help, the difference between them will be minimal.

Answer 1

There is a profiler for SQLite that may help.

Answer 2

Most DBMSes offer the ability to explain how they executed their queries. For SQLite, you precede the SQL statement with the word EXPLAIN.

So, for an easy case:

EXPLAIN SELECT * FROM TableName

will tell you whether an index was used.

Please be aware that the query plan may change as the quantity and nature of the data in your database changes. The engine may choose to use another index depending on table size and the number and distribution of distinct values in the index column(s). Therefore, the information you get from EXPAIN with your test database may not reflect the real query plan on a database full of "real" data.