MySQL's documentation states:
When combining LIMIT row_count with DISTINCT, MySQL stops as soon as it finds row_count unique rows.
The question was asked, how is this deterministic / accurate? My hypothesis is that it's due to MySQL's use of a clustering
index, as opposed to Postgres' heap storage. I think that, given a monotonic PK such as an AUTO_INCREMENT (or perhaps
any index), it's able to use that to guarantee determinism.
This table has 1,000,000 rows, consisting of a UUIDv4 PK, a random int of range (1,1000000), and ~1 KiB of Lorem Ipsum text.
postgres=# \d+ uuid_pk
Table "public.uuid_pk"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
id | uuid | | not null | | plain | | |
user_id | integer | | not null | | plain | | |
lorem | text | | not null | | extended | | |
| SET search_path TO 'example'; | |
| INSERT INTO genre (name) VALUES ('Rock'), ('Classic Rock'), ('Metal'), ('Progressive Metal'), ('Arena Rock'), ('Alternative Metal'), ('Grunge'); | |
| INSERT INTO category (name) VALUES ('Book'), ('Music'); | |
| INSERT INTO format (name) VALUES ('Compact Disc'), ('Vinyl'); | |
| INSERT INTO band (year_formed, name) VALUES (1985, 'Majesty'), (1988, 'Dream Theater'), (1990, 'Tool'), (1970, 'Queen'), (1987, 'Alice in Chains'); | |
| INSERT INTO artist (first_name, last_name) VALUES ('John', 'Petrucci'), ('John', 'Myung'), ('James', 'LaBrie'), ('Jordan', 'Ruddess'), ('Mike', 'Portnoy'), ('Mike', 'Mangini'); | |
| INSERT INTO artist (first_name, last_name, prefix, suffix) VALUES ('Brian', 'May', 'Sir', 'CBE'); |
| #!/usr/bin/env python3 | |
| # LICENSE | |
| # This Source Code Form is subject to the terms of the Mozilla Public License, v. 2.0. | |
| # If a copy of the MPL was not distributed with this file, You can obtain one at http://mozilla.org/MPL/2.0/. | |
| # Copyright 2024 Stephan Garland | |
| """ | |
| Calculates various parameters for the InnoDB buffer pool based on a simple input. |
| mysql> CREATE TABLE foo(id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, bar BINARY(16), baz CHAR(36)); | |
| Query OK, 0 rows affected (0.03 sec) | |
| mysql> INSERT INTO foo (bar, baz) VALUES (UUID_TO_BIN('dc23a9b9-a129-11ee-95fb-0242ac110000'), 'dc23a9b9-a129-11ee-95fb-0242ac110000'); | |
| Query OK, 1 row affected (0.02 sec) | |
| mysql> INSERT INTO foo (bar, baz) VALUES (UUID_TO_BIN('dc23a9b9-a129-11ee-95fb-0242ac110000'), 'dc23a9b9a12911ee95fb0242ac110000'); | |
| Query OK, 1 row affected (0.01 sec) | |
| mysql> SHOW BINARY LOGS; |
| import csv | |
| import json | |
| import os | |
| import pymysql | |
| import statistics | |
| import time | |
| def prepare_values(row): | |
| new_row = [] |
| #!/usr/bin/env bash | |
| function create_tables() { | |
| tbl_1=$(cat <<EOF | |
| CREATE TABLE IF NOT EXISTS test_innodb ( | |
| user_id CHAR(32) NOT NULL PRIMARY KEY, | |
| user_email VARCHAR(254) NOT NULL, | |
| created_at DATETIME NOT NULL, | |
| tags JSON NOT NULL DEFAULT ('{}'), | |
| shared_with JSON NOT NULL DEFAULT ('{}'), |