はしがき

ORM を使いながら、いくつかのテーブルを sql 句で join したことがありました。

join の条件(ON)に CASE を使えば、インデックスはしないって、インデックスがなんのか？探して整理します。

インデックス(index)とは？

インデックスは検索のスピードの向上のために使う仕組みです。

テーブルのカラムをインデックス化して、該当のテーブルのレコードを full scan ではなく、インデックスを利用して、高いスピードの検索を行います。

url: https://knowtechstuffz.blogspot.com/2015/04/how-sql-indexes-work-internally.html

インデックスの使い方

生成方法

CREATE [UNIQUE] INDEX インデックスネーム ON テーブル (カラム) [ASC | DESC]

UNIQUE オプションは重複しない UNIQUE なインデックスをつくります。

削除

DROP INDEX インデックスネーム ON テーブル

PRIMARY KEY、また、UNIQUE KEY から自動で作られたインデックスは DROP INDEX で削除することができません。

もし、ALTER TABLE で、PRIMARY KEY、UNIQUE KEY を削除すれば、自動で作られたインデックスも削除できます。

テスト

テーブルを作ります。

CREATE TABLE hello_world (
    id mediumint(9) NOT NULL auto_increment,
    hell varchar(10) default NULL,
    world tinyint(3) unsigned default NULL,
    PRIMARY KEY (id)
);

インデックスを確認しましょう。

SHOW INDEX FROM hello_world;

やはり、一つしかありませんね。

インデックスを作って、確認します。

CREATE INDEX index_hello_world ON hello_world (hell);

SHOW INDEX FROM hello_world;

index_hello_world という index が作られました！

また、インデックスがどうやって実行されるかのために、EXPLAIN を使いましょう。

In SQL, the EXPLAIN keyword provides a description of how the SQL queries are executed by the databases. These descriptions include the optimizer logs, how tables are joined and in what order, etc.

SQL には EXPLAIN を使って、SQL 句が実行される方法の説明を受けることができます。

EXPLAIN SELECT * FROM hello_world WHERE id > 4;

EXPLAIN SELECT * FROM hello_world WHERE hell LIKE ('h%');

key を確認すると PRIMARY KEY と自分が設定した、index_hello_world がインデックスとして使われました！

では、使われない時もある？

そうですね。このインデックスを使わない場合もあります。

関数や calculate する時

EXPLAIN SELECT * FROM hello_world WHERE UPPER(hell) = 'HELL';

EXPLAIN SELECT * FROM hello_world WHERE id + 1 > 4;

インデックスの場合データが sort されています。
この sort されたデータが、関数や calculate を通じて、データが変わりますので、sort されない可能性があります。
インデックスは sort が前提なので、インデックスを使いません。

LIKE 句の wildcard の位置

さっき検索したことを再利用します。

EXPLAIN SELECT * FROM hello_world WHERE hell LIKE ('%h');

wildcard が後ろにある時は、インデックスを使えますが、前にある時は、使いません。
これは すべての文字列 + 検索内容なので、整列とは別にインデックスを使いません。

OR を使うとき

EXPLAIN SELECT * FROM hello_world WHERE id > 2 OR hell LIKE ('h%');

OR を使うと、二つ以上の field を同時に比較するので、使える key が二つありましたが、実際に使う key はありません。

別のカラムを WHERE から比較するとき

EXPLAIN SELECT * FROM hello_world WHERE id = world;

インデックスを使う時は、インデックス scan する前に、全ても値を type conversion しますので、カラムの data type が違うことを比較するクエリを実行するとき、インデックスの performance が低下してしまう可能性があります。

IN を使いすぎるとき

EXPLAIN SELECT * FROM hello_world WHERE id IN (1, 2);

このように少ない場合は問題なく実行します。が。。。

