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?
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
