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<VALUE02. > >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<value02; and EXPLAIN QUERY PLAN SELECT * FROM Table01 WHERE value01<value02; to see what happens in de VDBE. An alternative would be to create AFTER INSERT and AFTER UPDATE triggers, which would store the result of the comparison in a fourth, indexed, column. -- ( Kees Nuyt ) c[_] ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
