Joins Without Indexes

If there are other conditions to the JOIN beyond the primary keys used then those columns should be indexed.  I.e. a:
LEFT JOIN ?:product_descriptions AS pd ON pd.product_id=?:products.product_id AND lang_code='en'.......

The lang_code column in ?:product_descriptions should also be indexed (and it is) to make the JOIN effecient (I.e. not require a full table scan to identify the row(s) of the JOIN).

product_descriptions table has complex key, which includes both product_id and lang_code. Thus, you should not add a special index for lang_code.

I just said the lang_code column was already indexed (as a complex key). Not that a new index should be created.

Indeed. So it seems the most of all 'big' requests do not require new indexes to be created, right?

Hello, guys.

It's impossible to define indexes for all of the possible WHERE and JOIN conditions, because MySQL has complex rules on how to use indexes with sorting and grouping. The order of columns at index definition affects on when the index can be used by MySQL optimizer (depending on query conditions), so an index is often added for a limited set of query conditions.

We also can't add indexes for all of the combinations of columns being used at queries, because this will literally kill performance of queries. Indexes are costly.

We will investigate which indexes we can add for the most common query conditions, but I can't promise that all possible query conditions will be covered by table indexes.

Product search, product features, product pricing are the primary JOINs to look at.

Also what goes into a product listing and the product detail page.

What you don't have control over at all is what JOINs are added by addons.

The OP has done a great job of giving you the query detail. Now all you have to do is analyze it! :-)