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.

Reply via email to