ALTER TABLE products
ADD FOREIGN KEY (category_id) REFERENCES categories(id);

ALTER TABLE product_descriptions
ADD FOREIGN KEY (product_id) REFERENCES products(id);

CREATE INDEX ct_lang
ON category_descriptions(category_id, lang);

EXPLAIN SELECT
	p.*, pd.*, cd.*
FROM products p
JOIN product_descriptions pd
	ON p.id = pd.product_id
        AND pd.lang = 'eng'
JOIN category_descriptions cd FORCE INDEX (ct_lang)
	ON p.category_id = cd.category_id
        AND cd.lang = 'eng';

--> total = 3 * 1 rows