MySQL Simple Query Optimization

Question

У меня есть следующий запрос:

SELECT
  b.item_name,
  COUNT(distinct c.user_id) AS total_count,
  AVG(c.item_rating) AS avg_rating
FROM       item_ratings as c
INNER JOIN items AS b ON b.item_id = c.item_id
INNER JOIN users AS u ON u.user_id = c.user_id
WHERE item_active = 1 AND u.user_valid = 1
GROUP BY c.item_id

Этот запрос выполняется в течение 500 секунд для высокооптимизированной базы данных - не уверен, что происходит.

индексирование

item_ratings - item_user_id, (item_id, user_id), item_rating, item_id
users - user_id, user_valid
items - item_id (primary), item_search (item_id, item_name), item_r (parent_id, item_id, item_active) 

Размер стола

Таблица item_ratings близка к 5 миллионам записей, а таблица элементов - около 200 тыс., А пользователи - около 250 тыс.

объяснять

Похоже, что запрос объяснения выполняет сортировку таблицы по элементам (возвращая все 200 тыс. Строк), даже несмотря на наличие индекса item_active. Другие таблицы (item_ratings и user) используют правильный индекс.

ОБНОВЛЕНИЕ

ПОЛНОЕ ОБЪЯСНИТЬ

id  select_type     table   type    possible_keys   key     key_len     ref       rows  Extra
1   SIMPLE  b   ALL     PRIMARY,item_id, item_search, item_r    NULL    NULL    NULL    218419  Using where; Using temporary; Using filesort
1   SIMPLE  c   ref     item_user_id ,user_id, item_id  4   myDB.b.item_id  29  Using where
1   SIMPLE  u   eq_ref  PRIMARY,user_valid,user_id  PRIMARY     4   myDB.c.user_id  1   Using where

Аппаратное обеспечение Это выделенный сервер MySQL, работающий под управлением Ubuntu 10.10 с 16 ГБ ОЗУ. Таблицы работают под управлением MyISAM.

Какие-либо предложения?

Answer 1

Ты прав. Этот запрос не должен занимать восемь минут. Одна возможность состоит в том, что индексы фактически ухудшают запрос, потому что запрос хочет выполнить полное сканирование таблицы. Прежде чем обойти их, я предлагаю следующее:

Предположительно, таблица пользователей и элементов имеет различный идентификатор. Кроме того, предположительно, у пользователя есть только один рейтинг для данного элемента. Если это так, вы можете удалить счетчик и заменить его счетчиком:

SELECT b.item_name, COUNT(c.user_id) AS total_count, AVG(c.item_rating) AS avg_rating
FROM item_ratings as c INNER JOIN
     items AS b
     ON b.item_id = c.item_id INNER JOIN
     users AS u
     ON u.user_id = c.user_id
WHERE item_active = 1 AND u.user_valid = 1
GROUP BY c.item_id 

Во-вторых, нет индекса "is_active". Индекс включен (parent_id, item_id, item_active). Ваш запрос не использует parent_id, поэтому этот индекс не будет использоваться.

В-третьих, похоже, что он проходит через индекс предметов из-за агрегации. Поскольку вы, кажется, хотите item_name вместо item_id, я бы предложил изменить группу на:

group by c.item_name

Это может позволить ему создать лучший план запроса.

Answer 2

Даже с индексом в поле item_active, запрос все еще был ОЧЕНЬ медленным. Поскольку этот запрос выполняется только один раз в день, я нашел другое решение, которое, надеюсь, можно будет использовать для других пользователей.

Я просто вытащил список активного пива только с помощью этого запроса:

SELECT b.beer_name
FROM items as b
WHERE b.item_active = 1

Затем в каждой строке я просматривал и получал рейтинг и средний рейтинг для каждого активного элемента, например:

SELECT COUNT(DISTINCT c.user_id) AS total_count, AVG(c.item_rating) AS avg_rating
FROM item_ratings as c 
INNER JOIN users AS u ON u.user_id = c.user_id
WHERE item_active = 1 AND u.user_valid = 1 and b.item_id = @item_id

Где @item_id - это item_id из цикла PHP, который я написал. После этого я беру результаты этого и помещаю это в стол для запросов. Это решение прекрасно работает для меня, потому что выполнение этих небольших запросов занимает менее секунды и может быть выполнено в пакетном формате в нерабочее время без блокировки других таблиц.

Спасибо всем за их предложения и помощь!