SQLite Forum

index question
Remember that you can always use [EXPLAIN QUERY PLAN](https://www.sqlite.org/eqp.html) to see how it's going to run a query.

Let's take a look:

SQLite version 3.32.1 2020-05-25 16:19:56
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table dance (id int primary key, "select" int not null, name text unique);
sqlite> create unique index if not exists index_dn on dance (name, "select");
sqlite> explain query plan select id, name from dance where name = 'waltz' and "select" > 0;
`--SEARCH TABLE dance USING INDEX sqlite_autoindex_dance_2 (name=?)

So a couple of things:

Using "int primary key" instead of "integer primary key" is creating an entire extra index on id. You can avoid that with "integer primary key" making it an alias for the rowid.

You've already declared name as unique... so there's already an index on it to satisfy the unique constraint. That the "sqlite\_autoindex\_dance\_2" index that it's using in the query. Since "name" is unique it already has an index on it, and an index on (name, "select") isn't going to make it more unique or provide any bonus, since name is already unique on its own. When SQLite looks at which index to use it sees that it can use the more compact 1 column index rather than the less compact 2 column index.

> Will the value of "select" be stored in the index in addition to the table, or only in the index?

When you declare an index of (a, b, c) then what's included in the index is a, b, c, and the rowid if it's a rowid table, or any remaining columns needed for the whole primary key in a without-rowid table.

That makes me think of another question which I'll probably post as a separate reply though.