* D. Richard Hipp: > One of the criticisms of SQLite is that it is slow to do joins. That > is true if SQLite is unable to figure out how to use an index to speed > the join. I was under the impression that SQLite actually did a > fairly reasonable job of making use of indices, if they exist. But > without indices, an k-way join takes time proportional to N^k. > > Do other SQL database engines not have this same limitation? Are > MySQL and PostgreSQL and Firebird and MS-SQL and Oracle creating > phantom indices on-the-fly to help them do joins faster, for example?
PostgreSQL roughly does one of the following (when dealing with a two-way join): * If one side of the join is estimated to be a small set, PostgreSQL performs a sequential scan on it, hashes it, and joins the other table in a hash join. * If both sides are large, each side is sorted, and a merge join is performed. Things go horribly wrong if the estimates are off and the wrong plan is picked. There's also a nested loop join (which would be what SQLite does), but I haven't seen it in recent version. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users