Ah.. okay - you did not mention tables t1 and t2 were in different database files. That is likely why this case was never tested. Explicit cross joins are the way to go - you know the data better than the database.
Read about SQLite's CROSS JOIN logic here: http://www.sqlite.org/cvstrac/wiki?p=QueryPlans "Note that you must use the keyword CROSS in order to disable the table reordering optimization. INNER JOIN, NATURAL JOIN, JOIN, and other similar combinations work just like a comma join in that the optimizer is free to reorder tables as it sees fit. (Table reordering is also disabled on an outer join, but that is because outer joins are not associative or commutative. Reordering tables in outer joins changes the result.) The use of the CROSS keyword to disable the table reordering by the optimizer is a planned feature for SQLite 3.2.6." --- Steve Green <[EMAIL PROTECTED]> wrote: > Interesting... I googled cross join and got the following definition: > > "A cross join (or Cartesian Product join) will return a result table where > each row from the first table is combined with each row from the second > table. The number of rows in the result table is the product of the number > of rows in each table. If the tables involved are large, this join can > take a very long time." > > So this isn't SQLite's definition of a cross join? > > > The schema is follows: > > table1.schema > > ATTACH DATABASE './table1.db' AS t1 ; > > CREATE TABLE t1.table1 > ( > i_id INT4, > b_id INT4, > d_id INT4, > c_id INT2, > data_in REAL, > data_out REAL > ); > CREATE INDEX t1.ix_table1_b_id ON table1( b_id ); > > DETACH DATABASE t1 ; > > table2.schema > > ATTACH DATABASE './table2.db' AS t2 ; > > CREATE TABLE t2.table2 > ( > d_id INT4 PRIMARY KEY, > r_id INT2, > m_id INT2, > i TEXT, > ct TEXT, > cc TEXT, > type TEXT, > notes TEXT > ); > > DETACH DATABASE t2 ; > > The actual query is > > select t1.b_id, t1.c_id, t2.r_id, t2.m_id, > sum( t1.data_in ) as data_in, > sum( t1.data_out ) as data_out > from t1 > join t2 > on t2.d_id = t1.d_id and t1.b_id >= 100 and t1.b_id < 200 > group by t1.b_id, t1.c_id, t2.m_id, t2.r_id > > Is this enough information? > > Steve > > Joe Wilson wrote: > > > 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> > === message truncated === __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
