Re: [sqlite] understanding EXPLAIN
On Apr 18, 2008, at 9:37 AM, P Kishor wrote: > I am trying to learn EXPLAIN. I have a table like so with ~184K rows > > sqlite> .s > CREATE TABLE sg_rivers ( > ogc_fid INTEGER PRIMARY KEY, > wkt_geometry TEXT, > name TEXT, > xmin REAL, > ymin REAL, > xmax REAL, > ymax REAL > ); > CREATE INDEX ix_xmax__sg_rivers ON sg_rivers (xmax); > CREATE INDEX ix_xmin__sg_rivers ON sg_rivers (xmin); > CREATE INDEX ix_ymax__sg_rivers ON sg_rivers (ymax); > CREATE INDEX ix_ymin__sg_rivers ON sg_rivers (ymin); > sqlite> select xmin, ymin, xmax, ymax from sg_rivers limit 3; > xmin ymin xmax ymax > - > -89. 43.642034 -89. 43.6 > -89. 43.642501 -89. 43.6 > -89. 43.642991 -89. 43.6 > sqlite> explain select wkt_geometry from sg_rivers where xmin >= -90; > addr opcode p1p2p3p4 p5 comment > - - -- > - > 0 Trace 0 0 0 explain select wkt_geometry > from sg_rivers where xmin >= -90; 00 > 1 Goto 0 20000 > 2 OpenRead 0 100943 000 > 3 SetNumColumns 0 4 000 > 4 OpenRead 1 197485 0 keyinfo(1,BINARY) 00 > 5 SetNumColumns 1 2 000 > 6 Integer-90 2 000 > 7 IsNull 2 17000 > 8 MakeRecord 2 1 5 eb 00 > 9 MoveGe 1 17500 > 10Column 1 0 500 > 11IsNull 5 16000 > 12IdxRowid 1 5 000 > 13MoveGe 0 0 500 > 14Column 0 1 600 > 15ResultRow 6 1 000 > 16Next 1 10000 > 17Close 0 0 000 > 18Close 1 0 000 > 19Halt 0 0 000 > 20Transaction0 0 000 > 21VerifyCookie 0 47000 > 22TableLock 0 100943 0 sg_rivers 00 > 23Goto 0 2 000 > > Am I to understand from the above that my query is *not* using the > index ix_xmin__sg_rivers? If not, why not? I think it is. Instruction 4 opens the index. Instruction 9 seeks to the first entry in the index where (xmin>=-90). Instructions 10-15 return you a row and instruction 16 advances to the next index entry. Try EXPLAIN QUERY PLAN for output that is easier to read. > In any case, would I be better off with a compound index if I modify > my query to > > select wkt_geometry from sg_rivers where xmin >= ? and ymin >= ? and > xmax <= ? and ymax <= ?; No. The B-Tree structures that SQLite uses are not really suitable for this kind of thing. Ideally you need an R-Tree structure. In SQLite, only one of the four binary constraints in the WHERE clause could be optimized using an index. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] understanding EXPLAIN
I am trying to learn EXPLAIN. I have a table like so with ~184K rows sqlite> .s CREATE TABLE sg_rivers ( ogc_fid INTEGER PRIMARY KEY, wkt_geometry TEXT, name TEXT, xmin REAL, ymin REAL, xmax REAL, ymax REAL ); CREATE INDEX ix_xmax__sg_rivers ON sg_rivers (xmax); CREATE INDEX ix_xmin__sg_rivers ON sg_rivers (xmin); CREATE INDEX ix_ymax__sg_rivers ON sg_rivers (ymax); CREATE INDEX ix_ymin__sg_rivers ON sg_rivers (ymin); sqlite> select xmin, ymin, xmax, ymax from sg_rivers limit 3; xmin ymin xmax ymax - -89. 43.642034 -89. 43.6 -89. 43.642501 -89. 43.6 -89. 43.642991 -89. 43.6 sqlite> explain select wkt_geometry from sg_rivers where xmin >= -90; addr opcode p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 0 explain select wkt_geometry from sg_rivers where xmin >= -90; 00 1 Goto 0 20000 2 OpenRead 0 100943 000 3 SetNumColumns 0 4 000 4 OpenRead 1 197485 0 keyinfo(1,BINARY) 00 5 SetNumColumns 1 2 000 6 Integer-90 2 000 7 IsNull 2 17000 8 MakeRecord 2 1 5 eb 00 9 MoveGe 1 17500 10Column 1 0 500 11IsNull 5 16000 12IdxRowid 1 5 000 13MoveGe 0 0 500 14Column 0 1 600 15ResultRow 6 1 000 16Next 1 10000 17Close 0 0 000 18Close 1 0 000 19Halt 0 0 000 20Transaction0 0 000 21VerifyCookie 0 47000 22TableLock 0 100943 0 sg_rivers 00 23Goto 0 2 000 Am I to understand from the above that my query is *not* using the index ix_xmin__sg_rivers? If not, why not? In any case, would I be better off with a compound index if I modify my query to select wkt_geometry from sg_rivers where xmin >= ? and ymin >= ? and xmax <= ? and ymax <= ?; -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to select uncomitted rows?
Grr. Copy/paste error. The create statement was: CREATE TABLE t (id INTEGER PRIMARY KEY AUTOINCREMENT, config TEXT); On Thu, Apr 17, 2008 at 5:20 PM, Scott Hess <[EMAIL PROTECTED]> wrote: > Just to be clear on "try it out", I mean something like the following, > where A) is in one shell, and B) in another. > > A) ...> ./sqlite3 test.db > A) sqlite> CREATE TABLE t (id INTEGER AUTOINCREMENT PRIMARY KEY NOT > NULL, config TEXT); > B) ...> ./sqlite3 test.db > B) sqlite> BEGIN; > A) sqlite> BEGIN; > A) sqlite> INSERT INTO t (id, config) VALUES (null, 'connection a'); > B) sqlite> INSERT INTO t (id, config) VALUES (null, 'connection b'); > B) SQL error: database is locked > > -scott > > On Thu, Apr 17, 2008 at 5:17 PM, Scott Hess <[EMAIL PROTECTED]> wrote: >> What will happen if you use BEGIN is that multiple users can get into >> the configuration mode, but once one user gets past BEGIN and runs >> anything which updates the database, the updates in other transactions >> will start throwing SQLITE_LOCKED. Spin up two sqlite3 command-line >> tools against the same database and check it out. >> >> If you use BEGIN IMMEDIATE, then this problem won't occur, because >> multiple threads can't get past BEGIN IMMEDIATE on the same database >> in the first place :-). >> >> What you have is basically a revision-control problem. If you let >> multiple users configure at the same time, you're going to handle >> merging the config changes in a sensible way and handle conflicts. >> >> -scott >> >> >> On Thu, Apr 17, 2008 at 5:08 PM, Alex Katebi <[EMAIL PROTECTED]> wrote: >>> Scott, >>> >>> Every user will have thier own sqlite connection. So multiple users are >>> allowed for configuration. There will be one router connection to actually >>> act on the commited configurations. The router will act on >>> individual configuration rows. The router and the users interact with each >>> other via the database file only. They run on seperate processes. All the >>> router sees is configuration rows being inserted deleted or updated by >>> whomever. It does not care. So here is my next question. If I have temporary >>> triggers for the CLI users to keep track of the uncommited rows. Then I have >>> another temoprary trigger for the router to act on the configurations after >>> being committed, would this work? Would the temporary trigger in the router >>> connection actually trigger? I guess I need to try this out. >>> >>> This is my own home project. I am my own boss. Once I have my design >>> figured out I will post it for analysis. >>> >>> Thanks, >>> -Alex >>> >>> >>> On Thu, Apr 17, 2008 at 7:26 PM, Scott Hess <[EMAIL PROTECTED]> wrote: >>> OK. I suspect that you might find exposing the SQLite transaction semantics as part of your user interface may be ... unsatisfactory. For instance, by keeping a long-lived transaction in this way, you cannot set any of the _other_ data in the config and commit it. This would include other users, so, for instance, while one user is configuring something really complex, like firewall rules, another user would not be able to set the timezone, or turn on logging, or something like that. I don't know, this may be satisfactory, but it seems like a regrettable thing to design into the system at such a low level (not letting multiple people configure so that they don't screw up is good, but not allowing it just because your design didn't allow it, less good). As an alternative, you might consider layering your config-management over something like the undo/redo example (*). Since this is more explicit (_you_ craft the structures in terms of SQLite, rather than relying on SQLite's internal semantics), when upper management comes to you with some crazy feature request which does not conform to the SQL transaction model, you'll be able to change things without too much pain. -scott (*) http://www.sqlite.org/cvstrac/wiki?p=UndoRedo On Thu, Apr 17, 2008 at 3:56 PM, Alex Katebi <[EMAIL PROTECTED]> wrote: > I am glad you asked. I am designing an interactive command line > interface to an ip router. A user will begin a transaction and start > configuring. At any time he can query for his configurations since the > begining of the transaction. When he is satisfied with his configuration he > will commit the configuration. After this his query should show nothing > until he begins another transaction. Also he might press the ?mark key on > his keyboard at anytime for help information or tab key for automatic > command token completion. > > So I will have to know what are the list of commands since the beginning > of his transaction. > > > > On Thu, Apr 17, 2008 at 3:53 PM, Scott Hess <[EMAIL PROTECTED]> wrote: > >> I don't mean in a separate database table - I
Re: [sqlite] how to select uncomitted rows?
Just to be clear on "try it out", I mean something like the following, where A) is in one shell, and B) in another. A) ...> ./sqlite3 test.db A) sqlite> CREATE TABLE t (id INTEGER AUTOINCREMENT PRIMARY KEY NOT NULL, config TEXT); B) ...> ./sqlite3 test.db B) sqlite> BEGIN; A) sqlite> BEGIN; A) sqlite> INSERT INTO t (id, config) VALUES (null, 'connection a'); B) sqlite> INSERT INTO t (id, config) VALUES (null, 'connection b'); B) SQL error: database is locked -scott On Thu, Apr 17, 2008 at 5:17 PM, Scott Hess <[EMAIL PROTECTED]> wrote: > What will happen if you use BEGIN is that multiple users can get into > the configuration mode, but once one user gets past BEGIN and runs > anything which updates the database, the updates in other transactions > will start throwing SQLITE_LOCKED. Spin up two sqlite3 command-line > tools against the same database and check it out. > > If you use BEGIN IMMEDIATE, then this problem won't occur, because > multiple threads can't get past BEGIN IMMEDIATE on the same database > in the first place :-). > > What you have is basically a revision-control problem. If you let > multiple users configure at the same time, you're going to handle > merging the config changes in a sensible way and handle conflicts. > > -scott > > > On Thu, Apr 17, 2008 at 5:08 PM, Alex Katebi <[EMAIL PROTECTED]> wrote: >> Scott, >> >> Every user will have thier own sqlite connection. So multiple users are >> allowed for configuration. There will be one router connection to actually >> act on the commited configurations. The router will act on >> individual configuration rows. The router and the users interact with each >> other via the database file only. They run on seperate processes. All the >> router sees is configuration rows being inserted deleted or updated by >> whomever. It does not care. So here is my next question. If I have temporary >> triggers for the CLI users to keep track of the uncommited rows. Then I have >> another temoprary trigger for the router to act on the configurations after >> being committed, would this work? Would the temporary trigger in the router >> connection actually trigger? I guess I need to try this out. >> >> This is my own home project. I am my own boss. Once I have my design >> figured out I will post it for analysis. >> >> Thanks, >> -Alex >> >> >> On Thu, Apr 17, 2008 at 7:26 PM, Scott Hess <[EMAIL PROTECTED]> wrote: >> >>> OK. I suspect that you might find exposing the SQLite transaction >>> semantics as part of your user interface may be ... unsatisfactory. >>> For instance, by keeping a long-lived transaction in this way, you >>> cannot set any of the _other_ data in the config and commit it. This >>> would include other users, so, for instance, while one user is >>> configuring something really complex, like firewall rules, another >>> user would not be able to set the timezone, or turn on logging, or >>> something like that. I don't know, this may be satisfactory, but it >>> seems like a regrettable thing to design into the system at such a low >>> level (not letting multiple people configure so that they don't screw >>> up is good, but not allowing it just because your design didn't allow >>> it, less good). >>> >>> As an alternative, you might consider layering your config-management >>> over something like the undo/redo example (*). Since this is more >>> explicit (_you_ craft the structures in terms of SQLite, rather than >>> relying on SQLite's internal semantics), when upper management comes >>> to you with some crazy feature request which does not conform to the >>> SQL transaction model, you'll be able to change things without too >>> much pain. >>> >>> -scott >>> >>> (*) http://www.sqlite.org/cvstrac/wiki?p=UndoRedo >>> >>> >>> On Thu, Apr 17, 2008 at 3:56 PM, Alex Katebi <[EMAIL PROTECTED]> >>> wrote: >>> > I am glad you asked. I am designing an interactive command line >>> > interface to an ip router. A user will begin a transaction and start >>> > configuring. At any time he can query for his configurations since the >>> > begining of the transaction. When he is satisfied with his configuration >>> he >>> > will commit the configuration. After this his query should show nothing >>> > until he begins another transaction. Also he might press the ?mark key >>> on >>> > his keyboard at anytime for help information or tab key for automatic >>> > command token completion. >>> > >>> > So I will have to know what are the list of commands since the beginning >>> > of his transaction. >>> > >>> > >>> > >>> > On Thu, Apr 17, 2008 at 3:53 PM, Scott Hess <[EMAIL PROTECTED]> wrote: >>> > >>> >> I don't mean in a separate database table - I mean in an in-memory >>> >> hashtable or array or something of the sort. Depending on what the >>> >> real goal you're trying to accomplish is, you might use triggers to >>> >> call custom function to accomplish this. >>> >> >>> >> You presumably desire to get this information in the interests of >>> >>
Re: [sqlite] how to select uncomitted rows?
What will happen if you use BEGIN is that multiple users can get into the configuration mode, but once one user gets past BEGIN and runs anything which updates the database, the updates in other transactions will start throwing SQLITE_LOCKED. Spin up two sqlite3 command-line tools against the same database and check it out. If you use BEGIN IMMEDIATE, then this problem won't occur, because multiple threads can't get past BEGIN IMMEDIATE on the same database in the first place :-). What you have is basically a revision-control problem. If you let multiple users configure at the same time, you're going to handle merging the config changes in a sensible way and handle conflicts. -scott On Thu, Apr 17, 2008 at 5:08 PM, Alex Katebi <[EMAIL PROTECTED]> wrote: > Scott, > > Every user will have thier own sqlite connection. So multiple users are > allowed for configuration. There will be one router connection to actually > act on the commited configurations. The router will act on > individual configuration rows. The router and the users interact with each > other via the database file only. They run on seperate processes. All the > router sees is configuration rows being inserted deleted or updated by > whomever. It does not care. So here is my next question. If I have temporary > triggers for the CLI users to keep track of the uncommited rows. Then I have > another temoprary trigger for the router to act on the configurations after > being committed, would this work? Would the temporary trigger in the router > connection actually trigger? I guess I need to try this out. > > This is my own home project. I am my own boss. Once I have my design > figured out I will post it for analysis. > > Thanks, > -Alex > > > On Thu, Apr 17, 2008 at 7:26 PM, Scott Hess <[EMAIL PROTECTED]> wrote: > >> OK. I suspect that you might find exposing the SQLite transaction >> semantics as part of your user interface may be ... unsatisfactory. >> For instance, by keeping a long-lived transaction in this way, you >> cannot set any of the _other_ data in the config and commit it. This >> would include other users, so, for instance, while one user is >> configuring something really complex, like firewall rules, another >> user would not be able to set the timezone, or turn on logging, or >> something like that. I don't know, this may be satisfactory, but it >> seems like a regrettable thing to design into the system at such a low >> level (not letting multiple people configure so that they don't screw >> up is good, but not allowing it just because your design didn't allow >> it, less good). >> >> As an alternative, you might consider layering your config-management >> over something like the undo/redo example (*). Since this is more >> explicit (_you_ craft the structures in terms of SQLite, rather than >> relying on SQLite's internal semantics), when upper management comes >> to you with some crazy feature request which does not conform to the >> SQL transaction model, you'll be able to change things without too >> much pain. >> >> -scott >> >> (*) http://www.sqlite.org/cvstrac/wiki?p=UndoRedo >> >> >> On Thu, Apr 17, 2008 at 3:56 PM, Alex Katebi <[EMAIL PROTECTED]> >> wrote: >> > I am glad you asked. I am designing an interactive command line >> > interface to an ip router. A user will begin a transaction and start >> > configuring. At any time he can query for his configurations since the >> > begining of the transaction. When he is satisfied with his configuration >> he >> > will commit the configuration. After this his query should show nothing >> > until he begins another transaction. Also he might press the ?mark key >> on >> > his keyboard at anytime for help information or tab key for automatic >> > command token completion. >> > >> > So I will have to know what are the list of commands since the beginning >> > of his transaction. >> > >> > >> > >> > On Thu, Apr 17, 2008 at 3:53 PM, Scott Hess <[EMAIL PROTECTED]> wrote: >> > >> >> I don't mean in a separate database table - I mean in an in-memory >> >> hashtable or array or something of the sort. Depending on what the >> >> real goal you're trying to accomplish is, you might use triggers to >> >> call custom function to accomplish this. >> >> >> >> You presumably desire to get this information in the interests of >> >> implementing a solution to a problem. You should perhaps post asking >> >> for suggestions on how to solve the problem. I think the question >> >> itself probably indicates that there's a disconnect in how you're >> >> trying to model the problem, but without knowing what the problem is, >> >> it's hard to do much. >> >> >> >> -scott >> >> >> >> On Thu, Apr 17, 2008 at 12:43 PM, Alex Katebi <[EMAIL PROTECTED]> >> >> wrote: >> >> > The reason I did not keep track in a seperate table was because I >> wanted >> >> to >> >> > do it using triggers. But triggers don't trigger until commit. >> >> > >> >> > On Thu, Apr 17, 2008 at 3:36 PM, Scott Hess
Re: [sqlite] how to select uncomitted rows?
Scott,
Every user will have thier own sqlite connection. So multiple users are
allowed for configuration. There will be one router connection to actually
act on the commited configurations. The router will act on
individual configuration rows. The router and the users interact with each
other via the database file only. They run on seperate processes. All the
router sees is configuration rows being inserted deleted or updated by
whomever. It does not care. So here is my next question. If I have temporary
triggers for the CLI users to keep track of the uncommited rows. Then I have
another temoprary trigger for the router to act on the configurations after
being committed, would this work? Would the temporary trigger in the router
connection actually trigger? I guess I need to try this out.
This is my own home project. I am my own boss. Once I have my design
figured out I will post it for analysis.
Thanks,
-Alex
On Thu, Apr 17, 2008 at 7:26 PM, Scott Hess <[EMAIL PROTECTED]> wrote:
> OK. I suspect that you might find exposing the SQLite transaction
> semantics as part of your user interface may be ... unsatisfactory.
> For instance, by keeping a long-lived transaction in this way, you
> cannot set any of the _other_ data in the config and commit it. This
> would include other users, so, for instance, while one user is
> configuring something really complex, like firewall rules, another
> user would not be able to set the timezone, or turn on logging, or
> something like that. I don't know, this may be satisfactory, but it
> seems like a regrettable thing to design into the system at such a low
> level (not letting multiple people configure so that they don't screw
> up is good, but not allowing it just because your design didn't allow
> it, less good).
>
> As an alternative, you might consider layering your config-management
> over something like the undo/redo example (*). Since this is more
> explicit (_you_ craft the structures in terms of SQLite, rather than
> relying on SQLite's internal semantics), when upper management comes
> to you with some crazy feature request which does not conform to the
> SQL transaction model, you'll be able to change things without too
> much pain.
>
> -scott
>
> (*) http://www.sqlite.org/cvstrac/wiki?p=UndoRedo
>
>
> On Thu, Apr 17, 2008 at 3:56 PM, Alex Katebi <[EMAIL PROTECTED]>
> wrote:
> > I am glad you asked. I am designing an interactive command line
> > interface to an ip router. A user will begin a transaction and start
> > configuring. At any time he can query for his configurations since the
> > begining of the transaction. When he is satisfied with his configuration
> he
> > will commit the configuration. After this his query should show nothing
> > until he begins another transaction. Also he might press the ?mark key
> on
> > his keyboard at anytime for help information or tab key for automatic
> > command token completion.
> >
> > So I will have to know what are the list of commands since the beginning
> > of his transaction.
> >
> >
> >
> > On Thu, Apr 17, 2008 at 3:53 PM, Scott Hess <[EMAIL PROTECTED]> wrote:
> >
> >> I don't mean in a separate database table - I mean in an in-memory
> >> hashtable or array or something of the sort. Depending on what the
> >> real goal you're trying to accomplish is, you might use triggers to
> >> call custom function to accomplish this.
> >>
> >> You presumably desire to get this information in the interests of
> >> implementing a solution to a problem. You should perhaps post asking
> >> for suggestions on how to solve the problem. I think the question
> >> itself probably indicates that there's a disconnect in how you're
> >> trying to model the problem, but without knowing what the problem is,
> >> it's hard to do much.
> >>
> >> -scott
> >>
> >> On Thu, Apr 17, 2008 at 12:43 PM, Alex Katebi <[EMAIL PROTECTED]>
> >> wrote:
> >> > The reason I did not keep track in a seperate table was because I
> wanted
> >> to
> >> > do it using triggers. But triggers don't trigger until commit.
> >> >
> >> > On Thu, Apr 17, 2008 at 3:36 PM, Scott Hess <[EMAIL PROTECTED]> wrote:
> >> >
> >> >> Until the data is committed, it's not really in the database. If
> you
> >> >> crash, it will be rolled back. So if it's really important to know
> >> >> what data has been written to the database but not committed, why
> >> >> don't you just track what you're writing to the database in an
> >> >> in-memory data structure of some sort? Or, to save space, just
> track
> >> >> the rowid of the rows you modify.
> >> >>
> >> >> -scott
> >> >>
> >> >>
> >> >> On Thu, Apr 17, 2008 at 12:33 PM, Alex Katebi <[EMAIL PROTECTED]
> >
> >> >> wrote:
> >> >> > Hi Richard,
> >> >> >
> >> >> > create table t1 (name);
> >> >> > insert into t1 values ('Alex');
> >> >> > begin;
> >> >> > insert into t1 values ('Richard');
> >> >> > select * from t1;
> >> >> >
> >> >> > How can I select only the second row in the above example?
> >> >> > If
Re: [sqlite] how to select uncomitted rows?
I am glad you asked. I am designing an interactive command line
interface to an ip router. A user will begin a transaction and start
configuring. At any time he can query for his configurations since the
begining of the transaction. When he is satisfied with his configuration he
will commit the configuration. After this his query should show nothing
until he begins another transaction. Also he might press the ?mark key on
his keyboard at anytime for help information or tab key for automatic
command token completion.
So I will have to know what are the list of commands since the beginning
of his transaction.
On Thu, Apr 17, 2008 at 3:53 PM, Scott Hess <[EMAIL PROTECTED]> wrote:
> I don't mean in a separate database table - I mean in an in-memory
> hashtable or array or something of the sort. Depending on what the
> real goal you're trying to accomplish is, you might use triggers to
> call custom function to accomplish this.
>
> You presumably desire to get this information in the interests of
> implementing a solution to a problem. You should perhaps post asking
> for suggestions on how to solve the problem. I think the question
> itself probably indicates that there's a disconnect in how you're
> trying to model the problem, but without knowing what the problem is,
> it's hard to do much.
>
> -scott
>
> On Thu, Apr 17, 2008 at 12:43 PM, Alex Katebi <[EMAIL PROTECTED]>
> wrote:
> > The reason I did not keep track in a seperate table was because I wanted
> to
> > do it using triggers. But triggers don't trigger until commit.
> >
> > On Thu, Apr 17, 2008 at 3:36 PM, Scott Hess <[EMAIL PROTECTED]> wrote:
> >
> >> Until the data is committed, it's not really in the database. If you
> >> crash, it will be rolled back. So if it's really important to know
> >> what data has been written to the database but not committed, why
> >> don't you just track what you're writing to the database in an
> >> in-memory data structure of some sort? Or, to save space, just track
> >> the rowid of the rows you modify.
> >>
> >> -scott
> >>
> >>
> >> On Thu, Apr 17, 2008 at 12:33 PM, Alex Katebi <[EMAIL PROTECTED]>
> >> wrote:
> >> > Hi Richard,
> >> >
> >> > create table t1 (name);
> >> > insert into t1 values ('Alex');
> >> > begin;
> >> > insert into t1 values ('Richard');
> >> > select * from t1;
> >> >
> >> > How can I select only the second row in the above example?
> >> > If there is not an easy way to do this I would probably have to use
> >> another
> >> > connection then diff the two selects right?
> >> >
> >> > Thanks,
> >> > -Alex
> >> >
> >> >
> >> >
> >> >
> >> >
> >> > On Thu, Apr 17, 2008 at 2:38 PM, D. Richard Hipp <[EMAIL PROTECTED]>
> wrote:
> >> >
> >> >>
> >> >> On Apr 17, 2008, at 2:35 PM, Alex Katebi wrote:
> >> >> > Is there a way to select rows that have not been committed yet?
> >> >> >
> >> >>
> >> >> No. SQLite doesn't really commit rows. It commits pages. A
> >> >> single page might hold multiple rows, only some of which might
> >> >> have changed. Or a single row might span multiple pages.
> >> >>
> >> >>
> >> >> D. Richard Hipp
> >> >> [EMAIL PROTECTED]
> >> >>
> >> >>
> >> >>
> >> >> ___
> >> >> sqlite-users mailing list
> >> >> sqlite-users@sqlite.org
> >> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >> >>
> >> > ___
> >> > sqlite-users mailing list
> >> > sqlite-users@sqlite.org
> >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >> >
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to select uncomitted rows?
Yap I was wrong about triggers. Triggers are part of the same connection. So I will try your suggestions. I will let you know how I made out. And thanks so much for clearing my mistakes. -Alex On Thu, Apr 17, 2008 at 6:06 PM, Alex Katebi <[EMAIL PROTECTED]> wrote: > I remember trying it before but I will try it again. Maybe I was wrong. I > will let you know. > Thanks! > > On Thu, Apr 17, 2008 at 4:43 PM, Dennis Cote <[EMAIL PROTECTED]> > wrote: > > > Alex Katebi wrote: > > > But triggers don't trigger until commit. > > > > > > > That is not true. > > > > Trigger code executes inline with the statement that caused the trigger > > to fire. > > > > Try a few triggers with the command line shell to convince yourself. > > > > Dennis Cote > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] selecting uncommited rows
Hi Dennis,
I am sorry I was wrong about triggers. My understanding of triggers was
incorrect. Triggers are part of the same connection. I will take your
suggestions. I will let you know.
Thanks so much for helping out!
-Alex
On Thu, Apr 17, 2008 at 6:11 PM, Dennis Cote <[EMAIL PROTECTED]> wrote:
> Alex Katebi wrote:
> > My problem is that triggers don't trigger until after commit.
> >
>
> No, that is not your problem. You haven't tried anything yet.
>
> This is a trace of SQlite executing the code I posted. There is not a
> commit in sight, and yet it works as expected. I added an unqualified
> select to dump the entire table so you can see the difference.
>
> SQLite version 3.5.7
> Enter ".help" for instructions
> sqlite> create table t1(name);
> sqlite> insert into t1 values('Alex');
> sqlite>
> sqlite> create table change_log (id integer primary key);
> sqlite>
> sqlite> create trigger in_t1 after insert on t1
>...> begin
>...> insert into change_log values(new.rowid);
>...> end;
> sqlite>
> sqlite> delete from change_log;
> sqlite> begin;
> sqlite> insert into t1 values('Dennis');
> sqlite> select * from t1 where rowid in (select id from change_log);
> Dennis
> sqlite> select * from t1;
> Alex
> Dennis
> sqlite>
>
> Try it before you decide it won't work.
>
> Dennis Cote
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] selecting uncommited rows
Alex Katebi wrote:
> My problem is that triggers don't trigger until after commit.
>
No, that is not your problem. You haven't tried anything yet.
This is a trace of SQlite executing the code I posted. There is not a
commit in sight, and yet it works as expected. I added an unqualified
select to dump the entire table so you can see the difference.
SQLite version 3.5.7
Enter ".help" for instructions
sqlite> create table t1(name);
sqlite> insert into t1 values('Alex');
sqlite>
sqlite> create table change_log (id integer primary key);
sqlite>
sqlite> create trigger in_t1 after insert on t1
...> begin
...> insert into change_log values(new.rowid);
...> end;
sqlite>
sqlite> delete from change_log;
sqlite> begin;
sqlite> insert into t1 values('Dennis');
sqlite> select * from t1 where rowid in (select id from change_log);
Dennis
sqlite> select * from t1;
Alex
Dennis
sqlite>
Try it before you decide it won't work.
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to select uncomitted rows?
I remember trying it before but I will try it again. Maybe I was wrong. I will let you know. Thanks! On Thu, Apr 17, 2008 at 4:43 PM, Dennis Cote <[EMAIL PROTECTED]> wrote: > Alex Katebi wrote: > > But triggers don't trigger until commit. > > > > That is not true. > > Trigger code executes inline with the statement that caused the trigger > to fire. > > Try a few triggers with the command line shell to convince yourself. > > Dennis Cote > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] selecting uncommited rows
My problem is that triggers don't trigger until after commit.
On Thu, Apr 17, 2008 at 4:52 PM, Dennis Cote <[EMAIL PROTECTED]> wrote:
> Alex Katebi wrote:
> > I will give a simple example:
> >
> > create table t1(name);
> > insert into t1('Alex');
> > begin;
> > insert into t1 values ('Dennis');
> > select * from t1;
> >
> > How can I see only the 'Dennis' row in this simple example.
> >
>
> As I said before, use a trigger to keep track of the changes.
>
> create table t1(name);
> insert into t1('Alex');
>
> create table change_log (id integer primary key);
>
> create trigger in_t1 after insert on t1
> begin
> insert into change_log values(new.rowid);
> end;
>
> delete from change_log;
> begin;
> insert into t1 values ('Dennis');
> select * from t1 where rowid in (select id from change_log);
>
> Now either commit or rollback to end your transaction.
>
> Dennis Cote
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] explain query plan?
Petite Abeille wrote: > > what's the meaning of the first two numeral columns in > explain query plan? > The columns of the explain query plan output are labeled by SQLite as: sqlite> .header on sqlite> .mode column sqlite> explain query plan select * from t; order fromdetail -- -- -- 0 0 TABLE t I believe the order is simply the order the tables are scanned. With the number incremented only when the scan is nested inside the other scan. Rows that are sequential with the same order number are executed sequentially rather than as a nested loop. I don't know what the from column is for. The source passes a variable called iFrom which is commented as "First unused FROM clause element". See http://www.sqlite.org/cvstrac/wiki?p=QueryPlans and http://www.sqlite.org/optoverview.html for some more info. There is a slide show from a presentation that Richard gave which explains how SQLite execute joins as nested for loops, but I can't find it now. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_changes question
If I do: sqlite3_exec(..."UPDATE foo..."...) and then: numChanges = sqlite3_changes() I get the number of updated rows back. My question is, if I changed the statement in the single exec call to "BEGIN IMMEDIATE;UPDATE foo...;COMMIT", will sqlite3_changes still return the same value as before? Or do I need to make the BEGIN/COMMIT in separate exec calls from the UPDATE to get my count? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] selecting uncommited rows
Alex Katebi wrote:
> I will give a simple example:
>
> create table t1(name);
> insert into t1('Alex');
> begin;
> insert into t1 values ('Dennis');
> select * from t1;
>
> How can I see only the 'Dennis' row in this simple example.
>
As I said before, use a trigger to keep track of the changes.
create table t1(name);
insert into t1('Alex');
create table change_log (id integer primary key);
create trigger in_t1 after insert on t1
begin
insert into change_log values(new.rowid);
end;
delete from change_log;
begin;
insert into t1 values ('Dennis');
select * from t1 where rowid in (select id from change_log);
Now either commit or rollback to end your transaction.
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to select uncomitted rows?
Alex Katebi wrote: > But triggers don't trigger until commit. > That is not true. Trigger code executes inline with the statement that caused the trigger to fire. Try a few triggers with the command line shell to convince yourself. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to select uncomitted rows?
I don't mean in a separate database table - I mean in an in-memory
hashtable or array or something of the sort. Depending on what the
real goal you're trying to accomplish is, you might use triggers to
call custom function to accomplish this.
You presumably desire to get this information in the interests of
implementing a solution to a problem. You should perhaps post asking
for suggestions on how to solve the problem. I think the question
itself probably indicates that there's a disconnect in how you're
trying to model the problem, but without knowing what the problem is,
it's hard to do much.
-scott
On Thu, Apr 17, 2008 at 12:43 PM, Alex Katebi <[EMAIL PROTECTED]> wrote:
> The reason I did not keep track in a seperate table was because I wanted to
> do it using triggers. But triggers don't trigger until commit.
>
> On Thu, Apr 17, 2008 at 3:36 PM, Scott Hess <[EMAIL PROTECTED]> wrote:
>
>> Until the data is committed, it's not really in the database. If you
>> crash, it will be rolled back. So if it's really important to know
>> what data has been written to the database but not committed, why
>> don't you just track what you're writing to the database in an
>> in-memory data structure of some sort? Or, to save space, just track
>> the rowid of the rows you modify.
>>
>> -scott
>>
>>
>> On Thu, Apr 17, 2008 at 12:33 PM, Alex Katebi <[EMAIL PROTECTED]>
>> wrote:
>> > Hi Richard,
>> >
>> > create table t1 (name);
>> > insert into t1 values ('Alex');
>> > begin;
>> > insert into t1 values ('Richard');
>> > select * from t1;
>> >
>> > How can I select only the second row in the above example?
>> > If there is not an easy way to do this I would probably have to use
>> another
>> > connection then diff the two selects right?
>> >
>> > Thanks,
>> > -Alex
>> >
>> >
>> >
>> >
>> >
>> > On Thu, Apr 17, 2008 at 2:38 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
>> >
>> >>
>> >> On Apr 17, 2008, at 2:35 PM, Alex Katebi wrote:
>> >> > Is there a way to select rows that have not been committed yet?
>> >> >
>> >>
>> >> No. SQLite doesn't really commit rows. It commits pages. A
>> >> single page might hold multiple rows, only some of which might
>> >> have changed. Or a single row might span multiple pages.
>> >>
>> >>
>> >> D. Richard Hipp
>> >> [EMAIL PROTECTED]
>> >>
>> >>
>> >>
>> >> ___
>> >> sqlite-users mailing list
>> >> sqlite-users@sqlite.org
>> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >>
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to select uncomitted rows?
Another approach could be to create an in-memory database (and in in-
memory table, like CREATE TABLE last_transaction(id INTEGER);)
and after each write operation save the rowid of the row using
sqlite3_last_insert_rowid (in C) or using SELECT last_insert_rowid();
(SQL) into that table.
---
Marco Bambini
http://www.sqlabs.net
http://www.sqlabs.net/blog/
http://www.sqlabs.net/realsqlserver/
On Apr 17, 2008, at 9:43 PM, Alex Katebi wrote:
> The reason I did not keep track in a seperate table was because I
> wanted to
> do it using triggers. But triggers don't trigger until commit.
>
> On Thu, Apr 17, 2008 at 3:36 PM, Scott Hess <[EMAIL PROTECTED]> wrote:
>
>> Until the data is committed, it's not really in the database. If you
>> crash, it will be rolled back. So if it's really important to know
>> what data has been written to the database but not committed, why
>> don't you just track what you're writing to the database in an
>> in-memory data structure of some sort? Or, to save space, just track
>> the rowid of the rows you modify.
>>
>> -scott
>>
>>
>> On Thu, Apr 17, 2008 at 12:33 PM, Alex Katebi <[EMAIL PROTECTED]>
>> wrote:
>>> Hi Richard,
>>>
>>> create table t1 (name);
>>> insert into t1 values ('Alex');
>>> begin;
>>> insert into t1 values ('Richard');
>>> select * from t1;
>>>
>>> How can I select only the second row in the above example?
>>> If there is not an easy way to do this I would probably have to use
>> another
>>> connection then diff the two selects right?
>>>
>>> Thanks,
>>> -Alex
>>>
>>>
>>>
>>>
>>>
>>> On Thu, Apr 17, 2008 at 2:38 PM, D. Richard Hipp <[EMAIL PROTECTED]>
>>> wrote:
>>>
On Apr 17, 2008, at 2:35 PM, Alex Katebi wrote:
> Is there a way to select rows that have not been committed yet?
>
No. SQLite doesn't really commit rows. It commits pages. A
single page might hold multiple rows, only some of which might
have changed. Or a single row might span multiple pages.
D. Richard Hipp
[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to select uncomitted rows?
The reason I did not keep track in a seperate table was because I wanted to
do it using triggers. But triggers don't trigger until commit.
On Thu, Apr 17, 2008 at 3:36 PM, Scott Hess <[EMAIL PROTECTED]> wrote:
> Until the data is committed, it's not really in the database. If you
> crash, it will be rolled back. So if it's really important to know
> what data has been written to the database but not committed, why
> don't you just track what you're writing to the database in an
> in-memory data structure of some sort? Or, to save space, just track
> the rowid of the rows you modify.
>
> -scott
>
>
> On Thu, Apr 17, 2008 at 12:33 PM, Alex Katebi <[EMAIL PROTECTED]>
> wrote:
> > Hi Richard,
> >
> > create table t1 (name);
> > insert into t1 values ('Alex');
> > begin;
> > insert into t1 values ('Richard');
> > select * from t1;
> >
> > How can I select only the second row in the above example?
> > If there is not an easy way to do this I would probably have to use
> another
> > connection then diff the two selects right?
> >
> > Thanks,
> > -Alex
> >
> >
> >
> >
> >
> > On Thu, Apr 17, 2008 at 2:38 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
> >
> >>
> >> On Apr 17, 2008, at 2:35 PM, Alex Katebi wrote:
> >> > Is there a way to select rows that have not been committed yet?
> >> >
> >>
> >> No. SQLite doesn't really commit rows. It commits pages. A
> >> single page might hold multiple rows, only some of which might
> >> have changed. Or a single row might span multiple pages.
> >>
> >>
> >> D. Richard Hipp
> >> [EMAIL PROTECTED]
> >>
> >>
> >>
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to select uncomitted rows?
Until the data is committed, it's not really in the database. If you
crash, it will be rolled back. So if it's really important to know
what data has been written to the database but not committed, why
don't you just track what you're writing to the database in an
in-memory data structure of some sort? Or, to save space, just track
the rowid of the rows you modify.
-scott
On Thu, Apr 17, 2008 at 12:33 PM, Alex Katebi <[EMAIL PROTECTED]> wrote:
> Hi Richard,
>
> create table t1 (name);
> insert into t1 values ('Alex');
> begin;
> insert into t1 values ('Richard');
> select * from t1;
>
> How can I select only the second row in the above example?
> If there is not an easy way to do this I would probably have to use another
> connection then diff the two selects right?
>
> Thanks,
> -Alex
>
>
>
>
>
> On Thu, Apr 17, 2008 at 2:38 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
>
>>
>> On Apr 17, 2008, at 2:35 PM, Alex Katebi wrote:
>> > Is there a way to select rows that have not been committed yet?
>> >
>>
>> No. SQLite doesn't really commit rows. It commits pages. A
>> single page might hold multiple rows, only some of which might
>> have changed. Or a single row might span multiple pages.
>>
>>
>> D. Richard Hipp
>> [EMAIL PROTECTED]
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to select uncomitted rows?
Hi Richard,
create table t1 (name);
insert into t1 values ('Alex');
begin;
insert into t1 values ('Richard');
select * from t1;
How can I select only the second row in the above example?
If there is not an easy way to do this I would probably have to use another
connection then diff the two selects right?
Thanks,
-Alex
On Thu, Apr 17, 2008 at 2:38 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
>
> On Apr 17, 2008, at 2:35 PM, Alex Katebi wrote:
> > Is there a way to select rows that have not been committed yet?
> >
>
> No. SQLite doesn't really commit rows. It commits pages. A
> single page might hold multiple rows, only some of which might
> have changed. Or a single row might span multiple pages.
>
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] explain query plan?
Hello, In "index using and explain using question", Dennis Cote wrote: "An EXPLAIN QUERY PLAN returns three columns. The output of explain query plan is not documented (to the best of my knowledge anyway), but is fairly self explanatory. It shows the order that tables are scanned and which indexes, if any, are used to speed up the scans." http://www.mail-archive.com/sqlite-users@sqlite.org/msg24924.html That said, what's the meaning of the first two numeral columns in explain query plan? E.g.: 0|2|TABLE document WITH INDEX document_name ORDER BY 1|1|TABLE document_token WITH INDEX document_token_document_id 2|0|TABLE token USING PRIMARY KEY Or: 0|0|TABLE token WITH INDEX token_name 0|0|TABLE token WITH INDEX token_name 0|0|TABLE AS token 1|1|TABLE document_token WITH INDEX document_token_token_id 2|2|TABLE document USING PRIMARY KEY Thanks in advance. Kind regards, -- PA. http://alt.textdrive.com/nanoki/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] selecting uncommited rows
On Thu, Apr 17, 2008 at 3:15 PM, Alex Katebi <[EMAIL PROTECTED]> wrote:
> I will give a simple example:
>
> create table t1(name);
> insert into t1('Alex');
> begin;
> insert into t1 values ('Dennis');
> select * from t1;
>
> The above will show two rows. How can I see only the 'Dennis' row in this
> simple example.
>
Here's one possibility:
sqlite> CREATE TABLE t1(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);
sqlite> INSERT INTO t1(name) VALUES ('Alex');
sqlite> BEGIN;
sqlite> CREATE TEMPORARY TABLE start AS
...> SELECT COALESCE(MAX(id), 0) AS v FROM t1;
sqlite> INSERT INTO t1(name) VALUES ('Dennis');
sqlite> SELECT name FROM t1 WHERE id > (SELECT v FROM start);
name = Dennis
sqlite>
Derrell
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] selecting uncommited rows
I will give a simple example:
create table t1(name);
insert into t1('Alex');
begin;
insert into t1 values ('Dennis');
select * from t1;
The above will show two rows. How can I see only the 'Dennis' row in this
simple example.
On Thu, Apr 17, 2008 at 2:57 PM, Dennis Cote <[EMAIL PROTECTED]> wrote:
> Alex Katebi wrote:
> > Actually I am not interested on rows that have been committed. I am
> > interested on the rows that have been changed but not commited yet. As I
> > understand the triggers trigger of of a commit.
> > The example that you are refering to is for undoing the already commited
> > rows. I am merely interested in seeing the rows that are in my
> transaction
> > queue before the commit.
> >
>
> SQLite does not have a transaction queue.
>
> The data that you have changed is already stored in the database before
> you do the commit. The commit simply removes the information that would
> be used to do a rollback.
>
> The page I referred you to was an example of using triggers to track
> changes to tables. This is what you want to do if I understand you
> correctly.
>
> Use triggers to track the rows that are changed by your transaction's
> insert, update, and delete statements. Then use a select to display the
> current values (i.e. the value that will be committed) for these rows
> only.
>
> HTH
> Dennis Cote
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to select uncomitted rows?
On Apr 17, 2008, at 2:35 PM, Alex Katebi wrote: > Is there a way to select rows that have not been committed yet? > No. SQLite doesn't really commit rows. It commits pages. A single page might hold multiple rows, only some of which might have changed. Or a single row might span multiple pages. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] how to select uncomitted rows?
Is there a way to select rows that have not been committed yet? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] selecting uncommited rows
Actually I am not interested on rows that have been committed. I am interested on the rows that have been changed but not commited yet. As I understand the triggers trigger of of a commit. The example that you are refering to is for undoing the already commited rows. I am merely interested in seeing the rows that are in my transaction queue before the commit. On Thu, Apr 17, 2008 at 1:54 PM, Dennis Cote <[EMAIL PROTECTED]> wrote: > Alex Katebi wrote: > >Can you give an example on how to use this. Basically I want to see > > (select) only the uncommited rows. > > > > You will have to keep track of the rows that have been changed yourself. > > You can have SQLite do it for you if you create a change_log table and > then setup triggers to add the rowids of any rows modified during the > transaction. You clear this table at the beginning of your transaction. > The triggers will insert rows for each change to the table. At the end > of the transaction you can select all the rows from the main table that > have their rowids stored in the change_log table. > > If you want to get fancier you can look at this page for more ideas > http://www.sqlite.org/cvstrac/wiki?p=UndoRedo > > HTH > Dennis Cote > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] OUTER JOIN and PRIMARY KEY in 3.5.8
> Your work-around until I fix this is to say > > owners.owner_id = pets.owner_id > > instead if what you have. In other words, put the > table on the left side of the join before the equals > sign instead of after it. Good idea: Swapping the terms of the JOIN expression does seem to work. Thank you for this workaround, and for your help. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] selecting uncommited rows
Alex Katebi wrote: >Can you give an example on how to use this. Basically I want to see > (select) only the uncommited rows. > You will have to keep track of the rows that have been changed yourself. You can have SQLite do it for you if you create a change_log table and then setup triggers to add the rowids of any rows modified during the transaction. You clear this table at the beginning of your transaction. The triggers will insert rows for each change to the table. At the end of the transaction you can select all the rows from the main table that have their rowids stored in the change_log table. If you want to get fancier you can look at this page for more ideas http://www.sqlite.org/cvstrac/wiki?p=UndoRedo HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] OUTER JOIN and PRIMARY KEY in 3.5.8
D. Richard Hipp wrote: > > Likely this has to do with ticket #3015. > http://www.sqlite.org/cvstrac/tktview?tn=3015 > Yes, I agree. It seems like the fix may have been too broad. The problem with the query in that report was the use of an index on the left table in the join condition. In the OP's query SQLite is failing to use an index on the right table when it should. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] OUTER JOIN and PRIMARY KEY in 3.5.8
On Apr 17, 2008, at 12:04 PM, Eric Minbiole wrote: > I have been using SQLite for about a year now, and have been extremely > pleased. While testing 3.5.8, I noticed that a SELECT with LEFT OUTER > JOIN seemed to stop using an index, resorting to a (slow) full table > scan. A simple (contrived) example follows: > > CREATE TABLE pets ( > pet_id INTEGER PRIMARY KEY, > owner_id INTEGER, > name TEXT ); > > CREATE TABLE owners ( > owner_id INTEGER PRIMARY KEY, > name TEXT ); > > INSERT INTO owners (owner_id, name) VALUES (1, 'Steve'); > INSERT INTO pets (owner_id, name) VALUES (1, 'Fluffy'); > > EXPLAIN QUERY PLAN > SELECT pets.name, owners.name > FROM pets > LEFT OUTER JOIN owners > ON (pets.owner_id = owners.owner_id); Your work-around until I fix this is to say owners.owner_id = pets.owner_id instead if what you have. In other words, put the table on the left side of the join before the equals sign instead of after it. It shouldn't make any difference. SQLite should generate exactly the same code regardless of whether you say A=B or B=A. Clearly something is busted. It will be fixed soon. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] selecting uncommited rows
Martin, Can you give an example on how to use this. Basically I want to see (select) only the uncommited rows. Thanks! -Alex On Thu, Apr 17, 2008 at 11:58 AM, Ken <[EMAIL PROTECTED]> wrote: > I believe the pragma read_uncommitted only works for shared cache mode and > threads. > > You'll have to select the data from the same connection that created the > data. > > > > "Martin.Engelschalk" <[EMAIL PROTECTED]> wrote: Hi, > > there is a pragma: PRAGMA read_uncommitted = 1; > You can select the uncommitted data and show ist before commit. > > Have a look here: http://www.sqlite.org/pragma.html > > Martin > > Alex Katebi schrieb: > > Hi All, > > > > Let's say I start a transaction and do bunch of insertions etc. Before > my > > commit I like to show (select) what I have configured. > > How can I accompilish this? > > > > Thanks, > > -Alex > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > > * Codeswift GmbH * > Traunstr. 30 > A-5026 Salzburg-Aigen > Tel: +49 (0) 8662 / 494330 > Mob: +49 (0) 171 / 4487687 > Fax: +49 (0) 12120 / 204645 > [EMAIL PROTECTED] > www.codeswift.com / www.swiftcash.at > > Codeswift Professional IT Services GmbH > Firmenbuch-Nr. FN 202820s > UID-Nr. ATU 50576309 > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] OUTER JOIN and PRIMARY KEY in 3.5.8
On Apr 17, 2008, at 12:31 PM, Dennis Cote wrote: > Eric Minbiole wrote: >> >> However, I wanted to let others take a look, to see if the >> issue was with my query (quite possible), or with the new version. >> > > This is definitely an issue with the new version. It is doing a nested > table scan instead of using the index for the left join. > Likely this has to do with ticket #3015. http://www.sqlite.org/cvstrac/tktview?tn=3015 D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] OUTER JOIN and PRIMARY KEY in 3.5.8
Eric Minbiole wrote: > > However, I wanted to let others take a look, to see if the > issue was with my query (quite possible), or with the new version. > This is definitely an issue with the new version. It is doing a nested table scan instead of using the index for the left join. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] OUTER JOIN and PRIMARY KEY in 3.5.8
I have been using SQLite for about a year now, and have been extremely pleased. While testing 3.5.8, I noticed that a SELECT with LEFT OUTER JOIN seemed to stop using an index, resorting to a (slow) full table scan. A simple (contrived) example follows: CREATE TABLE pets ( pet_id INTEGER PRIMARY KEY, owner_id INTEGER, name TEXT ); CREATE TABLE owners ( owner_id INTEGER PRIMARY KEY, name TEXT ); INSERT INTO owners (owner_id, name) VALUES (1, 'Steve'); INSERT INTO pets (owner_id, name) VALUES (1, 'Fluffy'); EXPLAIN QUERY PLAN SELECT pets.name, owners.name FROM pets LEFT OUTER JOIN owners ON (pets.owner_id = owners.owner_id); With 3.5.7, SQLite seems to use the primary key: 0|0|TABLE pets 1|1|TABLE owners USING PRIMARY KEY However, 3.5.8 appears to do a full table scan: 0|0|TABLE pets 1|1|TABLE owners On my actual database, the query time jumped from a couple milliseconds to a few seconds. In the meantime, I can continue using the earlier version(s). However, I wanted to let others take a look, to see if the issue was with my query (quite possible), or with the new version. Thank you, Eric ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] selecting uncommited rows
I believe the pragma read_uncommitted only works for shared cache mode and threads. You'll have to select the data from the same connection that created the data. "Martin.Engelschalk" <[EMAIL PROTECTED]> wrote: Hi, there is a pragma: PRAGMA read_uncommitted = 1; You can select the uncommitted data and show ist before commit. Have a look here: http://www.sqlite.org/pragma.html Martin Alex Katebi schrieb: > Hi All, > > Let's say I start a transaction and do bunch of insertions etc. Before my > commit I like to show (select) what I have configured. > How can I accompilish this? > > Thanks, > -Alex > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- * Codeswift GmbH * Traunstr. 30 A-5026 Salzburg-Aigen Tel: +49 (0) 8662 / 494330 Mob: +49 (0) 171 / 4487687 Fax: +49 (0) 12120 / 204645 [EMAIL PROTECTED] www.codeswift.com / www.swiftcash.at Codeswift Professional IT Services GmbH Firmenbuch-Nr. FN 202820s UID-Nr. ATU 50576309 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] selecting uncommited rows
Hi, there is a pragma: PRAGMA read_uncommitted = 1; You can select the uncommitted data and show ist before commit. Have a look here: http://www.sqlite.org/pragma.html Martin Alex Katebi schrieb: > Hi All, > > Let's say I start a transaction and do bunch of insertions etc. Before my > commit I like to show (select) what I have configured. > How can I accompilish this? > > Thanks, > -Alex > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- * Codeswift GmbH * Traunstr. 30 A-5026 Salzburg-Aigen Tel: +49 (0) 8662 / 494330 Mob: +49 (0) 171 / 4487687 Fax: +49 (0) 12120 / 204645 [EMAIL PROTECTED] www.codeswift.com / www.swiftcash.at Codeswift Professional IT Services GmbH Firmenbuch-Nr. FN 202820s UID-Nr. ATU 50576309 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] selecting uncommited rows
Hi All, Let's say I start a transaction and do bunch of insertions etc. Before my commit I like to show (select) what I have configured. How can I accompilish this? Thanks, -Alex ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Where To Put SQLite3.exe or SQLite3.dll for Windows Version of PHP?
Robert L Cochran wrote: > Where exactly do I install the sqlite3.exe (or should I use sqlite3.dll) > file for Microsoft Windows such that the Windows version of PHP 5.x > will find it and make use of it? Is it sufficient to create a C:\Program > Files\SQLite3 folder and put the SQLite 3.5.8 module(s) there, then edit > my path variable to point to it? > I put both files in my C:\Windows\System32 directory. It is already on the path so you can run sqlite3.exe, and sqlite3.dll will be available to any program that links to the dll but doesn't supply a local copy. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transaction across threads
http://sqlite.org/lockingv3.html http://sqlite.org/sharedcache.html http://sqlite.org/34to35.html(section 5.0) I don't see a need to document this as its already done by the above. I think you've missed the finer points. See my comments embedded below: HTH, Ken Shailesh Birari <[EMAIL PROTECTED]> wrote: any clarifications on the below statements? -Shailesh > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Shailesh Birari > Sent: Wednesday, April 16, 2008 11:30 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Transaction across threads > > Thank you Ken, Hipp and Shawn. > Below I am trying to summarize sqlite in the three dimensions > of shared cache, transactions and threads. Please let me know > which of the following are correct. Ideally I would like to > put this list on the sqlite website for others. > > 1) With shared cache mode, multiple connections is same as a > single connection. So all facts to single connection in > following points apply to multiple connections with shared cache mode. Not really, Shared cache mode does not have anything to do with a single connection. Actually there would be no point in having a shared cache. > 2) With non shared cache mode, multiple connections are independent. > They are always multiple connections contending with each > other whether across threads or across processes. True. > 3) Sharing connection in non shared cache mode across threads > is same as each thread having independent connection in > shared cache mode. Not exactly correct. Sharing a connection is not the same as a shared connection. A shared connection across threads is only 1 connection, The application must mutex the connection to prevent concurrent access. So only one thread at a time may use the connection. The shared cache allows multiple threads to "share" memory resources. The do not share the connection. Sqlite handles internally mutexing access to the database and critical structures. Only one thread may have a transaction active. So only one may modify the cache. But many may read the cache but you may not read and write concurrently. > > Transaction > --- > Following points are when connection is shared across threads > or multiple connections are opened with shared cache mode enabled. > 1) If a connection in one thread does a BEGIN TRANSACTION and > another thread does a insert (using shared connection or > different connection with shared cache mode) then this insert > is strictly a part of the transaction. there is no way an > application can tell that this insert is not a part of the > transaction started by the first thread. So if the > application does not want this insert to be a part of the > transaction, it is upto the application to not do a insert if > a transaction is in progress. Shared cache mode is not the same as a shared connection. Basically the above is true, but only for a shared connection not shared cache! > 2) On the same lines, BEGIN TRANSACTION on the thread > followed by BEGIN TRANSACTION on another thread is as good as > nested transaction and will error. Similarly BEGIN > TRANSACTION on one thread can be committed by COMMIT > transaction on another thread. > Only one transaction may be active at a time. A second threads begin transactoin will fail with SQLITE_BUSY. If the connection is shared then the second thread may commit. > Following points apply when there are multiple independent > connections to the database which is essentially in > non-shared cache mode: > 1) one can begin multiple transaction across connections, but > they have to be "read" transactions. If it becomes a write > transaction, only one write transaction can be active. So > multiple "select" statements can be active but only one > "insert" statement will be active at any given point of time. > True. > > Processes Vs thread: > > 1) There is no way that one can share a connection across > processes using a non-shared cache mode. So each process will > have its own connection. True. > 2) With shared cache mode, multiple connections across > processes is as good a one connection and the all above rules > apply as they are. Not sure what the point is? Shared cache has nothing to do with sharing connections. It is a cache, not a connection. > Please let me know what all statements are correct. If they > are not correct try to rewrite them so that we can add them > to the wiki for version '3.5.?' > > Regards > Shailesh > > > > -Original Message- > > From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] On Behalf Of D. > Richard Hipp > > Sent: Tuesday, April 15, 2008 9:24 PM > > To: General Discussion of SQLite Database > > Subject: Re: [sqlite] Transaction across threads > > > > > > On Apr 15, 2008,
Re: [sqlite] Transaction across threads
any clarifications on the below statements? -Shailesh > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Shailesh Birari > Sent: Wednesday, April 16, 2008 11:30 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Transaction across threads > > Thank you Ken, Hipp and Shawn. > Below I am trying to summarize sqlite in the three dimensions > of shared cache, transactions and threads. Please let me know > which of the following are correct. Ideally I would like to > put this list on the sqlite website for others. > > 1) With shared cache mode, multiple connections is same as a > single connection. So all facts to single connection in > following points apply to multiple connections with shared cache mode. > 2) With non shared cache mode, multiple connections are independent. > They are always multiple connections contending with each > other whether across threads or across processes. > 3) Sharing connection in non shared cache mode across threads > is same as each thread having independent connection in > shared cache mode. > > Transaction > --- > Following points are when connection is shared across threads > or multiple connections are opened with shared cache mode enabled. > 1) If a connection in one thread does a BEGIN TRANSACTION and > another thread does a insert (using shared connection or > different connection with shared cache mode) then this insert > is strictly a part of the transaction. there is no way an > application can tell that this insert is not a part of the > transaction started by the first thread. So if the > application does not want this insert to be a part of the > transaction, it is upto the application to not do a insert if > a transaction is in progress. > 2) On the same lines, BEGIN TRANSACTION on the thread > followed by BEGIN TRANSACTION on another thread is as good as > nested transaction and will error. Similarly BEGIN > TRANSACTION on one thread can be committed by COMMIT > transaction on another thread. > > Following points apply when there are multiple independent > connections to the database which is essentially in > non-shared cache mode: > 1) one can begin multiple transaction across connections, but > they have to be "read" transactions. If it becomes a write > transaction, only one write transaction can be active. So > multiple "select" statements can be active but only one > "insert" statement will be active at any given point of time. > > > Processes Vs thread: > > 1) There is no way that one can share a connection across > processes using a non-shared cache mode. So each process will > have its own connection. > 2) With shared cache mode, multiple connections across > processes is as good a one connection and the all above rules > apply as they are. > > Please let me know what all statements are correct. If they > are not correct try to rewrite them so that we can add them > to the wiki for version '3.5.?' > > Regards > Shailesh > > > > -Original Message- > > From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] On Behalf Of D. > Richard Hipp > > Sent: Tuesday, April 15, 2008 9:24 PM > > To: General Discussion of SQLite Database > > Subject: Re: [sqlite] Transaction across threads > > > > > > On Apr 15, 2008, at 11:31 AM, Shawn Wilsher wrote: > > >> 1) If shared, then the second threads insert is part of the > > >> transaction and should succeed. > > >> 2) No. > > >> 3) If the connection is shared between threads, there can > > only be 1 > > >> txn at a time. The second threads attempt to begin a txn > > will result > > >> in an error that indicates a txn is already active. > > > To be clear, when using a shared cache and more than one sqlite3 > > > connection object, only one transaction will exist at a > > time, correct? > > > > Correct. > > > > > > > > However, if it is not using the shared cache, you can have a > > > transaction opened up for each thread? > > > > > > > Well, sort of. Certainly true if each connection has a different > > database open. But there can only be one write transaction > at a time > > to a single database. If you have multiple connections to the same > > database file, one can have a write transaction open and > one or more > > others can have a read transaction open, but you cannot have two or > > more write transactions active at once and all of the read > > transactions will need to close prior to the write transaction > > committing (otherwise the writer gets an > > SQLITE_BUSY.) > > > > D. Richard Hipp > > [EMAIL PROTECTED] > > > > > > > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org >
Re: [sqlite] speed for select statements
"Mahalakshmi.m" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I am using > First 10 --> "SELECT * FROM ARTIST ORDER BY ArtistName LIMIT 10 ;" > Next 10 ie., 11 to 20 --> "SELECT * FROM ARTIST WHERE ArtistName > ? > ORDER BY ArtistName LIMIT 10 ;" > Previous 10 -->"SELECT * FROM ARTIST WHERE ArtistName < ? ORDER BY > ArtistName DESC LIMIT ? ;" > > The above statements provides best performance .i have used < ,> > since I want the results to be in ORDER BY. > > If I don't want to use ORDER BY then how can I optimize without using > OFFSET. Why don't you want to use ORDER BY? What's wrong with it? If you don't want to sort by ArtistName, do the same with ArtistId. You'll get records roughly in the order of insertion. > For this querry "SELECT * FROM ARTIST ORDER BY ArtistName LIMIT 10 ;" > will it use ARTIST_idx or not. Run the query again, prepended with EXPLAIN QUERY PLAN. The output will tell you. My prediction is - yes, it should use the index. -- With best wishes, Igor Tandetnik With sufficient thrust, pigs fly just fine. However, this is not necessarily a good idea. It is hard to be sure where they are going to land, and it could be dangerous sitting under them as they fly overhead. -- RFC 1925 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] speed for select statements
MY Table is: "CREATE TABLE ARTIST(ArtistId INTEGER PRIMARY KEY,ArtistName TEXT NOT NULL COLLATE NOCASE, ArtistTrackCount INTEGER, UNIQUE(ArtistName));" "CREATE TABLE ALBUM(AlbumId INTEGER PRIMARY KEY,AlbumName TEXT NOT NULL COLLATE NOCASE,AlbumTrackCount INTEGER,UNIQUE(AlbumName));" "CREATE TABLE MUSIC(Id INTEGER PRIMARY KEY,Track TEXT NOT NULL,URL TEXT NOT NULL,Album_Id INTEGER,Artist_Id INTEGER);" "CREATE UNIQUE INDEX ARTIST_idx ON ARTIST (ArtistName);" "CREATE UNIQUE INDEX ALBUM_idx ON ALBUM (AlbumName);" "CREATE INDEX MUSIC_idx ON MUSIC (Track);" "CREATE INDEX MUSIC_ARTIST_idx ON MUSIC (Artist_Id);" "CREATE INDEX MUSIC_ALBUM_idx ON MUSIC (Album_Id);" "CREATE INDEX MUSIC_ARTIST_ALBUM_idx ON MUSIC (Artist_Id, Album_Id);" I am using First 10 --> "SELECT * FROM ARTIST ORDER BY ArtistName LIMIT 10 ;" Next 10 ie., 11 to 20 --> "SELECT * FROM ARTIST WHERE ArtistName > ? ORDER BY ArtistName LIMIT 10 ;" Previous 10 -->"SELECT * FROM ARTIST WHERE ArtistName < ? ORDER BY ArtistName DESC LIMIT ? ;" The above statements provides best performance .i have used < ,> since I want the results to be in ORDER BY. If I don't want to use ORDER BY then how can I optimize without using OFFSET. If I am using OFFSET then it more time.i am having 6 records. Is there any other way to optimize. Another doubt is : For this querry "SELECT * FROM ARTIST ORDER BY ArtistName LIMIT 10 ;" will it use ARTIST_idx or not. Thanks & Regards, Mahalakshmi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
