Я очень новичок в индексах в 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, указывающей мою другую таблицу и ее ключевое слово, не имеющее индекса, я получил следующее:
Отличное улучшение! Правильно?
Как видите, key, используемый для таблицы b, по-прежнему NULL. Вы можете добавить индекс на b.keyword и сопоставить с
WHERE b.keyword IN ('dog','black','and','white')
Это функционально отличается от вашего предложения WHERE, хотя оно возвращает те же результаты.
Как он выглядит, вы можете быть заинтересованы в полнотекстовом поиске.
В зависимости от того, что вы хотите достичь, вы должны либо заменить 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 из вашего плана.
Да, это улучшилось (но от быстрого поиска я думаю, что можно улучшить). вы можете видеть, что оптимизатор запросов теперь видит И ИСПОЛЬЗУЕТ индекс keyword_id. он уменьшил строки, которые он ищет с 64283 до 10216. Но это все еще использует файловое хранилище, которое, надеюсь, кто-то еще может прояснить, похоже на сканирование таблицы SQL Server? что нехорошо... я мог ошибаться, хотя.
Теперь вы сможете сократить строки из таблицы b ниже 10216
Вы выполняете сравнение строк с b.keyword.... добавьте туда индекс.
Используйте INNER JOIN вместо LEFT JOIN. Левое соединение будет возвращать несогласованные строки в таблице соединений, которые, я думаю, вам не нужны.
Попробуйте поставить индексы на все в предложении WHERE и что угодно в JOIN, так что это будет:
a.keyword_id b.id b.keyword
Вы также можете попробовать добавить индекс в a.data_id, как и в "GROUP BY". Слишком много индексов обычно не является проблемой, если вы не добавляете большие объемы данных в большие таблицы, что может привести к тому, что INSERT будут очень медленными.