Some big sites do of course juggle performance vs in-database run-time
checks, but the statements as typically presented by MySQL partisans,
Live from the front :
This freshly created database has had to endure a multithreaded query
assault for about 2 hours.
It gave up.
TABLE `posts` (
`post_id` int(11) NOT NULL auto_increment,
`topic_id` int(11) NOT NULL,
etc...
mysql> SELECT max(post_id) FROM posts;
+--------------+
| max(post_id) |
+--------------+
| 591257 |
+--------------+
mysql> INSERT INTO posts (topic_id,post_text,user_id) VALUES (1,'DIE
BASTARD',666);
ERROR 1062 (23000): Duplicate entry '591257' for key 1
mysql> CHECK TABLE posts;
+-------------------+-------+----------+-----------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------------+-------+----------+-----------------------------+
| forum_bench.posts | check | warning | Table is marked as crashed |
| forum_bench.posts | check | error | Found 588137 keys of 588135 |
| forum_bench.posts | check | error | Corrupt |
+-------------------+-------+----------+-----------------------------+
mysql> REPAIR TABLE posts;
+-------------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------------+--------+----------+----------+
| forum_bench.posts | repair | status | OK |
+-------------------+--------+----------+----------+
mysql> INSERT INTO posts (topic_id,post_text,user_id) VALUES (1,'DIE
BASTARD',666);
Query OK, 1 row affected, 1 warning (0.10 sec)
mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------+
| Warning | 1364 | Field 'post_time' doesn't have a default value |
+---------+------+------------------------------------------------+
mysql> SELECT max(post_id) FROM posts;
+--------------+
| max(post_id) |
+--------------+
| 591257 |
+--------------+
mysql> SELECT count(*) FROM posts UNION ALL SELECT sum( topic_post_count )
FROM topics;
+----------+
| count(*) |
+----------+
| 588137 |
| 588145 |
+----------+
mysql> SELECT count(*) FROM topics WHERE topic_id NOT IN (SELECT topic_id
FROM posts);
+----------+
| count(*) |
+----------+
| 11583 |
+----------+
(Note : there cannot be a topic without a post in it, ha !)
Try Postgres :
forum_bench=> SELECT count(*) FROM posts UNION ALL SELECT
sum( topic_post_count ) FROM topics;
count
--------
536108
536108
(2 lignes)
forum_bench=> SELECT count(*) FROM topics WHERE topic_id NOT IN (SELECT
topic_id FROM posts);
count
-------
0
(1 ligne)
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
