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