Ah.. okay - you did not mention tables t1 and t2
were in different database files. That is likely why
this case was never tested. Explicit cross joins
are the way to go - you know the data better than
the database.
Read about SQLite's CROSS JOIN logic here:
http://www.sqlite.org/cvstrac/wiki?p=
Ah.. okay - you did not mention tables t1 and t2
were in different database files. That is likely why
this case was never tested. Explicit cross joins
are the way to go - you know the data better than
the database.
Read about SQLite's CROSS JOIN logic here:
http://www.sqlite.org/cvstrac/wiki?p=
Interesting... I googled cross join and got the following definition:
"A cross join (or Cartesian Product join) will return a result table where
each row from the first table is combined with each row from the second
table. The number of rows in the result table is the product of the number
of
CROSS JOIN is an SQLite-specific thing to disable
table join optimization.
Please post the schema and indexes of yours tables
so that this bug may be corrected when CROSS JOIN is not used.
--- Steve Green <[EMAIL PROTECTED]> wrote:
> Interestingly, using
>
> from t1
> cross join t2
>
>
Interestingly, using
from t1
cross join t2
fixes the problem; using this, causes indices from both tables to be used...
addr opcode p1 p2 p3
-- -- -- -
37OpenRead0 4
38
maybe "t2 cross join t1" makes more sense given the
sizes of the tables.
--- Joe Wilson <[EMAIL PROTECTED]> wrote:
> Hard to say what's the problem if you don't post the
> schema of the tables and the indexes, and provide some
> sample data.
>
> Perhaps there is a bug in the join optimizer.
>
Hard to say what's the problem if you don't post the
schema of the tables and the indexes, and provide some
sample data.
Perhaps there is a bug in the join optimizer.
Try using an explicit CROSS JOIN.
select t1.a, t1.b, t2.c, t2.d
from t1
cross join t2
where t1.x = t2.x and t1.a >
The performance is exactly the same after running analyze on both tables.
Steve
Joe Wilson wrote:
Run an ANALYZE statement on your database and your
queries will be fast once again.
Perhaps SQLite should fall back to the old non-optimized
join behavior when the sqlite_stat1 table is missing
Run an ANALYZE statement on your database and your
queries will be fast once again.
Perhaps SQLite should fall back to the old non-optimized
join behavior when the sqlite_stat1 table is missing.
This would cover 99% of the pre-SQLite3.2.3 legacy databases
out there where the queries have alread
Hi,
I currently have a v3.2.0 database that contains two tables that I regularly
query with a join, e.g.,
selectt1.a, t1.b, t2.c, t2.d
from t1
join t2
ont1.x = t2.x and t1.a >= 100 and t1.a < 200
group by t1.a, t1.b, t2.c, t2.d
Table t1 has an index on a
Table t2 has an i
10 matches
Mail list logo
