SQLite Forum

index question
Login

index question

(1) By doug (doug9forester) on 2020-06-05 06:27:42 [link] [source]

Given:

create table dance (id int primary key, select int not null, name text unique)

The table is a fixed sized table with the set of defined dances. I can select a set of dances by setting select=1; un-select=0;

I'm pretty certain I should define an index for the name. But I don't know whether to include "select" in the index or not. "select" is very volatile. I turn them all on or off, or select individual ones on or off from the user interaction.

If I define an index as

CREATE UNIQUE INDEX IF NOT EXISTS index_dn ON dance (name,select)
and use a select as
SELECT id,name FROM dance WHERE name='waltz' and select>0
is the index going to be used? Will the value of "select" be stored in the index in addition to the table, or only in the index?

What does it mean to use a WHERE clause in the CREATE INDEX as

CREATE UNIQUE INDEX IF NOT EXISTS index_dn ON dance (name,select)
  WHERE select>0
Does that mean that there is no index entry if select==0?

(2) By Keith Medcalf (kmedcalf) on 2020-06-05 07:22:54 in reply to 1 [source]

create table dance (id int primary key, select int not null, name text unique)

Do you mean INTEGER PRIMARY KEY? If so, you have misspelled INTEGER.

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

Yes, the index will be used. Yes the value of select is stored in both the table and the index.

What does it mean to use a WHERE clause in the CREATE INDEX as

CREATE UNIQUE INDEX IF NOT EXISTS index_dn ON dance (name,select) WHERE select>0

Does that mean that there is no index entry if select==0?

Yes, only records with select > 0 appear in the index. But why are you adding select to the index? If the value is not 0 does it matter what the value is?

If select can only contain the values 0 and 1 (true and false), then why are you not requiring that?

create table dance
(
   id integer primary key, 
   select int not null check (select in (True, False)), 
   name text unique
);
create unique index dance_true on dance(name) where select is true;
create unique index dance_false on dance(name) where select is false;

And you selects would be something like:

select id, name from dance where name == 'waltz' and select is true;

(3) By Larry Brasfield (LarryBrasfield) on 2020-06-05 10:45:40 in reply to 1 [link] [source]

This point is orthogonal to Keith's response.

You will likely learn, if you keep programming, not to use keywords as identifiers. In SQL, an identifier can be quoted, with surrounding doublequote characters, to make it act only as an identifier rather than being parsed as a keyword if it happens to be one. If you insist on using keywords as identifiers, you should be quoting them. The fact that SQLite's parser happens to tolerate unquoted keywords in some places where an identifier can be used is one you should not rely upon unless you plan to write SQL only for SQLite.

Potential parser errors aside, you will be doing a favor for anybody who has to read your SQL if you keep keywords and identifiers distinct from each other.

(4) By David Raymond (dvdraymond) on 2020-06-05 12:19:44 in reply to 1 [link] [source]

Remember that you can always use EXPLAIN QUERY PLAN 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;
QUERY PLAN
`--SEARCH TABLE dance USING INDEX sqlite_autoindex_dance_2 (name=?)
sqlite>

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.

(5) By luuk on 2020-06-05 12:37:15 in reply to 4 [link] [source]

"You've already declared name as unique... so there's already an index on it to satisfy the unique constraint"

Is this true? The index is not shown when doing this:

.schema dance

(6) By luuk on 2020-06-05 12:40:20 in reply to 5 [link] [source]

found the answer:

https://stackoverflow.com/questions/19357490/does-an-unique-constraint-remove-the-need-for-an-explicit-index-in-sqlite

which refers to: https://sqlite.org/lang_createtable.html#uniqueconst

which explains...

(7) By David Raymond (dvdraymond) on 2020-06-05 12:47:25 in reply to 6 [link] [source]

The index_list pragma will also show them on there.

For those reading along, here's what's currently in the CREATE TABLE page:

In most cases, UNIQUE and PRIMARY KEY constraints are implemented by creating a unique index in the database. (The exceptions are INTEGER PRIMARY KEY and PRIMARY KEYs on WITHOUT ROWID tables.) Hence, the following schemas are logically equivalent:

1. CREATE TABLE t1(a, b UNIQUE);

2. CREATE TABLE t1(a, b PRIMARY KEY);

3. CREATE TABLE t1(a, b);
CREATE UNIQUE INDEX t1b ON t1(b);