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
