Re: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the index?
RB Smissaert wrote: I think an application that would produce all the needed indexes based on the table and all the possible queries would be helpful. Anybody done such an app? _All_ possible queries? Not practical for any significant number of columns. N factorial gets big fast. The indexes would be much larger than the data base itself. I'm afraid you are going to have to settle for doing an intelligent design of the data base. Gerry - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the index?
Ok, I guessed something like that were the case, but what I didn't get was the purpose of the logN, without knowing the base for that. So why not simply something like: if (N / m) > 2 then most likely index will be helpful. It sure is tricky to add these indexes as getting it wrong will seriously affect performance. Trouble in this particular case is that I am dealing with lots of different queries set by the user. I think an application that would produce all the needed indexes based on the table and all the possible queries would be helpful. Anybody done such an app? I think though that I am getting close now to having it all covered and thanks again for all the assistance. RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 04 August 2007 23:45 To: SQLite Subject: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the index? RB Smissaert <[EMAIL PROTECTED]> wrote: > But then if the base of the logarithm doesn't matter then > how is this equation going to help you? > > m==N/logN > > So, basically it comes down to some experimenting? Well, it tells you that if m is much smaller than N (say, by two orders of magnitude or more), it's a pretty safe bet that index will be useful. If m is close to N (say, within an order of magnitude), it is a pretty safe bet the index will be unhelpful. In the middle lies an area where it's more or less a wash. Most real world problems tend to fall into the two well-defined areas. If you find your particular problem to fall into the gray area, then yes, you might want to experiment. But in this case, even if you find that an index helps, it is unlikely to help by much, so any advantage may be outweighed by additional space requirements and slowdown on inserts and updates. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the index?
RB Smissaert <[EMAIL PROTECTED]> wrote: But then if the base of the logarithm doesn't matter then how is this equation going to help you? m==N/logN So, basically it comes down to some experimenting? Well, it tells you that if m is much smaller than N (say, by two orders of magnitude or more), it's a pretty safe bet that index will be useful. If m is close to N (say, within an order of magnitude), it is a pretty safe bet the index will be unhelpful. In the middle lies an area where it's more or less a wash. Most real world problems tend to fall into the two well-defined areas. If you find your particular problem to fall into the gray area, then yes, you might want to experiment. But in this case, even if you find that an index helps, it is unlikely to help by much, so any advantage may be outweighed by additional space requirements and slowdown on inserts and updates. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Weird error
--- Alain Bertrand <[EMAIL PROTECTED]> wrote: > In a Qt4 application, I have the following bit of code : > > QString s; > bool b; > QSqlQuery q; > QSqlError err; > s= "CREATE TABLE ttd_bdata (sheetId integer primary key,"; > s+="creation_date date, modif_date date,"; > s+="speciesId integer,USDA_zone integer,"; > s+="generalities text,propagation text,care text,"; > s+="esthetics text, phyto text,miscellaneous text)"; > //s="SELECT * FROM ttd_species WHERE speciesId=4"; > qWarning( s.toAscii() ); > b=q.exec( s ); > > Executing the query fails with the following information > > SQL logic error or missing database > Unable to fetch row > Err number 1. > > If the query is remplaced by the commented one, I got no error. If I open my > database file with sqliteman, the query is executed without any problem. > Some ideas ? I don't understand your description, but here are some random thoughts that might help you: CREATE statements do not return any rows. Your select table did not match the create statement table. Instead: Use "SELECT * FROM ttd_bdata WHERE speciesId=4" Don't use += in this case, as the following is more efficient: s = "CREATE TABLE ttd_bdata (sheetId integer primary key," "creation_date date, modif_date date," "speciesId integer,USDA_zone integer," "generalities text,propagation text,care text," "esthetics text, phyto text,miscellaneous text)"; Building a website is a piece of cake. Yahoo! Small Business gives you all the tools to get online. http://smallbusiness.yahoo.com/webhosting - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Weird error
Hi all In a Qt4 application, I have the following bit of code : QString s; bool b; QSqlQuery q; QSqlError err; s= "CREATE TABLE ttd_bdata (sheetId integer primary key,"; s+="creation_date date, modif_date date,"; s+="speciesId integer,USDA_zone integer,"; s+="generalities text,propagation text,care text,"; s+="esthetics text, phyto text,miscellaneous text)"; //s="SELECT * FROM ttd_species WHERE speciesId=4"; qWarning( s.toAscii() ); b=q.exec( s ); Executing the query fails with the following information SQL logic error or missing database Unable to fetch row Err number 1. If the query is remplaced by the commented one, I got no error. If I open my database file with sqliteman, the query is executed without any problem. Some ideas ? TIA Alain - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Re: Re: Re: Re: Re: How does SQLite choose the index?
But then if the base of the logarithm doesn't matter then how is this equation going to help you? m==N/logN So, basically it comes down to some experimenting? RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 04 August 2007 21:32 To: SQLite Subject: [sqlite] Re: Re: Re: Re: Re: Re: How does SQLite choose the index? RB Smissaert <[EMAIL PROTECTED]> wrote: > OK, will have a look at the wiki. > >> There's no "m" on the right hand side. >> m equals N divided by logarithm of N. > > What is the base of that logarithm then? Doesn't matter. All calulations shown are order of magnitude, only accurate modulo multiplication by some unknown constant. Choosing different base for the logarithm simply changes this constant. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Re: Re: Re: Re: How does SQLite choose the index?
RB Smissaert <[EMAIL PROTECTED]> wrote: OK, will have a look at the wiki. There's no "m" on the right hand side. m equals N divided by logarithm of N. What is the base of that logarithm then? Doesn't matter. All calulations shown are order of magnitude, only accurate modulo multiplication by some unknown constant. Choosing different base for the logarithm simply changes this constant. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: [2.8.x] Incrementing a column automatically with INSERT/UPDATE?
At 15:28 04/08/2007 -0400, Igor Tandetnik wrote: UPDATE mytable SET timestamp = old.timestamp + 1 WHERE rowid=new.rowid; Great :-) Thanks a lot. For those interested in doing the same thing: = create table mytable (id INTEGER AUTO PRIMARY KEY, name VARCHAR(50), timestamp INTEGER); CREATE TRIGGER update_timestamp UPDATE ON mytable BEGIN UPDATE mytable SET timestamp = old.timestamp + 1 WHERE rowid=new.rowid; END; insert into mytable values (NULL,'Marge',1); select * from mytable; update mytable set name='Homer' where id=1; select * from mytable; = Since I'm at it, I'll check if I can have SQLite put a timestamp equal to 1 when creating a new record so the user doesn't have to. Thanks everyone G. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Re: Re: Re: Re: How does SQLite choose the index?
OK, will have a look at the wiki. > There's no "m" on the right hand side. > m equals N divided by logarithm of N. What is the base of that logarithm then? RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 04 August 2007 21:03 To: SQLite Subject: [sqlite] Re: Re: Re: Re: Re: How does SQLite choose the index? RB Smissaert <[EMAIL PROTECTED]> wrote: > Thanks; I have seen this O(N) etc. explanations a lot, but not sure > what they exactly mean. http://en.wikipedia.org/wiki/Big_O_notation Roughly, we say that an algorithm has complexity O(N) (where N is the size of its input) when there exists some constant C such that the running time of an algorithm on this input is no more than C*N. >> and for each entry would perform a logN > > Does the logN here mean m log N or something else? Yes, logN is the same as log N or log(N) - a logarithm of N. >> m==N/logN > > Ditto, does this mean break even point roughly when m equals N / (m > log N) ? There's no "m" on the right hand side. m equals N divided by logarithm of N. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Re: Re: Re: How does SQLite choose the index?
RB Smissaert <[EMAIL PROTECTED]> wrote: Thanks; I have seen this O(N) etc. explanations a lot, but not sure what they exactly mean. http://en.wikipedia.org/wiki/Big_O_notation Roughly, we say that an algorithm has complexity O(N) (where N is the size of its input) when there exists some constant C such that the running time of an algorithm on this input is no more than C*N. and for each entry would perform a logN Does the logN here mean m log N or something else? Yes, logN is the same as log N or log(N) - a logarithm of N. m==N/logN Ditto, does this mean break even point roughly when m equals N / (m log N) ? There's no "m" on the right hand side. m equals N divided by logarithm of N. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Re: Re: Re: How does SQLite choose the index?
Thanks; I have seen this O(N) etc. explanations a lot, but not sure what they exactly mean. Does it in this case simply mean O * N and O * (m log N) ? > and for each entry would perform a logN Does the logN here mean m log N or something else? > m==N/logN Ditto, does this mean break even point roughly when m equals N / (m log N) ? Sorry, these might be basic questions, but would like to get this clear. RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 04 August 2007 20:01 To: SQLite Subject: [sqlite] Re: Re: Re: Re: How does SQLite choose the index? RB Smissaert <[EMAIL PROTECTED]> wrote: > One thing I am not sure about yet is when an index would be helpful > in the > first place in relation to the data in the field. > I understand an index is going to help little if the values in a > particular > field can only for example be 1 or 0, but roughly when does it become > useful > to add an index? Suppose you have a table with N records. You run a query like "select * from t where f='x'; " which selects m records. Without an index on t(f), the query would run in O(N) time. With the index, it would be O(m log N) (it will scan m entries in the index, and for each entry would perform a logN lookup in the main table, by rowid). Thus, when m is close to N (that is, the query selects almost all records), an index actually performs worse than a linear scan. The break-even point is somewhere on the order m==N/logN. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Select, update on the row, and step leads to crash
It should be possible to do what you're trying to do as of 3.3.8. See the Tcl examples at the bottom of this patch: Allows UPDATE, INSERT, and DELETEs to occur while a SELECT is pending on the same table. http://www.sqlite.org/cvstrac/chngview?cn=3355 Post a complete self-contained C program demonstrating the problem if you need further help. --- karthikeyan <[EMAIL PROTECTED]> wrote: > What we are doing in short is below (using Sqlite 3.3.13). > > Select id, data from msgTable;// ok - returns 100 > tid = sqlite3_column_int; > data = sqlite3_column_bytes; > // make a copy of the bytes > // update the byte values > update msgTable set data=new data where id=tid; //ok - returns 101 > sqlite3_step// not ok > > Sqlite3_step, crashes because (BtCursor *pCur)->pPage is null in > sqlite3BtreeNext. > > I read somewhere that its possible to update while we are in a select. Is > this possible? > Or are there some other api call, which I need to call before sqlite3_step > is called?. > > Both the statements are prepared using sqlite3_prepare_v2 and values are > assigned using the bind functions. Need a vacation? Get great deals to amazing places on Yahoo! Travel. http://travel.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: [2.8.x] Incrementing a column automatically with INSERT/UPDATE?
Gilles Ganault wrote: So, I guess the solution is to write a trigger. Problem is, I've never used triggers before, so I don't really know how to get around to fetching the previous value of a timestamp column and increment it whenever a record is updated: CREATE TRIGGER update_timestamp UPDATE ON mytable BEGIN UPDATE mytable SET timestamp = old.timestamp + 1; END; UPDATE mytable SET timestamp = old.timestamp + 1 WHERE rowid=new.rowid; Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] [2.8.x] Incrementing a column automatically with INSERT/UPDATE?
At 13:05 04/08/2007 +, [EMAIL PROTECTED] wrote: 2.8.x support triggers. Good to know :-) So I don't have to upgrade everyone to 3.x then. I did try to create a self-incrementing timestamp column with 2.8.x but it doesn't seem to be handle more than one such column: C:\sqlite>sqlite database.db SQLite version 2.8.13 Enter ".help" for instructions sqlite> create table mytable (id INTEGER AUTO PRIMARY KEY, name VARCHAR(50), timestamp INTEGER AUTO); sqlite> insert into mytable values (NULL,'Bart',NULL); sqlite> select * from mytable; 1|Bart| So, I guess the solution is to write a trigger. Problem is, I've never used triggers before, so I don't really know how to get around to fetching the previous value of a timestamp column and increment it whenever a record is updated: CREATE TRIGGER update_timestamp UPDATE ON mytable BEGIN UPDATE mytable SET timestamp = old.timestamp + 1; END; Thanks for any tip G. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Re: Re: How does SQLite choose the index?
RB Smissaert <[EMAIL PROTECTED]> wrote: One thing I am not sure about yet is when an index would be helpful in the first place in relation to the data in the field. I understand an index is going to help little if the values in a particular field can only for example be 1 or 0, but roughly when does it become useful to add an index? Suppose you have a table with N records. You run a query like "select * from t where f='x'; " which selects m records. Without an index on t(f), the query would run in O(N) time. With the index, it would be O(m log N) (it will scan m entries in the index, and for each entry would perform a logN lookup in the main table, by rowid). Thus, when m is close to N (that is, the query selects almost all records), an index actually performs worse than a linear scan. The break-even point is somewhere on the order m==N/logN. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Re: How does SQLite choose the index?
RB Smissaert <[EMAIL PROTECTED]> wrote: How does the field order in indexes work with joins? So for example given the query: select t1.a, t1.b, t2.c from table1 t1 inner join table2 t2 on (t1.id1 = t2.id2) where t1.a = 'abc' would the index need to be (a, id1) or (id1, a) Doesn't matter. SQLite internally converts the original query to something like select t1.a, t1.b, t2.c from table1 t1, table2 t2 where t1.id1 = t2.id2 and t1.a = 'abc'; It then knows that the two operands of the AND can be checked in any order, so it could use either index. If, on the other hand, the last condition were t1.a >= 'abc', then an index on (id1, a) could be used to satisfy both conditions, but an index on (a, id1) only works for inequality but doesn't help with t1.id1=t2.id2 Does the field order in the tables have anything to do with this No. or is it just the field order in the query The field order in the query doesn't matter much, either. SQLite is smart enough to rearrange the checks in a variety of ways. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Re: How does SQLite choose the index?
How does the field order in indexes work with joins? So for example given the query: select t1.a, t1.b, t2.c from table1 t1 inner join table2 t2 on (t1.id1 = t2.id2) where t1.a = 'abc' would the index need to be (a, id1) or (id1, a) Does the field order in the tables have anything to do with this or is it just the field order in the query and field order in the index that matter? RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 04 August 2007 15:49 To: SQLite Subject: [sqlite] Re: Re: How does SQLite choose the index? RB Smissaert <[EMAIL PROTECTED]> wrote: > So, basically it is best to make one large index (apart from the > primary > integer key?) that includes all fields that could be in a WHERE > clause or a > JOIN or a GROUP BY or a HAVING or an ORDER BY? That depends on the queries you want to speed up. Index columns can only be used from left to right, with no skips, to satisfy the conditions of the query. For example, if you have an index on columns (a, b, c) and a query like select * from t where a='x' and c='z'; then the index can be used to satisfy a='x' condition, but then a linear check of all records having a='x' is performed to satisfy c='z'. If you run such a query often and need it to run fast, you may want another index on (a, c), or perhaps (a, c, b). Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Re: How does SQLite choose the index?
> Index columns can only be used from left to right, with no skips, > to satisfy the conditions of the query. Ah, yes, I forgot about that one. So, I will need some more indexes. RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 04 August 2007 15:49 To: SQLite Subject: [sqlite] Re: Re: How does SQLite choose the index? RB Smissaert <[EMAIL PROTECTED]> wrote: > So, basically it is best to make one large index (apart from the > primary > integer key?) that includes all fields that could be in a WHERE > clause or a > JOIN or a GROUP BY or a HAVING or an ORDER BY? That depends on the queries you want to speed up. Index columns can only be used from left to right, with no skips, to satisfy the conditions of the query. For example, if you have an index on columns (a, b, c) and a query like select * from t where a='x' and c='z'; then the index can be used to satisfy a='x' condition, but then a linear check of all records having a='x' is performed to satisfy c='z'. If you run such a query often and need it to run fast, you may want another index on (a, c), or perhaps (a, c, b). Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: How does SQLite choose the index?
RB Smissaert <[EMAIL PROTECTED]> wrote: So, basically it is best to make one large index (apart from the primary integer key?) that includes all fields that could be in a WHERE clause or a JOIN or a GROUP BY or a HAVING or an ORDER BY? That depends on the queries you want to speed up. Index columns can only be used from left to right, with no skips, to satisfy the conditions of the query. For example, if you have an index on columns (a, b, c) and a query like select * from t where a='x' and c='z'; then the index can be used to satisfy a='x' condition, but then a linear check of all records having a='x' is performed to satisfy c='z'. If you run such a query often and need it to run fast, you may want another index on (a, c), or perhaps (a, c, b). Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Select, update on the row, and step leads to crash
Hi, What we are doing in short is below (using Sqlite 3.3.13). Select id, data from msgTable; // ok - returns 100 tid = sqlite3_column_int; data = sqlite3_column_bytes; // make a copy of the bytes // update the byte values update msgTable set data=new data where id=tid; //ok - returns 101 sqlite3_step// not ok Sqlite3_step, crashes because (BtCursor *pCur)->pPage is null in sqlite3BtreeNext. I read somewhere that its possible to update while we are in a select. Is this possible? Or are there some other api call, which I need to call before sqlite3_step is called?. Both the statements are prepared using sqlite3_prepare_v2 and values are assigned using the bind functions. Thanks karthik This e-mail and attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient's) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: How does SQLite choose the index?
Thanks, that was very useful. I didn't realize that table values could be obtained from the index. I suppose it makes sense when you think about it. So, basically it is best to make one large index (apart from the primary integer key?) that includes all fields that could be in a WHERE clause or a JOIN or a GROUP BY or a HAVING or an ORDER BY? RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 04 August 2007 15:18 To: SQLite Subject: [sqlite] Re: How does SQLite choose the index? RB Smissaert <[EMAIL PROTECTED]> wrote: > I get this query plan (explain query plan): > > order from detail > > 0 0 TABLE ENTRY AS E WITH INDEX > IDX16$ENTRY$READ_CODE$ADDED_DATE$START_DATE$PROBLEM_ID > > My question is why doesn't it pick the index: > IDX3$ENTRY$READ_CODE > > Not sure, but I would think that is more effective. It's exactly the same in terms of efficiency. In fact, it is completely pointless to have two indexes where the column list of one is a strict prefix of the column list of another. The latter can be used, equally efficiently, everywhere the former can be used. In some cases the latter may even be more efficient. Consider: create table t (a text, b text); create index ta on t(a); create index tab on t(a, b); select a, b from t where a='xyz'; If SQLite chooses to use index ta, then it needs to perform a lookup in the table (by rowid) to retrieve the value of b. But if it uses index tab, then the value of b is stored in the index, and the table itself doesn't need to be consulted at all. So fewer pages to read from disk. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: How does SQLite choose the index?
RB Smissaert <[EMAIL PROTECTED]> wrote: I get this query plan (explain query plan): order from detail 0 0 TABLE ENTRY AS E WITH INDEX IDX16$ENTRY$READ_CODE$ADDED_DATE$START_DATE$PROBLEM_ID My question is why doesn't it pick the index: IDX3$ENTRY$READ_CODE Not sure, but I would think that is more effective. It's exactly the same in terms of efficiency. In fact, it is completely pointless to have two indexes where the column list of one is a strict prefix of the column list of another. The latter can be used, equally efficiently, everywhere the former can be used. In some cases the latter may even be more efficient. Consider: create table t (a text, b text); create index ta on t(a); create index tab on t(a, b); select a, b from t where a='xyz'; If SQLite chooses to use index ta, then it needs to perform a lookup in the table (by rowid) to retrieve the value of b. But if it uses index tab, then the value of b is stored in the index, and the table itself doesn't need to be consulted at all. So fewer pages to read from disk. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] GIS with spatial routing
Hallo all, i read a lot about the plans of creating an spatial index for gis applications. This would be very interessting for my, because i am writing a diploma thesis on developement an routing-algorithm on an mobile client with SQLite and the data of openstreetmap. there for i am collection some information on performance to decide if this is possible or not. Maybe someone of you has some experience with GIS on SQlite regards Sven - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] How does SQLite choose the index?
Given this table: CREATE TABLE AMorb37F6_E ([PATIENT_ID] INTEGER, [ENTRY_ID] INTEGER PRIMARY KEY, [READ_CODE] TEXT, [ADDED_DATE] TEXT, [START_DATE] TEXT) And these indexes: IDX10$ENTRY$PATIENT_ID IDX11$ENTRY$TERM_TEXT IDX12$ENTRY$READ_CODE$ADDED_DATE IDX13$ENTRY$READ_CODE$START_DATE IDX14$ENTRY$READ_CODE$PROBLEM_ID IDX15$ENTRY$READ_CODE$ADDED_DATE$PROBLEM_ID IDX16$ENTRY$READ_CODE$ADDED_DATE$START_DATE$PROBLEM_ID IDX2$ENTRY$ADDED_BY IDX3$ENTRY$READ_CODE IDX4$ENTRY$ENCOUNTER_ID IDX5$ENTRY$ADDED_DATE IDX6$ENTRY$UPDATED_DATE IDX7$ENTRY$START_DATE IDX8$ENTRY$PROBLEM_ID IDX9$ENTRY$ENTRY_FLAGS And this query: SELECT E.PATIENT_ID, E.ENTRY_ID, E.READ_CODE, E.ADDED_DATE, E.START_DATE FROM ENTRY E WHERE E.READ_CODE GLOB 'G2*' AND (NOT E.DORMANT_FLAG = 1) ORDER BY E.PATIENT_ID ASC, E.ENTRY_ID ASC I get this query plan (explain query plan): order fromdetail 0 0 TABLE ENTRY AS E WITH INDEX IDX16$ENTRY$READ_CODE$ADDED_DATE$START_DATE$PROBLEM_ID My question is why doesn't it pick the index: IDX3$ENTRY$READ_CODE Not sure, but I would think that is more effective. What are the general rules as to how SQLites picks from the available indexes? RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] [2.8.x] Incrementing a column automatically with INSERT/UPDATE?
Gilles Ganault <[EMAIL PROTECTED]> wrote: > Hello > > I was wondering if SQLite 2.8.x was able to update a column > automatically > when performing either INSERT or UPDATE, or if this can only be done with > triggers in 3.x? > 2.8.x support triggers. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] [2.8.x] Incrementing a column automatically with INSERT/UPDATE?
Hello I was wondering if SQLite 2.8.x was able to update a column automatically when performing either INSERT or UPDATE, or if this can only be done with triggers in 3.x? For instance, when adding a record, I need to have a counter in the third column be incremented by one: insert into mytable values (NULL,'bart',counter= counter+1) Thank you G. - To unsubscribe, send email to [EMAIL PROTECTED] -
