【MySQL】ORDER BY DESCを使わずに逆順ソートする裏技

こんにちは、エキサイト株式会社しばたにえんです。 ORDER BY DESCを使わずに逆順ソートする裏技を紹介します。

問題点

MySQL8.0未満だと降順インデックスを貼ることができず、キー値は昇順で入るためDESCでソートする際にパフォーマンスが低下してしまう。

通常のORDER BY DESCを使ったSELECT例

• テーブル作成

CREATE TABLE `sample_comment`(
    `sample_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'sampleテーブルの主キー',
    `comment` text NOT NULL COMMENT 'コメント',
    `posted_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'投稿日',
    PRIMARY KEY(`sample_id`),
    KEY `idx_posted_at`(`posted_at`)
) COMMENT = 'コメントテーブル';

• レコード登録

INSERT INTO sample_comment
    (comment)
VALUES
    ('hoge'),
    ('fuga'),
    ('ほげ'),
    ('ふが')
....
;

• データ取得

SELECT
    comment,
    posted_at,
FROM
    sample_comment
ORDER BY
    posted_at DESC

解決方法

posted_atに-1を掛けた値で昇順ソートをすれば、ORDER BY DESCと同じことを昇順ソートでできる

1. posted_atをUNIX_TIMESTAMP化して-1を掛けた値を登録するカラムの追加
2. posted_atをUNIX_TIMESTAMP化して-1を掛けた値を登録
3. インデックスの登録

posted_atに-1を掛けた値を利用してSELECTする例

• テーブル作成

CREATE TABLE `sample_comment`(
    `sample_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'sampleテーブルの主キー',
    `comment` text NOT NULL COMMENT 'コメント',
    `minus_posted_at_timestamp` bigint(20) DEFAULT '0' COMMENT '投稿日をタイムスタンプ化した値 * (-1)の値 order by descの代わりに使用',
    PRIMARY KEY(`sample_id`),
    KEY `idx_minus_posted_at_timestamp`(`minus_posted_at_timestamp`)
) COMMENT = 'コメントテーブル';

• レコード登録

INSERT INTO sample_comment
    (comment, minus_posted_at_timestamp)
VALUES
    ('hoge', -1 * UNIX_TIMESTAMP()),
    ('fuga', -1 * UNIX_TIMESTAMP()),
    ('ほげ', -1 * UNIX_TIMESTAMP()),
    ('ふが', -1 * UNIX_TIMESTAMP())
....
;

• データ取得

SELECT
    comment
FROM
    sample_comment
ORDER BY
    minus_posted_at_timestamp