EXPLAIN SELECT * FROM hello_world WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164, 165, 166, 167, 168, 169, 170, 171, 172, 173, 174, 175, 176, 177, 178, 179, 180, 181, 182, 183, 184, 185, 186, 187, 188, 189, 190, 191, 192, 193, 194, 195, 196, 197, 198, 199, 200, 201, 202, 203, 204, 205, 206, 207, 208, 209, 210, 211, 212, 213, 214, 215, 216, 217, 218, 219, 220, 221, 222, 223, 224, 225, 226, 227, 228, 229, 230, 231, 232, 233, 234, 235, 236, 237, 238, 239, 240, 241, 242, 243, 244, 245, 246, 247, 248, 249, 250, 251, 252, 253, 254, 255, 256, 257, 258, 259, 260, 261, 262, 263, 264, 265, 266, 267, 268, 269, 270, 271, 272, 273, 274, 275, 276, 277, 278, 279, 280, 281, 282, 283, 284, 285, 286, 287, 288, 289, 290, 291, 292, 293, 294, 295, 296, 297, 298, 299, 300, 301, 302, 303, 304, 305, 306, 307, 308, 309, 310, 311, 312, 313, 314, 315, 316, 317, 318, 319, 320, 321, 322, 323, 324, 325, 326, 327, 328, 329, 330, 331, 332, 333, 334, 335, 336, 337, 338, 339, 340, 341, 342, 343, 344, 345, 346, 347, 348, 349, 350, 351, 352, 353, 354, 355, 356, 357, 358, 359, 360, 361, 362, 363, 364, 365, 366, 367, 368, 369, 370, 371, 372, 373, 374, 375, 376, 377, 378, 379, 380, 381, 382, 383, 384, 385, 386, 387, 388, 389, 390, 391, 392, 393, 394, 395, 396, 397, 398, 399, 400, 401, 402, 403, 404, 405, 406, 407, 408, 409, 410, 411, 412, 413, 414, 415, 416, 417, 418, 419, 420, 421, 422, 423, 424, 425, 426, 427, 428, 429, 430, 431, 432, 433, 434, 435, 436, 437, 438, 439, 440, 441, 442, 443, 444, 445, 446, 447, 448, 449, 450, 451, 452, 453, 454, 455, 456, 457, 458, 459, 460, 461, 462, 463, 464, 465, 466, 467, 468, 469, 470, 471, 472, 473, 474, 475, 476, 477, 478, 479, 480, 481, 482, 483, 484, 485, 486, 487, 488, 489, 490, 491, 492, 493, 494, 495, 496, 497, 498, 499, 500);

このように使いすぎると、使わなくなります。

JOIN に CASE を使うとき

まず、hello_world の id を reference しているテーブルを作ります。

CREATE TABLE another_world (
    id mediumint(9) NOT NULL auto_increment,
    hello_world_id mediumint(9) NOT NULL,
    tenkoku varchar(10) default NULL,
    kita tinyint(3) unsigned default NULL,
    PRIMARY KEY (id),
    FOREIGN KEY(hello_world_id) REFERENCES hello_world (id)
);

INSERT INTO another_world (hello_world_id, tenkoku, kita) VALUES (1, 'uu', 52), (5, 'not', 32), (1, 'worl', 11);

SELECT * FROM another_world;

インデックスも確認します。

SHOW INDEX FROM another_world;

FOREIGN KEY もインデックスされましたね。

また、LEFT JOIN と LEFT JOIN の ON に CASE WHEN を使って実行します。

EXPLAIN SELECT * FROM hello_world as hw
LEFT JOIN another_world as aw
ON hw.id = aw.hello_world_id;

EXPLAIN SELECT * FROM hello_world as hw
LEFT JOIN another_world as aw
ON  hw.id = CASE WHEN aw.hello_world_id = 1 THEN 225 ELSE aw.hello_world_id END;

なんとか、another_world から設定されていた index がなくなりました。

いくつかの理由がありますが、自分が考える理由は CASE の条件によって、結果が不明です。
今回の場合は、hello_world_id をインデックスしていているので、インデックスは以下のようにセーブされている可能性があります。

another_world の hello_world_id の index

another_world の hello_world_id の値	location
1	another_world の location
4	another_world の location
5	another_world の location

ですが、CASE WHEN aw.hello_world_id = 1 THEN 225 ELSE aw.hello_world_id END を使って、hello_world_id が1の場合1をインデックスするか225をインデックスするかのような不明点があるので、インデックスしないと思います。

以外は、条件が複雑になったので、DB なりの最適化ができないので、インデックスしないと予想できます。

結果になんか using where や using index condition が表示しましけど、これの違さは？

using where: 単純に WHERE・ON がついているテーブルから、データを取得して、return します。

using index condition: 条件にインデックされていない row が含まれています。
一先、インデックされた row を確認して、テーブルから他の条件の row を探します。

SQL INDEX のあれこれ