[sqlite] Why is a separate journal file needed ?
On 2/28/2016 9:19 PM, Rowan Worth wrote: > On 27 February 2016 at 00:02, Igor Tandetnik wrote: > >> On 2/26/2016 4:01 AM, Rowan Worth wrote: >> >>> In principle this is correct, but actually the database *file* is not >>> immediately modified in rollback mode. Instead when a page is modified the >>> original contents are saved to the rollback journal, and the page is >>> updated *in memory*. >>> >> >> ... until such time as the cache needs to be spilled - then it's updated >> in the database file. In fact, I'm pretty sure the rollback journal is not >> created as long as all the changes are entirely in RAM. >> > > The rollback journal is always created before the main database file is > modified. This must happen for durability's sake - if the changes were only > held in RAM then sqlite would have no way to recover a partially modified > database caused by a power failure mid-transaction. By definition, as long as "all the changes are entirely in RAM", the main database file has not yet been modified. After all, if it has been modified, then some changes are already outside of RAM. In light of this rather obvious observation, I'm not quite sure what point you were trying to make with this paragraph. > > I simplified to make the main point stand out: it is not true that the >> database file remains "pristine" while the transaction is in progress, and >> changes are written only to the journal file. Instead, to the first >> approximation, the opposite is true. > > I agree it is not true in general, but it is mostly accurate for small > transactions No, it's never true (at least, not with the rollback journal - WAL is different still). Changes are not written to the journal; original unmodified pages are. -- Igor Tandetnik
[sqlite] Fwd: Sqlite is secure with MD5?
Is TCL being used (you have to specifically include the TCL interface ONLY if you are generating a DLL to be loaded into TCL)? Did you enable the MD5 commands? Why? If you do not want them being added to your TCL interpreter, then I would suggest perhaps not enabling the option which causes them to be included (as documented in the first few comment lines of the interface code, tclsqlite.c) I thought Firefox was written in C, so why does it care about the TCL interface, and why is it being included? Does Firefox have a TCL interpreter hidden inside it? > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of veer verma > Sent: Sunday, 28 February, 2016 22:40 > To: sqlite-users at mailinglists.sqlite.org > Subject: [sqlite] Fwd: Sqlite is secure with MD5? > > Hi, > > Can i get some help on this please? > > Thanks > Sameer > > Hello, > > I am using sqlite3-3.11.0, i See sqlite is using MD5 hash algorithm to > generate MD5 checksums for TCL interface. Since MD5 is broken for > purposes > of security, How can i replace MD5 with any other hash algorithm (sha-2) > to > keep it secure from malicious attacker? > Or Is there a way to compile sqlite without enabling TCl iinterface? if > yes, will disabling TCL interface would cause firefox 31 or any other > dependencies to break ? > > Thanks > Sameer > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ASK SQLite algoritm to chose Index
On Sunday, 28 February, 2016 09:17, Christoforus Surjoputro said: > 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. Take a look at your SELECT. You have told SQLite that "email" is UNIQUE. Yet in your SELECT you are matching on "password" too. SQLite has realised that since "email" must be UNIQUE it doesn't need the "password" column to find the right row. Having done that it picked the best index for the "email" column. I don't need to argue with you about this. There are very good answers to your question from other people. Simon.
[sqlite] Database layout in memory
Thank you for the quick response, Keith! As I understand it, the SQLite Backup API?s only work with an SQLite object. E.g.: sqlite3_backup_init() needs a pointer to the database to copy from. However, I do not have access to such a pointer, as the only thing I get is a raw binary dump of the main memory containing the database somewhere inside (think forensic dump). I believe the Backup API?s won?t be applicable. :-/ > On 28 Feb 2016, at 16:32, Keith Medcalf wrote: > > > Is there something wrong with using the backup api's? > >> -Original Message- >> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- >> bounces at mailinglists.sqlite.org] On Behalf Of Ren? Czerny >> Sent: Sunday, 28 February, 2016 08:22 >> To: SQLite mailing list >> Subject: [sqlite] Database layout in memory >> >> Dear SQLite mailing list, >> >> after not finding anything on Google, I want to ask my question here: >> >> I am currently doing research on how to extract an SQLite inmemory- >> database from the image of a computer?s main memory and store it as a >> database file on disc. My previous attempts however failed, as the >> database is not in one place in memory, but seems to be fragmented. I only >> managed to extract the database file containing the sqlite_master table. >> Here is what I tried: >> >> 1. Dump the main memory using LiME [0] on a Debian Wheezy system. >> 2. Opened the dump in a hex-editor and searched for patterns that indicate >> an SQLite database. (according to [1]) >> 3. Extracted the database file starting at the database header and >> retrieving (page-size * page-amount) bytes. >> >> The result did not include the tables? content, but only the schema. The >> content is at a total different offset in the memory dump. >> >> So my questions are: Can you point me to a resource where SQLite in-memory >> database layout is documented or described in a detailed way? Any other >> resources I should check out? Did I miss something? Is there another way? >> >> Please note, that in my scenario I only have the memory dump and in theory >> can?t make use of the live system. >> I am very glad for every input you could give me. >> >> Best regards, >> Ren? Czerny >> >> [0] https://github.com/504ensicsLabs/LiME >> [1] https://www.sqlite.org/fileformat2.html >> ___ >> 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] Database layout in memory
Dear SQLite mailing list, after not finding anything on Google, I want to ask my question here: I am currently doing research on how to extract an SQLite inmemory-database from the image of a computer?s main memory and store it as a database file on disc. My previous attempts however failed, as the database is not in one place in memory, but seems to be fragmented. I only managed to extract the database file containing the sqlite_master table. Here is what I tried: 1. Dump the main memory using LiME [0] on a Debian Wheezy system. 2. Opened the dump in a hex-editor and searched for patterns that indicate an SQLite database. (according to [1]) 3. Extracted the database file starting at the database header and retrieving (page-size * page-amount) bytes. The result did not include the tables? content, but only the schema. The content is at a total different offset in the memory dump. So my questions are: Can you point me to a resource where SQLite in-memory database layout is documented or described in a detailed way? Any other resources I should check out? Did I miss something? Is there another way? Please note, that in my scenario I only have the memory dump and in theory can?t make use of the live system. I am very glad for every input you could give me. Best regards, Ren? Czerny [0] https://github.com/504ensicsLabs/LiME [1] https://www.sqlite.org/fileformat2.html
[sqlite] ASK SQLite algoritm to chose Index
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 wrote: On 28 Feb 2016, at 5:57am, Scott Robison 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] Windows binaries compiled with ICU
Looking for %subj%, unable to find 'sqlite3.exe' with ICU extensions. Does it exist at all? If yes, couls anyone post link? thanks L.
[sqlite] ASK SQLite algoritm to chose Index
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 > 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 > > wrote: > > > > > > > > On 28 Feb 2016, at 5:57am, Scott Robison > 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
[sqlite] ASK SQLite algoritm to chose Index
On Sunday, 28 February, 2016 09:17, Christoforus Surjoputro 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 > wrote: > > > > On 28 Feb 2016, at 5:57am, Scott Robison 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] Database layout in memory
On Sunday, 28 February, 2016 08:46, Ren? Czerny said" > Thank you for the quick response, Keith! > As I understand it, the SQLite Backup API?s only work with an SQLite > object. E.g.: sqlite3_backup_init() needs a pointer to the database to > copy from. However, I do not have access to such a pointer, as the only > thing I get is a raw binary dump of the main memory containing the > database somewhere inside (think forensic dump). > I believe the Backup API?s won?t be applicable. :-/ You would have to find the db (connection) object in memory, and trace that through to the PCache to find all the database pages in memory -- equivalently to what the backup api does when sequentially accessing pages. The PCache must have an in-memory structure pointing to where each page is in memory. The database data pages themselves will not have that information. > > On 28 Feb 2016, at 16:32, Keith Medcalf wrote: > > > > > > Is there something wrong with using the backup api's? > > > >> -Original Message- > >> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite- > users- > >> bounces at mailinglists.sqlite.org] On Behalf Of Ren? Czerny > >> Sent: Sunday, 28 February, 2016 08:22 > >> To: SQLite mailing list > >> Subject: [sqlite] Database layout in memory > >> > >> Dear SQLite mailing list, > >> > >> after not finding anything on Google, I want to ask my question here: > >> > >> I am currently doing research on how to extract an SQLite inmemory- > >> database from the image of a computer?s main memory and store it as a > >> database file on disc. My previous attempts however failed, as the > >> database is not in one place in memory, but seems to be fragmented. I > only > >> managed to extract the database file containing the sqlite_master > table. > >> Here is what I tried: > >> > >> 1. Dump the main memory using LiME [0] on a Debian Wheezy system. > >> 2. Opened the dump in a hex-editor and searched for patterns that > indicate > >> an SQLite database. (according to [1]) > >> 3. Extracted the database file starting at the database header and > >> retrieving (page-size * page-amount) bytes. > >> > >> The result did not include the tables? content, but only the schema. > The > >> content is at a total different offset in the memory dump. > >> > >> So my questions are: Can you point me to a resource where SQLite in- > memory > >> database layout is documented or described in a detailed way? Any other > >> resources I should check out? Did I miss something? Is there another > way? > >> > >> Please note, that in my scenario I only have the memory dump and in > theory > >> can?t make use of the live system. > >> I am very glad for every input you could give me. > >> > >> Best regards, > >> Ren? Czerny > >> > >> [0] https://github.com/504ensicsLabs/LiME > >> [1] https://www.sqlite.org/fileformat2.html > >> ___ > >> 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] Database layout in memory
Just in case, here is where you can find documentation on the back API ... https://www.sqlite.org/backup.html > Dear SQLite mailing list, > > after not finding anything on Google, I want to ask my question here: > > I am currently doing research on how to extract an SQLite inmemory- > database from the image of a computer?s main memory and store it as a > database file on disc. My previous attempts however failed, as the > database is not in one place in memory, but seems to be fragmented. I only > managed to extract the database file containing the sqlite_master table. > Here is what I tried: > > 1. Dump the main memory using LiME [0] on a Debian Wheezy system. > 2. Opened the dump in a hex-editor and searched for patterns that indicate > an SQLite database. (according to [1]) > 3. Extracted the database file starting at the database header and > retrieving (page-size * page-amount) bytes. > > The result did not include the tables? content, but only the schema. The > content is at a total different offset in the memory dump. > > So my questions are: Can you point me to a resource where SQLite in-memory > database layout is documented or described in a detailed way? Any other > resources I should check out? Did I miss something? Is there another way? > > Please note, that in my scenario I only have the memory dump and in theory > can?t make use of the live system. > I am very glad for every input you could give me. > > Best regards, > Ren? Czerny > > [0] https://github.com/504ensicsLabs/LiME > [1] https://www.sqlite.org/fileformat2.html > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Database layout in memory
Is there something wrong with using the backup api's? > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Ren? Czerny > Sent: Sunday, 28 February, 2016 08:22 > To: SQLite mailing list > Subject: [sqlite] Database layout in memory > > Dear SQLite mailing list, > > after not finding anything on Google, I want to ask my question here: > > I am currently doing research on how to extract an SQLite inmemory- > database from the image of a computer?s main memory and store it as a > database file on disc. My previous attempts however failed, as the > database is not in one place in memory, but seems to be fragmented. I only > managed to extract the database file containing the sqlite_master table. > Here is what I tried: > > 1. Dump the main memory using LiME [0] on a Debian Wheezy system. > 2. Opened the dump in a hex-editor and searched for patterns that indicate > an SQLite database. (according to [1]) > 3. Extracted the database file starting at the database header and > retrieving (page-size * page-amount) bytes. > > The result did not include the tables? content, but only the schema. The > content is at a total different offset in the memory dump. > > So my questions are: Can you point me to a resource where SQLite in-memory > database layout is documented or described in a detailed way? Any other > resources I should check out? Did I miss something? Is there another way? > > Please note, that in my scenario I only have the memory dump and in theory > can?t make use of the live system. > I am very glad for every input you could give me. > > Best regards, > Ren? Czerny > > [0] https://github.com/504ensicsLabs/LiME > [1] https://www.sqlite.org/fileformat2.html > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ASK SQLite algoritm to chose Index
On 28 Feb 2016, at 5:57am, Scott Robison 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] ASK SQLite algoritm to chose Index
On 28 Feb 2016, at 5:47am, Keith Medcalf wrote: > I do not see a link ... do you see a link? I saw no link. The OP may not actually be posting to this mailing list. He may be using a web interface which does the posting for him. And the web interface may be faulty and not posting the link properly. Simon.
[sqlite] ASK SQLite algoritm to chose Index
This i the link: SQLite use autoindex instead my own index
| ? |
| ? | | ? | ? | ? | ? | ? |
| SQLite use autoindex instead my own indexI've problem with SQLite autoindex
in UNIQUE table. I've create table like below. c.execute('''CREATE TABLE user(
id INTEGER PRIMARY KEY, email TEXT NOT... |
| |
| View on stackoverflow.com | Preview by Yahoo |
| |
| ? |
On Sunday, February 28, 2016 12:44 AM, Keith Medcalf wrote:
No links seen from you, ever, on this thread.
SQLite automatically chooses the best index from those available.? The index it
is choosing is probably better than the one you want it to use002E
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Christoforus Surjoputro
> Sent: Saturday, 27 February, 2016 09:47
> To: SQLite mailing list
> Subject: Re: [sqlite] ASK SQLite algoritm to chose Index
>
> Hi Simon.
> I've post it in stackoverflow link I gave before. About run ANALYZE,
> SQLite still choose their own index instead Index that I've made. Thank
> you.
>
>
>? ? On Saturday, February 27, 2016 12:49 AM, Simon Slavin
> wrote:
>
>
>
> On 26 Feb 2016, at 5:45pm, Christoforus Surjoputro
> wrote:
>
> > I've problem with sqlite choosing index that I think I have better index
> to use. I've ask here but still didn't get why this happen? Did sqlite
> choose the best index to use or choose the last entered index?
>
> SQLite doesn't know which index was entered last.? It chose the best index
> based on what it knows.
>
> Can you post your TABLE definition and the INDEXes you defined on the
> table, and the result of EXPLAIN QUERY PLAN on your SELECT ?
>
> Please run ANALYZE, then do EXPLAIN QUERY PLAN on your SELECT again and
> tell us if anything changed.
>
> 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
