DBでテーブルのカラムにindexを張るとどれくらい変わるか調べる

SQLをいじっていて気になったので試してみました。 データベースはMySQLを使用しています。

使用するデータはこちらのサイト にあるsubmissionデータのcsvファイルです。

事前設定

csvファイルからデータを読み込むためにmysqlの設定を変更します。 デフォルトではファイルからの読み込みはセキュリティの問題上拒否されるようになっているのでこれを許可します。 my.cnfに以下の設定を追加します。

[mysqld]
loose-local-infile = 1

設定を書いたらサーバを再起動してMySQLにログインします。 ここでもログインコマンドにオプションを設定してログインします。

 $ mysql -u root -p --local-infile=1

データベース、テーブルの作成、データのロードを行います。SQL文は以下の通りです。 テーブル定義は元のデータに合わせて作っていて、idに主キーを設定した状態にします。

CREATE DATABASE IF NOT EXISTS atcoder;
USE atcoder;

DROP TABLE IF EXISTS submissions;
CREATE TABLE submissions (
    id             BIGINT        NOT NULL,
    epoch_second   BIGINT        NOT NULL,
    problem_id     VARCHAR (255) NOT NULL,
    contest_id     VARCHAR (255) NOT NULL,
    user_id        VARCHAR (255) NOT NULL,
    language       VARCHAR (255) NOT NULL,
    point          DOUBLE        NOT NULL,
    length         INT           NOT NULL,
    result         VARCHAR (255) NOT NULL,
    execution_time INT,

    PRIMARY KEY (id)
);

LOAD DATA 
    LOCAL INFILE './atcoder_submissions.csv' 
    INTO TABLE submissions 
    FIELDS TERMINATED BY ',' 
    LINES TERMINATED BY '\n'
    IGNORE 1 LINES;

何も考えずにクエリを発行してみる

まずはそもそもデータが何件あるか取得します。

mysql> select count(*) from submissions;
+----------+
| count(*) |
+----------+
|  3498339 |
+----------+
1 row in set (0.68 sec)

約350万件ですね。

全体のAC数を数えます

mysql> select count(*) from submissions where result='AC';
+----------+
| count(*) |
+----------+
|  1742578 |
+----------+
1 row in set (0.82 sec)

AC数が多いユーザ（重複なし）の上位10名を出してみます。

mysql> select user_id, count(distinct problem_id) as accepted_sum 
    -> from submissions 
    -> group by user_id order by accepted_sum desc limit 10;
+------------+--------------+
| user_id    | accepted_sum |
+------------+--------------+
| kmjp       |         2197 |
| ei13333    |         1930 |
| beet       |         1880 |
| latte0119  |         1810 |
| E869120    |         1652 |
| uwi        |         1573 |
| MAK_culcul |         1540 |
| square1001 |         1510 |
| leafmoon   |         1455 |
| mamekin    |         1426 |
+------------+--------------+
10 rows in set (48.57 sec)

流石に時間かかりますね。

カラムにindexを付ける

ユーザIDの絡むに対してindexをつけます

alter table submissions add index user_id(user_id)

そして同じクエリを実行

mysql> select user_id, count(distinct problem_id) as accepted_sum 
    -> from submissions 
    -> group by user_id order by accepted_sum desc limit 10;
+------------+--------------+
| user_id    | accepted_sum |
+------------+--------------+
| kmjp       |         2197 |
| ei13333    |         1930 |
| beet       |         1880 |
| latte0119  |         1810 |
| E869120    |         1652 |
| uwi        |         1573 |
| MAK_culcul |         1540 |
| square1001 |         1510 |
| leafmoon   |         1455 |
| mamekin    |         1426 |
+------------+--------------+
10 rows in set (22.88 sec)

半分以下になりましたね。

今回はテーブルが一つですが複数テーブルを使うクエリで適切にindexを設定すればより効果が大きく感じられると思います。