RE: [sqlite] sqlite too slow for me?
I believe that when using a transaction, i.e. Begin, Commit. SQLite will only perform the actual writing to file when Commit is made, therefore less disk I/O, or all I/O done at the end rather than after each insert and therefore better performance. See documentation at:- http://www.sqlite.org/speed.html P.S. I'm sure someone else will give a better explanation. > -Original Message- > From: Cesar David Rodas Maldonado [mailto:[EMAIL PROTECTED] > Sent: 29 June 2006 16:12 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] sqlite too slow for me? > > > I have a question for every body... > > > SQLite was very slow for my inserts (like 5 inserts), with out > sincronization > > but when i put BEGIN; before start with my inserts is was > faster... like > 1000 times more... :D why is that? > > > On 6/28/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote: > > > > I dont know a lot about MySQL... but mysql is not so faster as you > > think... > > I have in my computer the mysql 5 is good, but sqlite is so > faster too!!! > > > > but MySQL has a query cache so i think the cache make > it seems more > > faster than sqlite... > > > > > > > > > > On 6/27/06, Péter Szabó <[EMAIL PROTECTED]> wrote: > > > > > > First, thank you all for the answers. > > > > > > >UNIQUE(col1, col4, col5, col2), > > > > > > Adding this would surely make the query run faster -- > provided that > > > SQLite chooses the right index. But I also use the > UNIQUE(col1, col4, > > > col5) constraint to ensure the uniqueness of these three > columns. So > > > instead I should have both > > > > > > UNIQUE(col1, col4, col5), > > > UNIQUE(col1, col4, col5, col2), > > > > > > but this would waste my disk space. > > > > > > I am wondering how can MySQL 4.1 be so fast compared to SQLite 3? > > > MySQL answers my query in 0.02 seconds, and SQLite answers in more > > > than 28 seconds. > > > > > > I guess that MySQL doesn't do any magic either ( i.e. it > operates in > > > O(Klog N) time, which appears to be just 0.02 seconds), but MySQL > > > recognises that it should use the UNIQUE(col1, col4, col5) index, > > > while SQLite poorly chooses some other index, maybe the PRIMARY > > > KEY(col1, col2, col3, col4, col5), which is just wrong. > Can someone > > > confirm that the SQLite is using the wrong index? Is it > possible to > > > deterministically rewrite the query to force SQLite use the right > > > index? > > > > > > > > DISCLAIMER: This information and any attachments contained in this email message is intended only for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential, and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution, forwarding, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify the sender immediately by return email, and delete the original message immediately.
Re: [sqlite] sqlite too slow for me?
I have a question for every body... SQLite was very slow for my inserts (like 5 inserts), with out sincronization but when i put BEGIN; before start with my inserts is was faster... like 1000 times more... :D why is that? On 6/28/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote: I dont know a lot about MySQL... but mysql is not so faster as you think... I have in my computer the mysql 5 is good, but sqlite is so faster too!!! but MySQL has a query cache so i think the cache make it seems more faster than sqlite... On 6/27/06, Péter Szabó <[EMAIL PROTECTED]> wrote: > > First, thank you all for the answers. > > >UNIQUE(col1, col4, col5, col2), > > Adding this would surely make the query run faster -- provided that > SQLite chooses the right index. But I also use the UNIQUE(col1, col4, > col5) constraint to ensure the uniqueness of these three columns. So > instead I should have both > > UNIQUE(col1, col4, col5), > UNIQUE(col1, col4, col5, col2), > > but this would waste my disk space. > > I am wondering how can MySQL 4.1 be so fast compared to SQLite 3? > MySQL answers my query in 0.02 seconds, and SQLite answers in more > than 28 seconds. > > I guess that MySQL doesn't do any magic either ( i.e. it operates in > O(Klog N) time, which appears to be just 0.02 seconds), but MySQL > recognises that it should use the UNIQUE(col1, col4, col5) index, > while SQLite poorly chooses some other index, maybe the PRIMARY > KEY(col1, col2, col3, col4, col5), which is just wrong. Can someone > confirm that the SQLite is using the wrong index? Is it possible to > deterministically rewrite the query to force SQLite use the right > index? >
Re: [sqlite] sqlite too slow for me?
I dont know a lot about MySQL... but mysql is not so faster as you think... I have in my computer the mysql 5 is good, but sqlite is so faster too!!! but MySQL has a query cache so i think the cache make it seems more faster than sqlite... On 6/27/06, Péter Szabó <[EMAIL PROTECTED]> wrote: First, thank you all for the answers. >UNIQUE(col1, col4, col5, col2), Adding this would surely make the query run faster -- provided that SQLite chooses the right index. But I also use the UNIQUE(col1, col4, col5) constraint to ensure the uniqueness of these three columns. So instead I should have both UNIQUE(col1, col4, col5), UNIQUE(col1, col4, col5, col2), but this would waste my disk space. I am wondering how can MySQL 4.1 be so fast compared to SQLite 3? MySQL answers my query in 0.02 seconds, and SQLite answers in more than 28 seconds. I guess that MySQL doesn't do any magic either (i.e. it operates in O(Klog N) time, which appears to be just 0.02 seconds), but MySQL recognises that it should use the UNIQUE(col1, col4, col5) index, while SQLite poorly chooses some other index, maybe the PRIMARY KEY(col1, col2, col3, col4, col5), which is just wrong. Can someone confirm that the SQLite is using the wrong index? Is it possible to deterministically rewrite the query to force SQLite use the right index?
Re: [sqlite] sqlite too slow for me?
First, thank you all for the answers. UNIQUE(col1, col4, col5, col2), Adding this would surely make the query run faster -- provided that SQLite chooses the right index. But I also use the UNIQUE(col1, col4, col5) constraint to ensure the uniqueness of these three columns. So instead I should have both UNIQUE(col1, col4, col5), UNIQUE(col1, col4, col5, col2), but this would waste my disk space. I am wondering how can MySQL 4.1 be so fast compared to SQLite 3? MySQL answers my query in 0.02 seconds, and SQLite answers in more than 28 seconds. I guess that MySQL doesn't do any magic either (i.e. it operates in O(Klog N) time, which appears to be just 0.02 seconds), but MySQL recognises that it should use the UNIQUE(col1, col4, col5) index, while SQLite poorly chooses some other index, maybe the PRIMARY KEY(col1, col2, col3, col4, col5), which is just wrong. Can someone confirm that the SQLite is using the wrong index? Is it possible to deterministically rewrite the query to force SQLite use the right index?
Re: [sqlite] sqlite too slow for me?
--- [EMAIL PROTECTED] wrote: > "=?ISO-8859-1?Q?P=E9ter_Szab=F3?=" <[EMAIL PROTECTED]> wrote: > > > > My schema is the following: > > > > CREATE TABLE t ( > > col1 text NOT NULL, > > col2 integer NOT NULL, > > col3 integer NOT NULL, > > col4 text NOT NULL, > > col5 text NOT NULL, > > PRIMARY KEY(col1, col2, col3, col4, col5), > > UNIQUE(col1, col4, col5), > > UNIQUE(col4, col5, col1), > > UNIQUE(col5, col4, col1), > > ); > > > > > > This is also instant, possibly because SQLite is using UNIQUE(col1,col4,col= > > 5): > > > > SELECT col5 FROM t WHERE col1='foobar' AND > > col4='foobarfoobarfoobarfoob'; > > > > However, this is very slow in SQLite 3.3.5: > > > > SELECT col2 FROM t WHERE col1='foobar' AND > > col4='foobarfoobarfoobarfoob'; > > > > The second one will be much faster if you add the "col2" column > to the end of your first UNIQUE constraint: > >UNIQUE(col1, col4, col5, col2), The initial email says that col1 is always equal to 'foobar', and there are only 96 rows where col4='foobarfoobarfoobarfoob'. Wouldn't UNIQUE(col4, ...who cares...) be more efficient for his specific data set even if the table had to be hit to get the data corresponding to the key? > > Each UNIQUE constraint creates an index. In the case > where you have only the first 3 columns in the index, > the first 2 columns are used to lookup the rowid of > the table, then a binary search is performed for each > row of the table. If there are N rows in the entire > database and K rows of result, then this query requires > time O(KlogN). But if col2 is already part of the > index entry, there is no need to perform that binary > search and the value of col2 is extracted directly from > the index itself. This results in O(K + logN) run time, > which can be much faster, especially if the database > file is not in your disk cache. > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] sqlite too slow for me?
"=?ISO-8859-1?Q?P=E9ter_Szab=F3?=" <[EMAIL PROTECTED]> wrote: > > My schema is the following: > > CREATE TABLE t ( > col1 text NOT NULL, > col2 integer NOT NULL, > col3 integer NOT NULL, > col4 text NOT NULL, > col5 text NOT NULL, > PRIMARY KEY(col1, col2, col3, col4, col5), > UNIQUE(col1, col4, col5), > UNIQUE(col4, col5, col1), > UNIQUE(col5, col4, col1), > ); > > > This is also instant, possibly because SQLite is using UNIQUE(col1,col4,col= > 5): > > SELECT col5 FROM t WHERE col1='foobar' AND > col4='foobarfoobarfoobarfoob'; > > However, this is very slow in SQLite 3.3.5: > > SELECT col2 FROM t WHERE col1='foobar' AND > col4='foobarfoobarfoobarfoob'; > The second one will be much faster if you add the "col2" column to the end of your first UNIQUE constraint: UNIQUE(col1, col4, col5, col2), Each UNIQUE constraint creates an index. In the case where you have only the first 3 columns in the index, the first 2 columns are used to lookup the rowid of the table, then a binary search is performed for each row of the table. If there are N rows in the entire database and K rows of result, then this query requires time O(KlogN). But if col2 is already part of the index entry, there is no need to perform that binary search and the value of col2 is extracted directly from the index itself. This results in O(K + logN) run time, which can be much faster, especially if the database file is not in your disk cache. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] sqlite too slow for me?
"P?ter Szab?" <[EMAIL PROTECTED]> wrote: > Dear SQLite Developers, > > I am seeking help for optimizing my SQLite SQL query, which seems to > be running unreasonably slow. The query is: > > SELECT col2 FROM t WHERE col1='foobar' AND > col4='foobarfoobarfoobarfoob'; > > My schema is the following: > > CREATE TABLE t ( > col1 text NOT NULL, > col2 integer NOT NULL, > col3 integer NOT NULL, > col4 text NOT NULL, > col5 text NOT NULL, > PRIMARY KEY(col1, col2, col3, col4, col5), > UNIQUE(col1, col4, col5), > UNIQUE(col4, col5, col1), > UNIQUE(col5, col4, col1), > ); Just a guess: try SELECT +col2 FROM t ... Regards
