Well, I told the author of SQLite that a double inner join took forever to complete. He just told me I was wrong, but this inner join worked fine on PostGres, MySql and so on.
It annoys me, because neither PostGres not MySql are a binary file you can just run with a query, unlike SQLite which is very convenient for embedding in a desktop app.
SQLite does not have very many execution methods or a very intelligent optimizer, and every database has different access methods available to it to some extent. For example, Postgres lacks skip scan, which Oracle has. MySQL lacks bitmap index scans to combine indexes, which Postgres and Oracle have. Postgres (until the recently released 9.2) lacked index-only scan, which MySQL and Oracle (and DB2, and Informix, and SQL server...that was a feature long in coming) have. MySQL of many versions lacked hash joins (which blows my mind), it may have it now (depends how you count the forks).
So, all in all...somewhat expected. I have a number of queries that simply cannot run in their most naive rendering on Postgres due to lack of skip scan.
Comments
Well, I told the author of SQLite that a double inner join took forever to complete. He just told me I was wrong, but this inner join worked fine on PostGres, MySql and so on.
It annoys me, because neither PostGres not MySql are a binary file you can just run with a query, unlike SQLite which is very convenient for embedding in a desktop app.
SQLite does not have very many execution methods or a very intelligent optimizer, and every database has different access methods available to it to some extent. For example, Postgres lacks skip scan, which Oracle has. MySQL lacks bitmap index scans to combine indexes, which Postgres and Oracle have. Postgres (until the recently released 9.2) lacked index-only scan, which MySQL and Oracle (and DB2, and Informix, and SQL server...that was a feature long in coming) have. MySQL of many versions lacked hash joins (which blows my mind), it may have it now (depends how you count the forks).
So, all in all...somewhat expected. I have a number of queries that simply cannot run in their most naive rendering on Postgres due to lack of skip scan.
In case anyone wants to read more about skip scans, here's the link: http://docs.oracle.com/cd/B10501_01/server.920/a96533/optimo...
Mysql has an embedded version. It may not boil down to a single file on the HD, but I don't see that as a big drawback.
http://dev.mysql.com/doc/refman/5.1/en/libmysqld.html