You know. After a while you just get used to the quirks. Postgres has its own oddities. Granted they have a tendency to be by design, rather than glaring errors, but can still be annoying.
No. MySQL is able to optimize an anti-join but not the IN condition. It works about the same as having a LEFT JOIN with a not is null check in the where clause. MySQL will see NOT NOT IN as just IN.
+1 Very true, we should not read queries as math expressions. As I wrote there, the reason being is that mysql's query optimizer rewrites this query into EXISTS (SELECT 1) which will run for each row of the outer query.
The gist is that postgres replication, by design[1], can not go out of sync.
This is a major difference to MySQL replication, where silent divergence between Master/Slave is a common problem. You don't notice it until you run into spurious SQL errors or perform a manual table-checksum. In a worst case you don't notice it until that day when your master fails...
Except that in the specific example I have (this is my post), the results of the inner select (I would assume) should be cached because the condition in the inner select is a fixed set of criteria, not related at all to the outer records. Also of interest is that NOT IN(insert subselect here) doesn't raise the same issue. Additionally it prevents the entire query from using any indices... I have lots of interesting query planner results from this particular go around.
Also it's been fixed in 6.0-alpha just not back-ported, so I'm not sure I agree here.
i think i noticed this problem when i was writing a query two months ago.
back around 2001, I was reading Celko's "SQL for Smarties"... MySQL didn't have subselects then so I rewrote more than half of the subselect queries with joins... The funny thing is that I'd run my converted queries in Oracle and find they'd run faster in Oracle if i did them that way.
If you're looking to actually think relationally, that is, composing more complex queries from simpler components, as opposed to using only find-row-by-id types of queries (the kind that work equally well with a noSQL store like mongo anyway), you'd best use Postgresql, or if you like to spend money for no good reason an Oracle or SQL Server.
I don't think any .net shops are particularly happy with it - they know of no other tool.
My personal opinion: There are far less mentally and emotionally demanding things that do as good a job for zero outlay and don't suddenly steal your wallet whenever you try and scale them up.
Having said that, I've used it since SQL 6.5 and it supports small to medium workloads fine. It's also significantly less ugly and needy than Oracle.
I'd pick PostgreSQL if I was going solo on a project though.
SQL Server is an excellent product, tho' even I would not have gone 24/7 with it prior to 2005. Comparing the free version to MySQL it's no contest - SQL Server is faster, supports far richer SQL and has much, much better instrumentation for tuning and diagnosis.
And licenses for MySQL Enterprise aren't that cheap either.
30 comments
[ 3.3 ms ] story [ 33.9 ms ] threadI think every everyone goes through that particular mysql learning curve - use a join instead.
Same thing they were saying back in the '90s for foreign keys, transactions, stored procedures...
I have to do the same when I teach new developers and friends about the replication problems with PG.
When you say "replication problems" then you probably mean silent slave corruption?
I'm sorry but this is still a MySQL-only feature. The PostgreSQL-replication can't go out of sync, and worse, it can't even silently fail.
In this regard (and some others) PostgreSQL sadly lacks all the fun and excitement that can be had with MySQL.
This is a major difference to MySQL replication, where silent divergence between Master/Slave is a common problem. You don't notice it until you run into spurious SQL errors or perform a manual table-checksum. In a worst case you don't notice it until that day when your master fails...
[1] http://awurl.com/0g7Eshe5L#first_awesome_highlight
It's doing exactly what you told it to do.
The bug is a human bug of not understanding what you are asking even though it seems logical.
(I do know the answer - it's because they can do hash joins and MySQL can't).
Also it's been fixed in 6.0-alpha just not back-ported, so I'm not sure I agree here.
back around 2001, I was reading Celko's "SQL for Smarties"... MySQL didn't have subselects then so I rewrote more than half of the subselect queries with joins... The funny thing is that I'd run my converted queries in Oracle and find they'd run faster in Oracle if i did them that way.
Afaik a lot of .Net shops I know are fairly happy with it, to say the least.
My personal opinion: There are far less mentally and emotionally demanding things that do as good a job for zero outlay and don't suddenly steal your wallet whenever you try and scale them up.
Having said that, I've used it since SQL 6.5 and it supports small to medium workloads fine. It's also significantly less ugly and needy than Oracle.
I'd pick PostgreSQL if I was going solo on a project though.
Thanks for the rest of your feedback though :)
And licenses for MySQL Enterprise aren't that cheap either.