Re: [sqlite] suggestion for an optimized sql
It may be the case where the index will cause more harm than good in this instance depending upon the percentage matching. In other words if only 10% of the records match using the index great... But if say 90% match the index this would be Very Bad and A Full scan would be quicker. I full table scan is not always a bad thing. It just depends upon the percentage of rows you need returned. Clodo <[EMAIL PROTECTED]> wrote: Thanks to all great people that help me. I will create a specific field with an index on that. Bye! > You could create a field in the table Value01LessThanValue02 and use a > trigger to update this value whenever data is updated. Then you can search > on just this one field. However, it's a boolean result so depending on the > percentage of records that match this condition, the index may not be that > helpful in the end anyways. > > HTH, > > Sam > > --- > We're Hiring! Seeking a passionate developer to join our team building Flex > based products. Position is in the Washington D.C. metro area. If interested > contact [EMAIL PROTECTED] > > -Original Message- > From: Clodo [mailto:[EMAIL PROTECTED] > Sent: Thursday, December 20, 2007 5:36 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] suggestion for an optimized sql > > Hi, i have a table like this with thousand of records: > > CREATE TABLE Table01 ( > id integer, > value01 text, > value02 text > ); > > I need to optimize the following sql: > > SELECT * FROM Table01 WHERE VALUE01 > > How i can use indexes to avoid a full-table scan? Thanks! > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] suggestion for an optimized sql
Thanks to all great people that help me. I will create a specific field with an index on that. Bye! You could create a field in the table Value01LessThanValue02 and use a trigger to update this value whenever data is updated. Then you can search on just this one field. However, it's a boolean result so depending on the percentage of records that match this condition, the index may not be that helpful in the end anyways. HTH, Sam --- We're Hiring! Seeking a passionate developer to join our team building Flex based products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Clodo [mailto:[EMAIL PROTECTED] Sent: Thursday, December 20, 2007 5:36 AM To: sqlite-users@sqlite.org Subject: [sqlite] suggestion for an optimized sql Hi, i have a table like this with thousand of records: CREATE TABLE Table01 ( id integer, value01 text, value02 text ); I need to optimize the following sql: SELECT * FROM Table01 WHERE VALUE01 - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] suggestion for an optimized sql
Continuing with Sams thought process, Create another table that only contains record id's where value01=value02 Then just query table01_a The use of an index is probably not going to help and your better off most likely performing the full table scan especially if you are getting a significant chunk of the table and the records more than likely have an even distribution. "Samuel R. Neff" <[EMAIL PROTECTED]> wrote: You could create a field in the table Value01LessThanValue02 and use a trigger to update this value whenever data is updated. Then you can search on just this one field. However, it's a boolean result so depending on the percentage of records that match this condition, the index may not be that helpful in the end anyways. HTH, Sam --- We're Hiring! Seeking a passionate developer to join our team building Flex based products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Clodo [mailto:[EMAIL PROTECTED] Sent: Thursday, December 20, 2007 5:36 AM To: sqlite-users@sqlite.org Subject: [sqlite] suggestion for an optimized sql Hi, i have a table like this with thousand of records: CREATE TABLE Table01 ( id integer, value01 text, value02 text ); I need to optimize the following sql: SELECT * FROM Table01 WHERE VALUE01 How i can use indexes to avoid a full-table scan? Thanks! - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] suggestion for an optimized sql
You could create a field in the table Value01LessThanValue02 and use a trigger to update this value whenever data is updated. Then you can search on just this one field. However, it's a boolean result so depending on the percentage of records that match this condition, the index may not be that helpful in the end anyways. HTH, Sam --- We're Hiring! Seeking a passionate developer to join our team building Flex based products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Clodo [mailto:[EMAIL PROTECTED] Sent: Thursday, December 20, 2007 5:36 AM To: sqlite-users@sqlite.org Subject: [sqlite] suggestion for an optimized sql Hi, i have a table like this with thousand of records: CREATE TABLE Table01 ( id integer, value01 text, value02 text ); I need to optimize the following sql: SELECT * FROM Table01 WHERE VALUE01
Re: [sqlite] suggestion for an optimized sql
On Thu, 20 Dec 2007 11:36:29 +0100, Clodo <[EMAIL PROTECTED]> wrote: >Hi, i have a table like this with thousand of records: > >CREATE TABLE Table01 ( > id integer, > value01 text, > value02 text >); > >I need to optimize the following sql: > >SELECT * FROM Table01 WHERE VALUE01 >How i can use indexes to avoid a full-table scan? Thanks! You could create an index on (value01) or on (value01,value02), but it probably wouldn't help at all. Being TEXT columns, the index could potentially be large. A full table scan might be replaced by a full index scan. I don't think it would be any faster. I would say: try it and compare the results. Use EXPLAIN SELECT * FROM Table01 WHERE value01
