Dennis Cote <[EMAIL PROTECTED]> wrote: > > > > Why full table scan? :/ > > SQLite can takes set (1) of rowid by ex(x) index for > > "X=5". Then takes another set (2) of rowid by ex(y) for "Y=7". > > Then SQLite need only to union this two set (1) and (2). > > Final SQLite should returns rows where rowid in (set1 union set2). > > > > > > > I think you mean intersection where you have used union. SQLite won't > optimize the query this way, but you can do it manually. > > Instead of > > select * from ex4 where x = 5 or y = 7; > > You can do this > > select * from ex4 where rowid in > ( > select rowid from ex4 where x = 5 > intersect > select rowid from ex4 where y = 7 > ); > > The intersect operation allows each of the sub-selects to be executed > using an independent index, and the outer select uses the implicit index > on the rowid. >
INTERSECT would give you x=5 AND y=7. For x=5 OR y=7 you want UNION. -- D. Richard Hipp <[EMAIL PROTECTED]> ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------