概要
MySQLのLIMITを使ったクエリでは、ORDER BYの結果が変わるというものがある。
https://dev.mysql.com/doc/refman/8.0/ja/limit-optimization.html
それについて手元で検証した。
使用する環境
MySQL8.0のDocker。ただし、MySQL5.7でも同様の事象が確認できると思われる。
FROM --platform=linux/x86_64 mysql:8-debian
ENV LANG ja_JP.UTF-8
ENV LANGUAGE ja_JP:ja
ENV LC_ALL ja_JP.UTF-8
ENV MYSQL_ALLOW_EMPTY_PASSWORD=yes
RUN useradd mysqld
COPY mysqld_charset.cnf /etc/mysql/conf.d/mysqld_charset.cnf
ADD world-db.tar.gz /docker-entrypoint-initdb.d/
CMD ["mysqld"]
テストデータ
下記のようなテストデータを作成した。
mysql> select * from item order by created_at limit 20;
+----+------+---------------------+
| id | name | created_at |
+----+------+---------------------+
| 68 | 0 | 2023-12-20 18:00:00 |
| 64 | 0 | 2023-12-20 18:00:00 |
| 65 | 0 | 2023-12-20 18:00:00 |
| 66 | 0 | 2023-12-20 18:00:00 |
| 67 | 0 | 2023-12-20 18:00:00 |
| 60 | 0 | 2023-12-20 19:00:00 |
| 63 | 0 | 2023-12-20 19:00:00 |
| 62 | 0 | 2023-12-20 19:00:00 |
| 61 | 0 | 2023-12-20 19:00:00 |
| 59 | 0 | 2023-12-20 19:00:00 |
| 52 | 0 | 2023-12-20 20:00:00 |
| 46 | 0 | 2023-12-20 20:00:00 |
| 44 | 0 | 2023-12-20 20:00:00 |
| 47 | 0 | 2023-12-20 20:00:00 |
| 53 | 0 | 2023-12-20 20:00:00 |
| 43 | 0 | 2023-12-20 20:00:00 |
| 45 | 0 | 2023-12-20 20:00:00 |
| 51 | 0 | 2023-12-20 20:00:00 |
| 49 | 0 | 2023-12-20 20:00:00 |
| 33 | 0 | 2023-12-20 20:00:00 |
+----+------+---------------------+
20 rows in set (0.01 sec)
検証
まず、LIMIT句を付与せずにcreated_at
でORDER BY
をする
mysql> select * from item order by created_at;
+----+------+---------------------+
| id | name | created_at |
+----+------+---------------------+
| 68 | 0 | 2023-12-20 18:00:00 |
| 67 | 0 | 2023-12-20 18:00:00 |
| 66 | 0 | 2023-12-20 18:00:00 |
| 65 | 0 | 2023-12-20 18:00:00 |
| 64 | 0 | 2023-12-20 18:00:00 |
そのあとにLIMIT句を付与して実行すると結果が変わるのがわかる。
mysql> select * from item order by created_at limit 5;
+----+------+---------------------+
| id | name | created_at |
+----+------+---------------------+
| 67 | 0 | 2023-12-20 18:00:00 |
| 66 | 0 | 2023-12-20 18:00:00 |
| 64 | 0 | 2023-12-20 18:00:00 |
| 68 | 0 | 2023-12-20 18:00:00 |
| 65 | 0 | 2023-12-20 18:00:00 |
+----+------+---------------------+
5 rows in set (0.01 sec)
created_at
がユニークでない場合、結果が不定となってしまうことがわかる。
なので、純粋にidでORDER BY
をかければOK。
mysql> select * from item order by created_at, id limit 5;
+----+------+---------------------+
| id | name | created_at |
+----+------+---------------------+
| 64 | 0 | 2023-12-20 18:00:00 |
| 65 | 0 | 2023-12-20 18:00:00 |
| 66 | 0 | 2023-12-20 18:00:00 |
| 67 | 0 | 2023-12-20 18:00:00 |
| 68 | 0 | 2023-12-20 18:00:00 |
+----+------+---------------------+
5 rows in set (0.01 sec)
<p style='padding: 5px;'>