Kristoffer Danielsson wrote: > All my tables have unique column names, so I tend to use "NATURAL JOIN" for > my joins (all join-columns are indexed). When I have multiple tables, I > sometimes get incredibly slow queries (not sure if they even terminate).
If all of your tables have unique column names, then a natural join would degenerate to a cartesian product, because there are no column pairs across different tables for which only rows having matching values are kept. A cartesian product would almost always be slow regardless of your JOIN syntax. I'll assume that you meant to say that all of your columns *except* the ones you are joining on, have unique column names, which makes more sense. Correct me if I'm wrong. > SELECT SUM(x) FROM t1 NATURAL JOIN t2 NATURAL JOIN t3 NATURAL JOIN t4 GROUP > BY x ORDER BY x; -- takes forever, whereas: > > SELECT SUM(x) FROM t2 NATURAL JOIN t3 NATURAL JOIN t4 NATURAL JOIN t1 GROUP > BY x ORDER BY x; -- takes one second Are all of those "x" supposed to be the same column? I don't think it makes sense to do all of [SUM(x), GROUP BY x, ORDER BY x] in the same query. If the query is supposed to have exactly 1 output row, counting the number of groups resulting from the GROUP BY, then the ORDER BY is useless, and makes the query slower (unless a smart optimizer eliminates it from ever running). If the query is supposed to have an output row for each distinct value of x from the GROUP BY, then SUM(x) would presumably be the same number as x for every row. Did you mean this?: SELECT foo, SUM(bar) FROM ... GROUP BY foo ORDER BY foo -- Darren Duncan _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
