[解決済み] #1071 - 指定された鍵が長すぎる; 鍵の最大長は 1000 バイトです。

質問

このタイトルの質問は以前にも回答されていると思いますが、読んでみてください。私は投稿する前に、このエラーに関する他のすべての質問/回答を熟読しました。

次のクエリで上記のエラーが発生します。

CREATE TABLE IF NOT EXISTS `pds_core_menu_items` (
  `menu_id` varchar(32) NOT NULL,
  `parent_menu_id` int(32) unsigned DEFAULT NULL,
  `menu_name` varchar(255) DEFAULT NULL,
  `menu_link` varchar(255) DEFAULT NULL,
  `plugin` varchar(255) DEFAULT NULL,
  `menu_type` int(1) DEFAULT NULL,
  `extend` varchar(255) DEFAULT NULL,
  `new_window` int(1) DEFAULT NULL,
  `rank` int(100) DEFAULT NULL,
  `hide` int(1) DEFAULT NULL,
  `template_id` int(32) unsigned DEFAULT NULL,
  `alias` varchar(255) DEFAULT NULL,
  `layout` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`menu_id`),
  KEY `index` (`parent_menu_id`,`menu_link`,`plugin`,`alias`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

どなたか、この問題の原因と解決方法をご存知でしょうか？ この同じクエリは私のローカルマシンでは完全に動作し、以前のホストでも同様に動作していました。Btw.it's from a mature project - phpdevshell - so I'd guess these guys know what they're doing, but you never know.

どんな手がかりでもありがたいです。

phpMyAdminを使用しています。

どのように解決するのですか？

Devart が言うように、インデックスの全長が長すぎます。

短い答えは、とにかくそのような長い VARCHAR カラムのインデックスを作成すべきではないということで、インデックスは非常にかさばり、非効率になります。

ベストプラクティスは プレフィックスインデックス を使用し、データの左側の部分文字列にのみインデックスを付けることです。 ほとんどのデータは、いずれにせよ 255 文字よりずっと短いでしょう。

インデックスを定義する際に、列ごとのプレフィックス長を宣言することができます。 例えば

...
KEY `index` (`parent_menu_id`,`menu_link`(50),`plugin`(50),`alias`(50))
...

しかし、あるカラムに最適なプレフィックスの長さは何でしょうか。 それを知るための方法を紹介します。

SELECT
 ROUND(SUM(LENGTH(`menu_link`)<10)*100/COUNT(`menu_link`),2) AS pct_length_10,
 ROUND(SUM(LENGTH(`menu_link`)<20)*100/COUNT(`menu_link`),2) AS pct_length_20,
 ROUND(SUM(LENGTH(`menu_link`)<50)*100/COUNT(`menu_link`),2) AS pct_length_50,
 ROUND(SUM(LENGTH(`menu_link`)<100)*100/COUNT(`menu_link`),2) AS pct_length_100
FROM `pds_core_menu_items`;

で指定された文字列の長さ以上の行がない割合を教えてくれます。 menu_link カラムに指定された文字列長を持たない行の割合を示します。 このような出力が表示されるかもしれません。

+---------------+---------------+---------------+----------------+
| pct_length_10 | pct_length_20 | pct_length_50 | pct_length_100 |
+---------------+---------------+---------------+----------------+
|         21.78 |         80.20 |        100.00 |         100.00 |
+---------------+---------------+---------------+----------------+

これは、文字列の 80% が 20 文字未満で、すべての文字列が 50 文字未満であることを表しています。 したがって、プレフィックス長50以上のインデックスを作成する必要はありませんし、255文字の全長をインデックスする必要もないことは確かです。

追記:この INT(1) と INT(32) のデータ型は、MySQL に関する別の誤解を表しています。 numeric 引数は、ストレージやカラムに許可される値の範囲に関連する効果はありません。 INT は常に 4 バイトで、常に -2147483648 から 2147483647 までの値を許容します。 numeric引数は表示中の値のパディングに関するもので、これは ZEROFILL オプションを使用しない限り効果はありません。