2

I am doing performance analysis over mondial database using sqlite3. One test case where I have to compare performance with and without using index (it should not use sqlite_autoindex as well).

I found this link :How can I force a query to not use a index on a given table? very useful but most of the answers refer to SQL SERVER. But I need it for SQLITE3. (I have tried PRAGMA options but no result).

Kingsman
  • 174
  • 1
  • 10

1 Answers1

3

It's buried in the syntax diagrams for SELECT, but there is a way - Using NOT INDEXED with a table name in the FROM clause:

sqlite> CREATE TABLE foo(bar);
sqlite> CREATE INDEX foo_idx ON foo(bar);
sqlite> EXPLAIN QUERY PLAN SELECT * FROM foo WHERE bar = ?;
QUERY PLAN
`--SEARCH TABLE foo USING COVERING INDEX foo_idx (bar=?)
sqlite> EXPLAIN QUERY PLAN SELECT * FROM foo NOT INDEXED WHERE bar = ?;
QUERY PLAN
`--SCAN TABLE foo

As you can see, the first query uses the index, and the second one doesn't.

How to read EXPLAIN QUERY PLAN output.

Shawn
  • 47,241
  • 3
  • 26
  • 60
  • Thank you so much! that's a quick and helpful answer. Phew!. Even after specifying NOT INDEXED SQLite optimizer still, use " autoindex", but that's not my focus as of now. I saw the documentation but found that this behavior of optimizer cannot be altered. – Kingsman Dec 03 '18 at 00:58