Continuing with Sams thought process, Create another table that only contains record id's where value01<value02
Use triggers to maintain the table so when an record is inserted into table01 you would test if value01<value02 is true and insert the id into table01_a Deletes are trivial, just delete. Updates may either cause an insert or replace into table01_a or a delete in the case that the 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: [email protected] 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] -----------------------------------------------------------------------------
