Улучшение таблиц MySQL с индексами

Я очень новичок в индексах в MySQL. Я знаю, мне, наверное, следовало бы это сделать раньше, но большинство проектов было достаточно маленьким для меня, чтобы уйти от него;)

Итак, теперь я тестирую его. Я выполнил свой тест, выполнив EXPLAIN по запросу:

Query:

EXPLAIN SELECT a . *
FROM `tff__keywords2data` AS a
LEFT JOIN `tff__keywords` AS b ON a.keyword_id = b.id
WHERE (
b.keyword = 'dog' || b.keyword = 'black' || b.keyword = 'and' || b.keyword = 'white'
)
GROUP BY a.data_id
HAVING COUNT( a.data_id ) =4

Во-первых, без индексов я получил следующие результаты:

Затем с индексом data_id и keyword_id я получил следующее:

Итак, насколько я понимаю, количество строк, которые должен выполнять MySQL, идет от 61k до 10k, что должно быть хорошо?

Итак, мой вопрос: я прав? И есть ли что-нибудь еще, о чем я мог бы подумать, пытаясь оптимизировать?

UPDATE:

Далее, после некоторой помощи AJ и Piskvor, указывающей мою другую таблицу и ее ключевое слово, не имеющее индекса, я получил следующее:

Отличное улучшение! Правильно?

Ответ 1

Как видите, key, используемый для таблицы b, по-прежнему NULL. Вы можете добавить индекс на b.keyword и сопоставить с

WHERE b.keyword IN ('dog','black','and','white')

Это функционально отличается от вашего предложения WHERE, хотя оно возвращает те же результаты.

Как он выглядит, вы можете быть заинтересованы в полнотекстовом поиске.

Ответ 2

В зависимости от того, что вы хотите достичь, вы должны либо заменить LEFT JOIN на INNER JOIN, либо перенести условие WHERE в предложение ON:

Как и сейчас:

SELECT  a.*
FROM    `tff__keywords2data` AS a
LEFT JOIN
        `tff__keywords` AS b
ON      b.id = a.keyword_id
WHERE   b.keyword = 'dog' || b.keyword = 'black' || b.keyword = 'and' || b.keyword = 'white'
GROUP BY
        a.data_id
HAVING  COUNT( a.data_id ) = 4

ваш запрос на самом деле является INNER join (поскольку у вас есть ненулевые условия в предложении WHERE).

Кроме того, вместо использования битовой арифметики (которая не поддается продвижению) вы должны использовать собственные конструкторы OR или IN:

SELECT  a.*
FROM    `tff__keywords2data` AS a
JOIN    `tff__keywords` AS b
ON      b.id = a.keyword_id
WHERE   b.keyword IN ('dog', 'black', 'and', 'white')
GROUP BY
        a.data_id
HAVING  COUNT(*) = 4

Вы также можете создать индекс на ttf__keywords (keyword), который может фильтровать в keywords, который вы ищете, и сделать меньше записей для выбора из ведущего b.

Наконец, если вам не нужно неявное упорядочение на a.data_id, избавитесь от него, добавив ORDER BY NULL:

SELECT  a.*
FROM    `tff__keywords2data` AS a
JOIN    `tff__keywords` AS b
ON      b.id = a.keyword_id
WHERE   b.keyword IN ('dog', 'black', 'and', 'white')
GROUP BY
        a.data_id
HAVING  COUNT(*) = 4 
ORDER BY
        NULL

Это удалит filesort из вашего плана.

Ответ 3

Да, это улучшилось (но от быстрого поиска я думаю, что можно улучшить). вы можете видеть, что оптимизатор запросов теперь видит И ИСПОЛЬЗУЕТ индекс keyword_id. он уменьшил строки, которые он ищет с 64283 до 10216. Но это все еще использует файловое хранилище, которое, надеюсь, кто-то еще может прояснить, похоже на сканирование таблицы SQL Server? что нехорошо... я мог ошибаться, хотя.

Теперь вы сможете сократить строки из таблицы b ниже 10216

Ответ 4

Вы выполняете сравнение строк с b.keyword.... добавьте туда индекс.

Ответ 5

Используйте INNER JOIN вместо LEFT JOIN. Левое соединение будет возвращать несогласованные строки в таблице соединений, которые, я думаю, вам не нужны.

Ответ 6

Попробуйте поставить индексы на все в предложении WHERE и что угодно в JOIN, так что это будет:

a.keyword_id b.id b.keyword

Вы также можете попробовать добавить индекс в a.data_id, как и в "GROUP BY". Слишком много индексов обычно не является проблемой, если вы не добавляете большие объемы данных в большие таблицы, что может привести к тому, что INSERT будут очень медленными.