Индекс MySQL для столбца TEXT не эффективен

Question

У меня есть довольно простая таблица для хранения пар ключ: значение, настроенная так:

CREATE TABLE `assetProperties` (
  `propertyKey` varchar(255) NOT NULL,
  `propertyValue` text NOT NULL,
  `id` bigint(20) NOT NULL,
  `assetInstance_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK67F768435C68E1C0` (`id`),
  KEY `FK67F76843FBDFC83F` (`assetInstance_id`),
  KEY `keyIndex` (`propertyKey`),
  KEY `valIndex` (`propertyValue`(255)),
  CONSTRAINT `FK67F76843FBDFC83F` FOREIGN KEY (`assetInstance_id`) REFERENCES `assets` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

... и в этой таблице есть разумное количество записей:

mysql> select count(*) from assetProperties;
+----------+
| count(*) |
+----------+
| 19931305 |
+----------+

... и что я хочу сделать, это найти записи, которые точно соответствуют данной паре ключ: значение. Например, используя запрос как:

SELECT count(*) FROM assetProperties WHERE propertyKey = "Wood Species" 
    AND propertyValue = "Jarrah";

Без каких-либо индексов на propertyKey а также propertyValue этот запрос выполняется довольно медленно, примерно на 10+ секунд. Что и следовало ожидать. Проблема в том, что даже после добавления индексов в оба поля время запроса не улучшается.

Странно то, что индекс на propertyKey кажется, работает правильно:

-- Before adding index
mysql> SELECT count(*) FROM assetProperties WHERE propertyKey = "Wood Species";
+----------+
| count(*) |
+----------+
|   339395 |
+----------+
1 row in set (9.37 sec)  <-- bad

-- After adding index
mysql> SELECT count(*) FROM assetProperties WHERE propertyKey = "Wood Species";
+----------+
| count(*) |
+----------+
|   339395 |
+----------+
1 row in set (0.16 sec)  <-- reasonable

... пока индекс на propertyValue похоже, мало что изменило:

-- Before adding index
mysql> SELECT count(*) FROM assetProperties WHERE propertyValue = "Jarrah";
+----------+
| count(*) |
+----------+
|   219099 |
+----------+
1 row in set (12.51 sec)  <-- bad

-- After adding index
mysql> SELECT count(*) FROM assetProperties WHERE propertyValue = "Jarrah";
+----------+
| count(*) |
+----------+
|   219099 |
+----------+
1 row in set (9.75 sec)  <-- still garbage

Единственная разница между propertyKey а также propertyValue является то, что бывший varchar(255) столбец и последний text, Есть ли что-то, что мешает MySQL эффективно использовать индекс на text колонка, или что я могу сделать, чтобы сделать запрос быстрее?

редактировать

Также попытался добавить многостолбцовый индекс по обоим полям. Не сделал разницы

И далее, если я добавлю новый столбец (скажем, propertyValueShort) типа varchar(255) и скопировать значения из propertyValue в новый столбец и установить соответствующие индексы, он работает следующим образом:

mysql> SELECT count(*) FROM assetProperties WHERE propertyKey = "Wood Species" AND propertyValueShort = "Jarrah";
+----------+
| count(*) |
+----------+
|   219099 |
+----------+
1 row in set (0.14 sec)  <-- acceptable

Так что это может быть немедленным ответом (самый длинный propertyValue в настоящее время используется 88 символов, поэтому мне не нужно использовать text там). Однако до сих пор не объясняет, почему индекс на text колонка так плохо работает.

Answer 1

PRIMARY KEY (`id`),
KEY `keyIndex` (`propertyKey`),

->

PRIMARY KEY(property_key, id),
INDEX(id),

Зачем?

• InnoDB "кластеризует" ПК с данными. Это означает, что все строки с одинаковыми property_key теперь будет "рядом друг с другом", тем самым сводя к минимуму чтение с диска.
• Является id AUTO_INCREMENT? Если так, INDEX(id) достаточно; ПК не нужен. Остальное... зачем id? Вы можете избавиться от этого? На самом деле, нет; вам все равно нужно сделать ПК уникальным как-то.

Что ROW_FORMAT стола? Как долго property_value обычно? Они влияют на то, хранятся ли "большие" поля, такие как TEXT, рядом со строкой или хранятся отдельно.

(Итог: схемы ключ-значение - отстой.)