SQLite use autoindex instead my own index

Question

I've problem with SQLite autoindex in UNIQUE table. I've create table like below.

c.execute('''CREATE TABLE user(
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    name TEXT NOT NULL,
    );'''
)
c.execute('CREATE INDEX USR on user(email, password);')

But when I check using explain query plan, SQLite use the autoindex provide by itself. How to avoid this to use my own index instead of it's auto index? How I try:

c.execute('EXPLAIN QUERY PLAN SELECT id, name FROM social WHERE email = "a@a.com" AND password = 'password'')

And the resut is:

(0, 0, 0, 'SEARCH TABLE social USING INDEX sqlite_autoindex_user_1(email=?))

Answer 1

In your case, I think "sqlite_autoindex_user_1" is the index SQLite uses to implement the declared constraint on "email". Despite the name, it's an internal index, not an autoindex.

Do not confuse automatic indexes with the internal indexes (having names like "sqlite_autoindex_table_N") that are sometimes created to implement a PRIMARY KEY constraint or UNIQUE constraint. The automatic indexes described here exist only for the duration of a single query, are never persisted to disk, and are only visible to a single database connection. Internal indexes are part of the implementation of PRIMARY KEY and UNIQUE constraints, are long-lasting and persisted to disk, and are visible to all database connections. The term "autoindex" appears in the names of internal indexes for legacy reasons and does not indicate that internal indexes and automatic indexes are related.

Source

The query optimizer decided that using the index on "email" would be fastest. It's probably right.

---

To see how SQLite might use your covering index "medp", build a test table like this.

create table social_test (
  id integer primary key, 
  name text not null,    -- no UNIQUE constraint for testing
  tampil integer not null
);

create index medp on social (name, tampil);

Insert a million rows if you want to.

analyze social;
explain query plan select * from social where name = 'facebook' and tampil = 6;
0|0|0|SEARCH TABLE social USING COVERING INDEX medp (name=? AND tampil=?)

Answer 2

Due to the UNIQUE constraint, the database knows that the lookup on the email column can return at most one row. This means that only a single password value needs to be checked, and that can be done just as easily by looking at the already-known table row.

The two-column index needs more space than the single-column one, so it would be slower to load it from disk.

To force the database to use your index, you could use the INDEXED BY clause, but this would not improve the performance.

Answer 3

A see a few problems here.

1. The first SQL statement (CREATE TABLE ...) is malformed due to an extra comma between the last column and the closing parenthesis.

2. The third SQL statement (EXPLAIN QUERY PLAN SELECT ...) uses double quotes around the specified email. SQLite will allow this for backward compatibility reasons, but it is not recommended.

3. The quoting of the string representing the third SQL statement is not quoted properly in any language I'm familiar with.

4. Finally, the table name social is not defined in the limited schema you gave, so really we have no way of knowing what real indexes are available.

If we assume you meant "user" when you typed "social", as CL. said, the "autoindex" in use guarantees the email is unique so it doesn't need anything else. It might have used your explicit index as a covering index if you didn't need the name column as well, but since your explicit index does not include the name column (as required by the select statement), it deems the autoindex best. Additionally, the autoindex is almost certainly better for this case because the smaller index (only email vs email & password) means that there will likely be fewer reads of btree pages while trying to find the requested email address. In other words, an index on email address only will be a smaller index than an index on email and password.

Answer 4

From the documentation:

Choosing between multiple indices

(...)

When faced with a choice of two or more indices, SQLite tries to estimate the total amount of work needed to perform the query using each option. It then selects the option that gives the least estimated work.

To help the optimizer get a more accurate estimate of the work involved in using various indices, the user may optionally run the ANALYZE command. The ANALYZE command scans all indices of database where there might be a choice between two or more indices and gathers statistics on the selectiveness of those indices. The statistics gathered by this scan are stored in special database tables names shows names all begin with "sqlite_stat". The content of these tables is not updated as the database changes so after making significant changes it might be prudent to rerun ANALYZE. The results of an ANALYZE command are only available to database connections that are opened after the ANALYZE command completes.

(...)

So, you can run the analyze command to rescan the indices, but this will not guarantee that the optimizer will prefer your index.

To force a given index to be used you can use the INDEXED BY phrase. From the documentation:

The INDEXED BY phrase forces the SQLite query planner to use a particular named index on a DELETE, SELECT, or UPDATE statement. The INDEXED BY phrase is an SQLite extension and is not portable to other SQL database engines.