On 28 Feb 2016, at 5:57am, Scott Robison <scott at casaderobison.com> wrote:
> So the link appears to be: > http://stackoverflow.com/questions/35625812/sqlite-use-autoindex-instead-my-own-index And that allows us to provide an explanation. Here's the setup: sqlite> CREATE TABLE user(id INTEGER PRIMARY KEY,email TEXT NOT NULL UNIQUE,password TEXT NOT NULL,name TEXT NOT NULL); sqlite> CREATE INDEX usr ON user(email,password); sqlite> EXPLAIN QUERY PLAN SELECT id, name FROM user WHERE email = "a at a.com" AND password = 'password'; 0|0|0|SEARCH TABLE user USING INDEX sqlite_autoindex_user_1 (email=?) As the OP reports, SQLite chooses its own automatic index to search the table. OP expects/wants SQLite instead to use the index he has invented and asks for a way to force this. But actually OP chose a poor index to be used for the search and SQLite has spotted this. The automatic index SQLite created was based on "email TEXT NO NULL UNIQUE" so it enforces the UNIQUE property. Which means it can go straight to the value for "email" which is being searched for. It doesn't need a value for "password" at all: it either finds the right "email" or it doesn't. As a check, try it without telling SQLite that "email' is UNIQUE: sqlite> CREATE TABLE user(id INTEGER PRIMARY KEY,email TEXT NOT NULL,password TEXT NOT NULL,name TEXT NOT NULL); sqlite> CREATE INDEX usr ON user(email,password); sqlite> EXPLAIN QUERY PLAN SELECT id, name FROM user WHERE email = "a at a.com" AND password = 'password'; 0|0|0|SEARCH TABLE user USING INDEX usr (email=? AND password=?) Now we get the behaviour the OP expected. Simon.
