Hi Keith,
Thanks for reply. You explain it very well. But, I've try make an unique index,
but sqlite still use their autoindex, which mean their index still better than
mine. Maybe, it's because UNIQUE constrain on email will only return one value
as you say.
Thanks.
On Monday, February 29, 2016 2:28 AM, Keith Medcalf <kmedcalf at
dessus.com> wrote:
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
_______________________________________________
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
