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
---- -------------- ---------- ---------- ---------------------------------
<snip>
37 OpenRead 0 4
38 SetNumColumns 0 7
39 Integer 2 0
40 OpenRead 3 6 keyinfo(1,BINARY)
41 Integer 0 0
42 OpenRead 1 2
43 SetNumColumns 1 3
44 Integer 0 0
45 OpenRead 4 3 keyinfo(1,BINARY)
<snip>
However, using
from t2
cross join t1
does *not* cause both indices to be used.
As well, using
from t1, t2
which is suppossed to be the same as
from t1
cross join t2
does *not* cause both indices to be used.
This is all rather puzzling; I had also thought that using "join" was equivalent
to "cross join", but this could be ignorance on my behalf...
Steve
Joe Wilson 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.
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 >= 100 and t1.a < 200
group by t1.a, t1.b, t2.c, t2.d
--- Steve Green <[EMAIL PROTECTED]> wrote:
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.
This would cover 99% of the pre-SQLite3.2.3 legacy databases
out there where the queries have already been hand-tuned.
--- Steve Green <[EMAIL PROTECTED]> wrote:
Hi,
I currently have a v3.2.0 database that contains two tables that I regularly
query with a join, e.g.,
select t1.a, t1.b, t2.c, t2.d
from t1
join t2
on t1.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 index on x
I've created a new v3.3.4 database with the same schema and data. Table t1 has
about 150 million rows and t2 has about 3000 rows. When I run the query using
v3.2.0, it returns in about 0.1 seconds. When run using v3.3.4, it takes about
20 seconds. For completeness, I ran the same query using v3.2.8, v3.3.2, and
v3.3.3. v3.2.8 performed the same (roughly) as v3.2.0; v3.3.2 and v3.3.3
performed
the same as v3.3.4 (so, it appears that the change was made going into v3.3.x).
When I ran an explain on the query in both databases, the main difference I
noticed was in the opening of the database tables; in v3.2.0, it appears that
the indices of both tables are being used...
addr opcode p1 p2 p3
---- -------------- ---------- ---------- ---------------------------------
<snip>
37 OpenRead 1 4
38 SetNumColumns 1 7
39 Integer 0 0
40 OpenRead 3 6 keyinfo(1,BINARY)
41 Integer 2 0
42 OpenRead 0 2
43 SetNumColumns 0 8
44 Integer 2 0
45 OpenRead 4 3 keyinfo(1,BINARY)
46 Integer 3795633 0
<snip>
In v3.3.4, it appears that the index of only one of the tables is being used...
addr opcode p1 p2 p3
---- -------------- ---------- ---------- ---------------------------------
<snip>
37 OpenRead 0 2
38 SetNumColumns 0 3
39 Integer 0 0
40 OpenRead 1 4
41 SetNumColumns 1 7
42 Integer 0 0
43 OpenRead 3 6 keyinfo(1,BINARY)
44 Rewind 0 78
45 Integer 3795633 0
<snip>
It's quite possible that I'm misinterpreting the explain data as I'm fairly new
with it... However, it seems to me that this might explain the huge difference
in performance.
Has anyone else seen this type of performance difference between v3.2.x and
v3.3.x?
Any ideas on how we can improve the performance using v3.3.x (we'd really like
to
use the most current version of the database, but the performance difference
would
be a killer)?
Thanks,
Steve
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
Steve Green
SAVVIS
Transforming Information Technology SM
This message contains information which may be confidential and/or
privileged. Unless you are the intended recipient (or authorized
to receive for the intended recipient), you may not read, use,
copy or disclose to anyone the message or any information contained
in the message. If you have received the message in error, please
advise the sender by reply e-mail at [EMAIL PROTECTED] and
delete the message and any attachment(s) thereto without retaining
any copies.
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
Steve Green
SAVVIS
Transforming Information Technology SM
This message contains information which may be confidential and/or
privileged. Unless you are the intended recipient (or authorized
to receive for the intended recipient), you may not read, use,
copy or disclose to anyone the message or any information contained
in the message. If you have received the message in error, please
advise the sender by reply e-mail at [EMAIL PROTECTED] and
delete the message and any attachment(s) thereto without retaining
any copies.