Re: [sqlite] Re: perfomance degradation for expr "foo = X or bar =X"

[bash]

On Wed, 9 May 2007 14:24:27 +0400
Tomash Brechko <[EMAIL PROTECTED]> wrote:

> On Wed, May 09, 2007 at 14:03:54 +0400, bash wrote:
> > Im simplify environment:
> > 
> > CREATE TABLE tbl(
> > id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
> > n1 int,
> > n2 int
> > );
> > CREATE INDEX idx1 on tbl(n1);
> > CREATE INDEX idx2 on tbl(n2);
> > 
> > sqlite> select count(*) from tbl;
> > 63026
> > 
> > 1 query:
> > SELECT id, n1, n2
> > FROM tbl
> > WHERE  n1 = $I OR n2 = $I
> > ORDER BY id DESC;
> > 
> > 2 query:
> >   SELECT id, n1, n2 
> >   FROM tbl
> >   WHERE  n1 = $I
> > UNION
> >   SELECT id, n1, n2
> >   FROM tbl
> >   WHERE  n2 = $I
> > ORDER BY id DESC;
> > 
> > Timing:
> > 1: 0.080 s.
> > 2: 0.000 s.
> 
> From http://www.sqlite.org/optoverview.html section 6.0:
> 
>   Each table in the FROM clause of a query can use at most one index...
> 
> So the first query can't benefit from both idx1 and idx2.  You may use
> 
>   EXPLAIN QUERY PLAN SELECT ...
> 
> to see what indexes will be used.

If i understand right from previous post by Peter there will be not
used any indexes because of "OR".

-- 
Biomechanical Artificial Sabotage Humanoid

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: perfomance degradation for expr "foo = X or bar =X"

[Tomash Brechko]

On Wed, May 09, 2007 at 14:03:54 +0400, bash wrote:
> Im simplify environment:
> 
> CREATE TABLE tbl(
>   id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
>   n1 int,
>   n2 int
> );
> CREATE INDEX idx1 on tbl(n1);
> CREATE INDEX idx2 on tbl(n2);
> 
> sqlite> select count(*) from tbl;
> 63026
> 
> 1 query:
> SELECT id, n1, n2
> FROM tbl
> WHERE  n1 = $I OR n2 = $I
> ORDER BY id DESC;
> 
> 2 query:
>   SELECT id, n1, n2 
>   FROM tbl
>   WHERE  n1 = $I
> UNION
>   SELECT id, n1, n2
>   FROM tbl
>   WHERE  n2 = $I
> ORDER BY id DESC;
> 
> Timing:
> 1: 0.080 s.
> 2: 0.000 s.

>From http://www.sqlite.org/optoverview.html section 6.0:

  Each table in the FROM clause of a query can use at most one index...

So the first query can't benefit from both idx1 and idx2.  You may use

  EXPLAIN QUERY PLAN SELECT ...

to see what indexes will be used.


-- 
   Tomash Brechko

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: perfomance degradation for expr "foo = X or bar =X"

[bash]

Im simplify environment:

CREATE TABLE tbl(
id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
n1 int,
n2 int
);
CREATE INDEX idx1 on tbl(n1);
CREATE INDEX idx2 on tbl(n2);

sqlite> select count(*) from tbl;
63026

1 query:
SELECT id, n1, n2
FROM tbl
WHERE  n1 = $I OR n2 = $I
ORDER BY id DESC;

2 query:
  SELECT id, n1, n2 
  FROM tbl
  WHERE  n1 = $I
UNION
  SELECT id, n1, n2
  FROM tbl
  WHERE  n2 = $I
ORDER BY id DESC;

Timing:
1: 0.080 s.
2: 0.000 s.

Still big degradation.

-- 
Biomechanical Artificial Sabotage Humanoid

-
To unsubscribe, send email to [EMAIL PROTECTED]
-