On Wed, 9 May 2007 18:13:07 +0400 Tomash Brechko <[EMAIL PROTECTED]> wrote:
> On Wed, May 09, 2007 at 17:45:52 +0400, bash wrote: > > > 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). > > You should read it the following way: "SQLite can't use two indexes > per table, and using only one index is pointless, hence no index is > used at all". > > So your question is actually "why SQLite uses at most one index per > table?". My guess is that the benefits are out-weighted by the > implementation complexity. Oh... so this is implementation limitation. Im currently thinking about this table: CREATE TABLE map ( x int, y int, name char ); CREATE INDEX map_x ON map(x); CREATE INDEX map_y ON map(y); And query for it will be something like this (circle): SELECT name FROM map WHERE (point_x - x)^2 + (point_y -y)^2 < R^2; How SQLite will works? Is there any benefit in indexes? -- Biomechanical Artificial Sabotage Humanoid ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------