How to optimize complex sql query?

Question

MySQL

hello, there are 3 tables, the

category_products table connects the category with products, but there are more than 600k records and there are more than 350k in the products table, inner join, left join worked until there were quite a lot of goods, everything would be fine if sorting by name, price is unnecessary and views
here is the sql query itself

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

Answer 1

Add indexes on tables on those columns through which the tables are connected. Add indexes on the columns by which the grouping is performed. Watch the query plan, think, torment Google.

Answer 2

Add an intermediate table with the data in the required form, update the code when updating the main table/trigger/schedule. Denormalization is a normal technique for improving performance.

Answer 3

Get rid of join. Select only
And then for the selected 24 products, select categories. For example, the result of the above query is CREATE TEMPORARY TABLE ... and already its join product_category.
Create indexes on views and related fields.
PS Your request is incorrect. Because you can't SELECT category_products.category_idwhenGROUP BY products.id