Как правильно индексировать таблицу ссылок для соединения многие-ко-многим в MySQL?
2 ответа
Это зависит от того, как Вы ищете.
, Если Вы ищете как это:
/* Given a value from table1, find all related values from table2 */
SELECT *
FROM table1 t1
JOIN table_table tt ON (tt.table_1 = t1.id)
JOIN table2 t2 ON (t2.id = tt.table_2)
WHERE t1.id = @id
затем Вам нужно:
ALTER TABLE table_table ADD CONSTRAINT pk_table1_table2 (table_1, table_2)
В этом случае, table1 будет ведущим в NESTED LOOPS, и Ваш индекс будет применим только, когда table1 будет индексирован сначала.
, Если Вы ищете как это:
/* Given a value from table2, find all related values from table1 */
SELECT *
FROM table2 t2
JOIN table_table tt ON (tt.table_2 = t2.id)
JOIN table1 t1 ON (t1.id = tt.table_1)
WHERE t2.id = @id
затем Вам нужно:
ALTER TABLE table_table ADD CONSTRAINT pk_table1_table2 (table_2, table_1)
по причинам выше.
Вам не нужны независимые индексы здесь. Сводный индекс может использоваться везде, где простой индекс на первом столбце может использоваться. При использовании независимых индексов Вы не сможете искать эффективно оба значения:
/* Check if relationship exists between two given values */
SELECT 1
FROM table_table
WHERE table_1 = @id1
AND table_2 = @id2
Для запроса как это, Вам будет нужен по крайней мере один индекс на обоих столбцах.
Это никогда не плохо, чтобы иметь дополнительный индекс для второго поля:
ALTER TABLE table_table ADD CONSTRAINT pk_table1_table2 PRIMARY KEY (table_1, table_2)
CREATE INDEX ix_table2 ON table_table (table_2)
Первичный ключ будет использоваться для поисков on both values и для поисков на основе значения [1 110], дополнительный индекс будет использоваться для поисков на основе значения [1 111].
Пока Вы указываете оба ключа в запросе, не имеет значения, что приказывает, чтобы они имели в запросе, и при этом не имеет значения, что приказывает, чтобы Вы указали их в индексе.
Однако не маловероятно, что у Вас иногда будут только один или другие из ключей. Если у Вас иногда есть id_1 только, то это должно быть первым (но Вам все еще только нужен один индекс).
, Если Вы иногда имеете один, иногда другой, иногда оба, Вам будут нужны один индекс с обоими ключами и второй (групповой) индекс с одним полем - более выборочные из этих двух ключей - и основной сводный индекс должны запуститься с другого ключа.