Блог

У меня есть этот запрос mysql, и я не уверен, каковы последствия индексации всех полей в запросе . Я имею в виду, нормально ли индексировать все поля в операторе CASE, операторе Join и операторе Where? Есть ли какие-либо последствия для производительности индексирования полей?

 SELECT roots.id as root_id, root_words.*,
CASE 
WHEN root_words.title LIKE '%text%' THEN 1
WHEN root_words.unsigned_title LIKE '%normalised_text%' THEN 2
WHEN unsigned_source LIKE '%normalised_text%' THEN 3
WHEN roots.root LIKE '%text%' THEN 4
END as priorities
FROM  roots INNER JOIN root_words ON roots.id=root_words.root_id
WHERE (root_words.unsigned_title LIKE '%normalised_text%') OR (root_words.title LIKE '%text%')
OR (unsigned_source LIKE '%normalised_text."%') OR (roots.root LIKE '%text%') ORDER by priorities
  

Кроме того, как я могу еще больше повысить скорость выполнения приведенного выше запроса?

Спасибо!

1. Вы индексируете столбцы в таблицах, а не запросы.

2. Ни один из указанных вами критериев поиска не сможет использовать индексы (поскольку условия поиска начинаются с подстановочного знака).

3. Вы должны убедиться, что id столбец проиндексирован, чтобы ускорить JOIN . (Предположительно, он уже проиндексирован как ПЕРВИЧНЫЙ КЛЮЧ в одной таблице и ВНЕШНИЙ КЛЮЧ в другой).

Чтобы ускорить этот запрос, вам нужно будет использовать полнотекстовый поиск. Добавление индексов не ускорит этот конкретный запрос и отнимет у вас время на вставки, обновления и удаления.

Предостережение: индексы ускоряют время поиска, но замедляют выполнение вставок и обновлений.

Чтобы ответить на последствия индексации каждого поля, следует отметить снижение производительности при использовании индексов всякий раз, когда индексируемые данные изменяются путем вставки, обновления или удаления. Это потому, что SQL необходимо поддерживать индекс. Это баланс между тем, как часто считываются данные, и тем, как часто они изменяются.

В этом конкретном запросе единственный индекс, который мог бы помочь, был бы в вашем JOIN предложении, в полях roots.id и root_words.root_id .

Ни одна из проверок в вашем WHERE предложении не может быть проиндексирована из-за ведущего '%' . Это заставляет SQL сканировать каждую строку в этих таблицах на предмет совпадающего значения.

Если вы сможете удалить начало '%' , вы тогда выиграете от индексов по этим полям … если нет, вам следует подумать о реализации полнотекстового поиска; но имейте в виду, это не тривиально.

Индексация не поможет при использовании в сочетании с LIKE '%something%' .

Это все равно что искать слова в словаре, которые где-то в них есть ae . Словарь (или индекс в данном случае) организован на основе первой буквы слова, затем второй буквы и т.д. В нем нет механизма, позволяющего расположить все слова с ae в них близко друг к другу. В конечном итоге вы все равно читаете весь словарь от начала до конца.

Индексирование полей, используемых в предложении CASE, скорее всего, вам не поможет. Индексирование помогает, упрощая поиск записей в таблице. Предложение CASE касается обработки найденных вами записей, а не их поиска в первую очередь.

Оптимизаторам также может быть сложно оптимизировать множество несвязанных OR условий, таких как ваше. Оптимизатор пытается сузить объем усилий для завершения вашего запроса, но это трудно сделать, когда несвязанные условия могут сделать запись приемлемой.

В целом, ваш запрос выиграл бы от индексов на roots(root_id) и / или roots(id) , но не более того.

Однако, если бы вам пришлось индексировать дополнительные поля, это привело бы к двум основным затратам:
— Увеличение времени записи (вставка, обновление или удаление) из-за дополнительных индексов для записи
— Увеличение занимаемого места на диске