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
