On Wed, 9 May 2007 17:29:29 +0400 Tomash Brechko <[EMAIL PROTECTED]> wrote:
> > On Wed, May 09, 2007 at 16:32:34 +0400, bash wrote: > > SELECT * FROM ex1 WHERE x>'abc' AND y>'abc'; > > In this form only one indexes will be used, why not both? > > One index per table rule. At first glance it seems like SQLite could > use at least one index for "x=5 OR y=7" case too, but there is no > point in that, as the other part of the OR would require full table > scan anyway. 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). > And for the solution with the UNION, > > sqlite> explain query plan > ...> SELECT id, n1, n2 > ...> FROM tbl > ...> WHERE n1 = $I > ...> UNION > ...> SELECT id, n1, n2 > ...> FROM tbl > ...> WHERE n2 = $I > ...> ORDER BY id DESC; > 0|0|TABLE tbl WITH INDEX idx1 > 0|0|TABLE tbl WITH INDEX idx2 Yep... Im using now this construction. -- Biomechanical Artificial Sabotage Humanoid ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------