Я все еще узнаю о MySQL. Я могу совершать очень простую ошибку, и я готов быть сдержанным здесь...
То, что этот запрос пытается сделать, выбрать главных участников из нашего веб-сайта на основе количества количества обзоров книги и рецепта, которые они сделали.
Я делаю вычисление общего количества в самом SQL-запросе. Запрос является медленным (9 секунд) и не будет определенно масштабировать рассмотрение, что у нас только есть 400 участников и несколько тысяч обзоров до сих пор, и это растет вполне быстро.
Я предполагаю, что это делает полное сканирование таблицы здесь, и что вычисление замедляет его, но я не знаю об альтернативном способе сделать это и любил бы некоторую мудрость.
Вот SQL-оператор:
SELECT users.*, COUNT( DISTINCT bookshelf.ID ) AS titles, COUNT( DISTINCT book_reviews.ID ) as bookreviews, COUNT( DISTINCT recipe_reviews.ID ) AS numreviews, COUNT( DISTINCT book_reviews.ID ) + COUNT( DISTINCT recipe_reviews.ID ) as reviewtotal
FROM users
LEFT OUTER JOIN recipe_reviews ON recipe_reviews.user_id = users.ID
LEFT OUTER JOIN book_reviews ON book_reviews.user_id = users.ID
LEFT OUTER JOIN bookshelf ON users.ID = bookshelf.user_id
GROUP BY users.ID
ORDER BY reviewtotal DESC
LIMIT 8
Вот ОБЪЯСНЕНИЕ:
+----+-------------+----------------+-------+-------------------+-------------------+---------+---------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+-------+-------------------+-------------------+---------+---------------------+------+---------------------------------+
| 1 | SIMPLE | users | index | NULL | PRIMARY | 4 | NULL | 414 | Using temporary; Using filesort |
| 1 | SIMPLE | recipe_reviews | ref | recipe_reviews_fk | recipe_reviews_fk | 5 | users.ID | 12 | |
| 1 | SIMPLE | book_reviews | ref | user_id | user_id | 5 | users.ID | 4 | |
| 1 | SIMPLE | bookshelf | ref | recipe_reviews_fk | recipe_reviews_fk | 5 | users.ID | 13 | |
+----+-------------+----------------+-------+-------------------+-------------------+---------+---------------------+------+---------------------------------+
ОБНОВИТЕ И РЕШЕННЫЙ:
Я понял, и подтвержденный @recursive, что запрос является корнем проблемы. Я получаю Декартовы произведения от этого. Я переписал его как серию подзапросов, и заключительный рабочий код здесь:
SELECT *, bookreviews + recipereviews AS totalreviews
FROM (SELECT users.*,
(SELECT count(*) FROM bookshelf WHERE bookshelf.user_id = users.ID) as titles,
(SELECT count(*) FROM book_reviews WHERE book_reviews.user_id = users.ID) as bookreviews,
(SELECT count(*) FROM recipe_reviews WHERE recipe_reviews.user_id = users.ID) as recipereviews
FROM users) q
Это дает мне результат в миллисекундах. Существуют также способы сделать это с СОЕДИНЕНИЯМИ. Посмотрите, Как добавить вместе результаты нескольких подзапросов? если Вы хотите развить это.
Можно попробовать проверить, есть ли улучшение в удалении модификаторов DISTINCT
. Предположив, что поля DISTINCT
ed все равно являются первичными ключами, это может привести к ненужной работе.
]Индексировать все таблицы на []user_id[
]. Это могло бы легко ускорить запрос на порядки, если бы он еще не был выполнен.[
] Ты пытаешься сделать слишком много вещей с этим запросом. Я вижу проблемы с дизайном вашего db / запроса. Почему у вас есть user_id в book_shelf? Как насчет следующей структуры таблицы[
] [CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT ,
name VARCHAR( 20 ) NOT NULL ,
PRIMARY KEY ( `id` )
)
CREATE TABLE recipe_reviews (
id INT NOT NULL AUTO_INCREMENT ,
review VARCHAR( 20 ),
user_id INT,
PRIMARY KEY (id),
FOREIGN KEY (user_id) references users(id)
)
CREATE TABLE bookshelf (
id INT NOT NULL AUTO_INCREMENT ,
name VARCHAR( 20 ) NOT NULL ,
PRIMARY KEY ( id )
)
CREATE TABLE book_reviews (
id INT NOT NULL AUTO_INCREMENT ,
review VARCHAR( 20 ),
user_id INT,
bookshelf_id INT,
PRIMARY KEY (id),
FOREIGN KEY (user_id) references users(id),
FOREIGN KEY (bookshelf_id) references bookshelf(id)
)
]
[]Если вы хотите объединить по пользователям, вот ваш запрос :[
] [SELECT users.*, COUNT(book_reviews.ID ) as bookreviews, COUNT( recipe_reviews.ID ) AS recipereviews, bookreviews + recipereviews as reviewtotal
FROM users
LEFT OUTER JOIN recipe_reviews ON recipe_reviews.user_id = users.ID
LEFT OUTER JOIN book_reviews ON book_reviews.user_id = users.ID
GROUP BY users.ID
ORDER BY reviewtotal DESC
]
[]Вы также можете объединить как по пользователям, так и по книгам, тогда включать в нее system_reviews не имеет смысла.[
] []PS: вам не нужен DISTINCT, т.к. у вас есть ключи, которые позаботятся об этом.[
].] Почему бы просто не сохранить количество отзывов на пользователя в виде столбца в таблице пользователей? Каждый новый отзыв, который делает пользователь, должен также требовать увеличения значения количества отзывов на единицу.[
] []Например:[
] [user_id user_name number_of_reviews
1 bob 5
2 jane 10
]
[]Боб вводит новый отзыв, и вы увеличиваете его число до 6:[
] [review_id user_id review_text
16 1 "Great!"
user_id user_name number_of_reviews
1 bob 6
2 jane 10
]
[]Теперь вы можете просто получить 5 лучших рецензентов следующим образом:[
] [SELECT * FROM users ORDER BY number_of_reviews DESC LIMIT 5
] Нужно создавать индексы по идентификатору user_id (желательно, по возможности, кластерные индексы).
Вы уверены, что сделали это? Помните, что наличие постороннего ключа автоматически не генерирует индекс по этому ключу.
Если вы соединяете 4 B-дерева по 1k строк каждый, то это, конечно, должно занять не 9 секунд, а несколько миллисекунд.
Длительное время выполнения указывает на то, что вы выполняете сканирование таблиц для каждого пользователя.
Я уверен, что это правильный ответ.
Ваш запрос в порядке, за исключением того, что вы дважды ОТКАЗЫВАете свои отзывы, замените второй счет на просмотры книг и нумеропередачи.
Я часто обнаруживаю, что создание временной таблицы меньшего размера из таблицы большего размера будет иметь заметные преимущества в скорости.
Итак, основной процесс:
для таких возможностей всегда полезно работать с каким-нибудь кэшированием....
Это уже может помочь создавать суммы для всех пользователей на ночной основе и хранить эти суммы у пользователя. Это очень поможет и ускорит поиск.
Вам также следует как-то кэшировать этот запрос как минимум на минуту или пять, так как вы будете выполнять один и тот же запрос независимо от того, кто вошел в систему.