> 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?" Or do > their optimizers do a better job of finding ways to use indices in a > join?" Can somebody supply me with specific examples of joins that other > database engines do efficiently but that SQLite does slowly?" Acoording to SQLite wiki other databases do better job without indices: " Test 6: INNER JOIN without an index SELECT t1.a FROM t1 INNER JOIN t2 ON t1.b=t2.b; SQLite 3.3.3 (sync): 14.473 SQLite 3.3.3 (nosync): 14.445 SQLite 2.8.17 (sync): 47.776 SQLite 2.8.17 (nosync): 47.750 PostgreSQL 8.1.2: 0.176 MySQL 5.0.18 (sync): 3.421 MySQL 5.0.18 (nosync): 3.443 FirebirdSQL 1.5.2: 0.141 "
> Is join > efficiency really a frustration to many SQLite users? Generally not, however the behaviour could be more user friendly. The way is I use SQLite is probably not common becase I don't write queries - apllication's users write them. I also deal with quite large data. The biggest problem with the way joins work is with subqueries. If flattening cannot be done the query runs slow. For example I was told by an user that joins on views are really slow (on large data it means that doesn't work at all). The are other minor problems: 1. Creating indices on every (possibly very large) table makes database file much bigger that it would be if SQLite used temporary indices created before query is run. 2. Database users need to know how exaclty how SQLite work. That is not problem if programmer write queries, but can be a problem if database is used by a mathematician who doesn't really care about it and simply wants to do some calculations. ---------------------------------------------------------------------- Chcesz miec nawigacje GPS ? Zamow lub przedluz umowe na neostrade, a nawigacja bedzie Twoja. Kliknij na link po szczegoly! http://link.interia.pl/f219a _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
