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