Of course, if the index on (email, password) was UNIQUE, then it would be *better* than a unique index on email only, for queries constrained on both email and password.
Unless you tell it so, the computer has no idea the index uniquely identifies a single row, and you do this by CREATE UNIQUE INDEX rather than CREATE INDEX. sqlite> create table x (user, email unique, password); sqlite> .eqp on sqlite> select * from x where email='a'; --EQP-- 0,0,0,SEARCH TABLE x USING INDEX sqlite_autoindex_x_1 (email=?) sqlite> create index xa on x (email, password); sqlite> select * from x where email='a'; --EQP-- 0,0,0,SEARCH TABLE x USING INDEX sqlite_autoindex_x_1 (email=?) sqlite> select * from x where email='a' and password='b'; --EQP-- 0,0,0,SEARCH TABLE x USING INDEX sqlite_autoindex_x_1 (email=?) sqlite> create unique index xb on x (email, password); sqlite> select * from x where email='a'; --EQP-- 0,0,0,SEARCH TABLE x USING INDEX sqlite_autoindex_x_1 (email=?) sqlite> select * from x where email='a' and password='b'; --EQP-- 0,0,0,SEARCH TABLE x USING INDEX xb (email=? AND password=?) sqlite> > -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Keith Medcalf > Sent: Sunday, 28 February, 2016 09:52 > To: SQLite mailing list > Subject: Re: [sqlite] ASK SQLite algoritm to chose Index > > > On Sunday, 28 February, 2016 09:17, Christoforus Surjoputro > <cs_sanmar at yahoo.com> said: > > > Thanks for reply? Simon. > > > So you think that it better use autoindex instead of my own index for > > matching email and password from user input and database? How about > sqlite > > queryplanner docs tell that said "To get the maximum performance out of > a > > query with multiple AND-connectedterms in the WHERE clause, you really > > want a multi-column index withcolumns for each of the AND terms."? The > > index that I made contain email and password, but sqlite autoindex only > > contain email that of course my index is better than autoindex as they > > said from docs. How about that, Simon? > > The "autoindex" is generated from the constraint in the table definition. > It is a unique constraint on the email address. The index that you > created is not unique -- it may have the same email address billions of > times. It may also have billions of passwords for each email address. > Unless you have run ANALYZE then the query optimizer has no idea the shape > of the data and assumes that their are billions and billions of email > addresses, and that for each one there are billions and billions of > passwords, which will point to many trillions of records. > > However, the "autoindex" generated by the UNIQUE constraint on email can > only ever return 1 record. 1 record is less than a billion trillion, so > that index is favoured since the job of the query planner is to select the > desired record(s) with the most efficiency (and retrieving one record is > more efficient that retrieving trillions of records, by definition.) > > > On Sunday, February 28, 2016 1:15 PM, Simon Slavin > > <slavins at bigfraud.org> wrote: > > > > > > > > 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. > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
