Рецепты оптимизации запросов

• выбираем только нужные колонки и строки, используем внешние ключи для ускорения JOIN
• ограничиваем обновление всей таблицы с помощью LIMIT или другими методами
• иногда вместо JOIN выгодна декомпозиция на несколько SELECT с использованием IN, вообще минимизируем JOIN
• в GROUP BY и ORDER BY желательно только колонки одной таблицы для использования индексов
• замена вложенных запросов на JOIN может дать выигрыш, но нужно проверять
• TEXT/BLOB поля в JOIN снижают производительность
• mysql_fetch_array() не работает построчно, в отличие от mysql_unbuffered_array()

Иногда можно оптимизировать запрос путем явного указания правила оптимизации, но лучше использовать эту возможность только если больше ничего не помогает.

• HIGH_PRIORITY/LOW_PRIORITY - приоритет для выражения
• DELAYED - отложенный INSERT/REPLACE
• STRAIGHT JOIN - сортировка JOIN в указанном порядке
• SQL_SMALL_RESULT/SQL_BIG_RESULT - для GROUP BY и DISTINCT, нужно ли помещать временную таблицу на диск
• SQL_BUFFER_RESULT - помещает результата в буфер
• SQL_CACHE/SQL_NO_CACHE - управление кешированием запроса
• USE INDEX, FORCE INDEX, IGNORE INDEX - использование индексов

COUNT(*) считает строки, COUNT(expression) считает количество не NULL результатов:

COUNT(color = 'red' OR NULL)

Основные моменты:

• COUNT(*) быстрее чем COUNT(id)
• для подсчета количества значений используем покрывающие индексы
• денормализация может существенно улучшить производительность: кеширование счетчиков в отдельном поле, summary tables

• используем индексы
• желательно не простые индексы, а покрывающие
• SQL_BIG_RESULT и SQL_SMALL_RESULT - таблица в памяти или на диске в зависимости от размера результата
• желательно группировать по целочисленным ключам

По умолчанию MySQL сортирует результат GROUP BY по полям группировки, если нет ORDER BY.

Если порядок не важен, используем ORDER BY NULL. GROUP BY может содержать ASC/DESC.

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

SELECT film.film_id, film.description
  FROM sakila.film
  INNER JOIN (
    SELECT film_id FROM sakila.film
    ORDER BY title LIMIT 50, 5
  ) AS lim USING(film_id);

Всегда используем UNION ALL, при простом UNION выполняется неявный DISTINCT - большие накладные расходы.

• кешируется весь результат SELECT
• проверяется по полному совпадению текста SQL, включая комментарии
• функции типа NOW(), CURRENT_DATE(), пользовательские функции, хранимые процедуры и функции, пользовательские переменные, временные таблицы не кешируются
• лучше всего подходит для сложных запросов, возвращающих мало данных, например COUNT()
• кеширование большого объема данных часто снижает производительность, можно использовать SQL_NO_CACHE
• лучше использовать кеш на клиенте