ORDER BYの速度

環境

CPU

Core 2 E6600

Physical Memory

2GB

OS

Windows XP SP3

MySQL Version

5.0.27

MySQL Table Type

InnoDB

.NET Framework Version

2.0(SP1)

ADO.NET Provider

Connector/Net 5.1.6

MySqlのバージョンが古いのは，単に昔やったプロジェクトで入っていたものを再利用しているからです。

---

前準備

1. こんなテーブルを作る。

   CREATE TABLE test1 (
       ID INT PRIMARY KEY,
       Value1 INT NOT NUL UNIQUE,
       Value2 INT NOT NULL
   )

2. test1に，ID = Value1 = Value2になるように，[1, 10000]の値を突っ込む。

---

計測

• 以下のプログラムを実行する。

  using System;
  using System.Diagnostics;
  using MySql.Data.MySqlClient;
  
  class Program
  {
      static readonly string[] _commands =
      {
          "SELECT * FROM test1 ORDER BY ID",
          "SELECT * FROM test1 ORDER BY ID limit 10",
          "SELECT * FROM test1 ORDER BY Value1",
          "SELECT * FROM test1 ORDER BY Value1 limit 10",
          "SELECT * FROM test1 ORDER BY Value2",
          "SELECT * FROM test1 ORDER BY Value2 limit 10",
          "SELECT COUNT(ID) FROM test1"
      };
  
      static void Main (string[] args)
      {
          long[] ticks = new long[_commands.Length];
          Stopwatch sw = new Stopwatch();
  
          for (int i = 0; i < 10; ++i)
          {
              using (MySqlConnection conn = new MySqlConnection(
                  @"Server=localhost;Database=test;Uid=uid;Pwd=pwd"))
              {
                  conn.Open();
                  using (MySqlCommand comm = new MySqlCommand("SELECT 10", conn))
                  {
                      comm.ExecuteNonQuery();
                  }
  
                  for (int j = 0; j < _commands.Length; ++j)
                  {
                      string commandStr = _commands[j];
  
                      sw.Reset();
                      using (MySqlCommand comm = new MySqlCommand(commandStr, conn))
                      {
                          sw.Start();
                          comm.ExecuteNonQuery();
                          sw.Stop();
                      }
  
                      ticks[j] += sw.ElapsedTicks;
                  }
                  conn.Close();
              }
          }
  
          for (int j = 0; j < _commands.Length; ++j)
          {
              Console.WriteLine("{0,10}ticks : {1}", ticks[j], _commands[j]);
          }
      }
  }

---

さて，普通に考えたら，IDとValue1にはインデックスがあるので，ORDER BY Value1の方がORDER BY Value2よりも時間はかからなさそうです。 しかし……。

SELECT * FROM test1 ORDER BY ID

213535503ticks

SELECT * FROM test1 ORDER BY ID limit 10

5208264ticks

SELECT * FROM test1 ORDER BY Value1

465056280ticks

SELECT * FROM test1 ORDER BY Value1 limit 10

5790636ticks

SELECT * FROM test1 ORDER BY Value2

283178781ticks

SELECT * FROM test1 ORDER BY Value2 limit 10

120534615ticks

SELECT COUNT(ID) FROM test1

68597829ticks

……なんでValue2でソートした方が速くなるんだ……。念のためExplainしてみましたが，

id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  test1   index   NULL    PRIMARY 4       NULL    10339
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  test1   index   NULL    PRIMARY 4       NULL    10339
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  test1   index   NULL    Value1  4       NULL    10339
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  test1   index   NULL    Value1  4       NULL    10339
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  test1   ALL     NULL    NULL    NULL    NULL    10339   Using filesort
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  test1   ALL     NULL    NULL    NULL    NULL    10339   Using filesort
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  test1   index   NULL    PRIMARY 4       NULL    10339   Using index

Value1はちゃんとインデックス使っている……。最新なら直っているかな？というわけで，5.0.51bを上書きでインストール。結果は……

SELECT * FROM test1 ORDER BY ID

207209592ticks

SELECT * FROM test1 ORDER BY ID limit 10

4652703ticks

SELECT * FROM test1 ORDER BY Value1

472087971ticks

SELECT * FROM test1 ORDER BY Value1 limit 10

5696766ticks

SELECT * FROM test1 ORDER BY Value2

275851026ticks

SELECT * FROM test1 ORDER BY Value2 limit 10

124287543ticks

SELECT COUNT(ID) FROM test1

67732911ticks

変わらない……。データを読ませれば変わるかと，ExecuteNonQueryの呼び出しを以下に修正。

using (MySqlDataReader reader = comm.ExecuteReader())
{
    while (reader.Read())
    {
        for (int k = 0; k < reader.FieldCount; ++k)
        {
            object o = reader[k];
        }
    }
}

SELECT * FROM test1 ORDER BY ID

497410938ticks

SELECT * FROM test1 ORDER BY ID limit 10

5393538ticks

SELECT * FROM test1 ORDER BY Value1

535218507ticks

SELECT * FROM test1 ORDER BY Value1 limit 10

6257565ticks

SELECT * FROM test1 ORDER BY Value2

622938447ticks

SELECT * FROM test1 ORDER BY Value2 limit 10

124877898ticks

SELECT COUNT(ID) FROM test1

67883031ticks

やっと逆転。でもなんかすっきりしない……。

タグ：MySQL SQL