Optimization of a query on a table that represents a weak entity type

I've been trying to figure this problem out for a while now, so I decided I'll try my luck here.
I have a very complex online shop DB structure (multiple shops in one database, hundreds of thousands of products) and I'm querying MySQL to return products from a specified category. Products are in a many-to-many (M:N) relationship to categories, so I have tables product, category and product_category (which stands for a weak entity type).

Table schemas are as follows:

``
CREATE TABLE product (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) COLLATE utf8_czech_ci NOT NULL,
description text COLLATE utf8_czech_ci NOT NULL,
date timestamp NOT NULL DEFAULT current_timestamp(),
date_edited timestamp NOT NULL DEFAULT current_timestamp(),
visibility tinyint(1) NOT NULL DEFAULT 1,
main_slider tinyint(1) NOT NULL DEFAULT 0,
bought int(11) NOT NULL DEFAULT 0,
price_action int(11) NOT NULL DEFAULT 0,
quantity_action int(11) NOT NULL DEFAULT 0,
duration_action timestamp NOT NULL DEFAULT current_timestamp(),
new tinyint(1) NOT NULL DEFAULT 1,
type int(2) NOT NULL DEFAULT 0,
origin_url varchar(250) COLLATE utf8_czech_ci DEFAULT NULL,
origin_price int(11) NOT NULL DEFAULT 0,
origin_price_dph int(11) NOT NULL DEFAULT 0,
rating int(11) NOT NULL DEFAULT 0,
ratingcount int(11) NOT NULL DEFAULT 0,
facebook_flag tinyint(1) DEFAULT 0,
salebot tinyint(1) NOT NULL DEFAULT 0,
shop_supplier_id int(11) NOT NULL,
shop_id int(11) NOT NULL,
product_global_id int(11) DEFAULT NULL,
prioritize tinyint(1) NOT NULL DEFAULT 0,
free_delivery tinyint(1) NOT NULL DEFAULT 0,
PRIMARY KEY (id),
UNIQUE KEY product_global_id_2 (product_global_id,shop_id),
KEY shop_id (shop_id),
KEY shop_supplier_id (shop_supplier_id),
KEY product_global_id (product_global_id),
KEY rating (rating),
KEY date (date),
KEY quantity_action (quantity_action),
KEY duratio

LEFT JOIN image .. AND (image.id IS NOT NULL). If you don't want products without images, then INNER JOIN image and leave out the AND image.id ... criteria. Same with LEFT JOIN product_category, if you want a not-null category in the WHERE, don't use a LEFT JOIN, [INNER] JOIN is what you are really requesting.

Ordering with RAND() is probably also a cause of slowness.

Your subqueries on variant* availability should be things you try to use window functions for in later MySQL versions.

Try to incorporate the all categories into the query and get the results once rather than many times.

Also maybe try to cache this query result out of the database so you don't need to do it as frequently.

StackExchange Database Administrators Q#221004, answer score: 2