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 > > 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. > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
