Как оптимизировать сложный sql запрос?

Question

[Vanya Huk]

здраствуйте, есть 3 таблицы


таблица category_products связывает категорию с продуктами, но там более 600к записей и в таблице products более 350к, inner join, left join работали, пока товаров не стало совсем много, все было бы ничего, если ненужно сортировки по названию, цене и просмотрах

вот сам sql запрос

SELECT  `products`.`id` ,  `products`.`title` ,  `products`.`slug` ,  `products`.`label` ,  `products`.`price` ,  `products`.`old_price` , `products`.`category_id` ,  `products`.`picture_id` ,  `products`.`brand_id` ,  `category_products`.`category_id` AS `pivot_category_id` ,  `category_products`.`product_id` AS  `pivot_product_id` 
FROM  `products` 
INNER JOIN  `category_products` ON  `products`.`id` =  `category_products`.`product_id` 
WHERE  `category_products`.`category_id` =  '2067'
GROUP BY  `products`.`id` 
ORDER BY  `products`.`views` DESC 
LIMIT 24

Comments

[Евгений]

Лучше бы кроме запроса еще привести вывод команд:

\d products
\d category_products
EXPLAIN ANALYZE ваш_запрос;

Answer 1

[V Sh.]

Добавить индексы на таблицы по тем столбцам, через которые идет соединение таблиц. Добавить индексы по столбцам, по которым идет группировка. Смотреть план запроса, думать, мучить гугл.

Comments

[ngame]

Я бы еще к индексам foreign ключи добавил, чтобы можно было работать с моделями, и тянуть записи chunk'ом.

Answer 2

[Антон Антон]

Добавьте промежуточную таблицу с данными в нужном виде, обновляйте кодом при апдейте основной таблицы/триггером/по расписанию. Денормализация - нормальный прием повышения производительности.

Comments

[Vanya Huk]

Антон Антон: а нельзя никак иначе?
там запрос где-то 0.6 секунды в категориях, в которых более 30к товаров

[Антон Антон]

Vanya Huk: можно сначала вывести explain запроса и посмотреть, какая именно его часть занимает столько времени...

[Антон Антон]

может быть достаточно индекса по category+views (один на две колонки) по таблице products

Answer 3

[Boris Köln]

Избавиться от join. Выбирать только

SELECT * FROM products ORDER BY views DESC LIMIT 24

А потом для выбранных 24 товаров выбрать категории. Например, результат вышеуказанного запроса в CREATE TEMPORARY TABLE ... и уже ее join product_category.

Создать индексы по views и связанным полям.

P.S. У вас запрос неправильный. Потому что нельзя SELECT category_products.category_id, когда GROUP BY products.id