* 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
