MySQLエラー 1170の修正：キー指定にキーの長さがないBLOB/TEXT型カラムが使用されています

TL;DR: クイックフィックス

MySQLは、TEXTやBLOBカラムの内容全体をインデックス化することを拒否します。これは、これらのフィールドが膨大なデータ量を保持する可能性があるためです。エラー 1170を修正するには、プレフィックス長（MySQLがインデックスとして管理する特定の文字数）を定義する必要があります。

失敗するコマンドの例：

CREATE INDEX idx_description ON products(description);

最初の191文字のみをインデックス化するには、次の構文を使用します：

CREATE INDEX idx_description ON products(description(191));

なぜこのエラーが発生するのか？

InnoDBなどのストレージエンジンには、インデックスキーの長さに厳格な制限があります。LONGTEXTカラムは4GBのデータを保存できますが、InnoDBの内部インデックス制限は、古いフォーマットでは767バイト、最新のDYNAMICフォーマットでは3072バイトであることが一般的です。もしMySQLが4GBのフィールド全体をインデックス化しようとすると、データベースのパフォーマンスは急落し、インデックスファイルのサイズが爆発的に増加してしまいます。

プレフィックス長を要求することで、MySQLはインデックスを軽量に保ちます。これは本質的に、データベースに対して「データの場所を特定するために、この文字列の先頭部分だけを見てほしい」と伝えていることになります。これにより、ハードウェアのボトルネックに陥ることなく、高速な検索を維持できます。

エラー 1170を解決する3つの方法

1. プレフィックスインデックスを実装する

ほとんどの開発者は、一意性とパフォーマンスのバランスが取れた長さを選択することでこの問題を解決します。一般的な選択肢は191文字または255文字です。

ALTER TABLEを使用する場合：

ALTER TABLE articles ADD INDEX (content(255));

CREATE INDEXを使用する場合：

CREATE INDEX idx_title_prefix ON blog_posts (title(100));

技術メモ： utf8mb4文字セットを使用する場合、MySQLは1文字あたり4バイトを確保します。191文字のプレフィックスは764バイト（191 * 4）となり、古いMySQL設定で見られる従来の767バイトの制限内に安全に収まります。

2. カラムをVARCHARに変更する

本当にTEXTカラムが必要ですか？データが通常255文字や500文字に収まる場合は、VARCHARの方が適しています。TEXTとは異なり、VARCHARカラムは、合計サイズがエンジンの制限内に収まっている限り、インデックス作成にプレフィックス長を必要としません。

-- TEXTからVARCHAR(255)に切り替え
ALTER TABLE users MODIFY COLUMN bio VARCHAR(255); 
CREATE INDEX idx_bio ON users(bio);

3. 大規模な検索にはFULLTEXTを使用する

標準的なB-treeインデックスは完全一致には優れていますが、「〜を含む」といった検索には不向きです。ブログ投稿用の検索バーを作成している場合、プレフィックスインデックスはあまり役に立ちません。代わりにFULLTEXTインデックスに切り替えてください。これにはプレフィックス長が必要なく、大きなテキストブロックを効率的に処理できます。

ALTER TABLE products ADD FULLTEXT(description);

FULLTEXTインデックスには特定の構文が必要であることに注意してください。標準的なWHERE句ではなく、SQLクエリでMATCH() ... AGAINST()を使用する必要があります。

プレフィックス長の「スイートスポット」を見つける

インデックスの長さを勘で決めないでください。計算によって最も効率的な数値を見つけることができます。目標は、プレフィックスの一意性がフル文字列とほぼ同等になる長さを見つけることです。

これらの一意性を比較して、どこで一意性が横ばいになるかを確認します：

-- 1. テーブル内の一意な値の総数
SELECT COUNT(DISTINCT description) FROM products;

-- 2. 最初の10文字のみを見た場合の一意な値の数
SELECT COUNT(DISTINCT LEFT(description, 10)) FROM products;

-- 3. 最初の20文字を見た場合の一意な値の数
SELECT COUNT(DISTINCT LEFT(description, 20)) FROM products;

もし20文字でのカウントが全一意なカウントの99%であれば、20は完璧で高性能なプレフィックス長と言えます。

変更を確認する方法

修正を適用したら、インデックス構造を再確認してください。次のコマンドを実行します：

SHOW INDEX FROM table_name;

Sub_partカラムを確認してください。指定した整数（191など）が表示されているはずです。もしNULLと表示されている場合は、インデックスがカラム全体をカバーしていることを意味しますが、これはBLOB型以外の型でのみ発生します。

パフォーマンスのトレードオフ

短く保つ： プレフィックスが長くなるとインデックスが重くなり、INSERT操作が遅くなります。必要な分だけをインデックス化してください。
BLOBのプライマリキーを避ける： TEXTカラムをプライマリキーとして使用することは、アーキテクチャ上の警告信号（レッドフラグ）です。パフォーマンス向上のために、BIGINT AUTO_INCREMENTやUUIDを使用してください。
ソートの問題： MySQLは通常、ORDER BYやGROUP BY操作にプレフィックスインデックスを使用できません。このカラムで頻繁にソートを行う場合は、別のスキーマ設計を検討してください。