How to optimize query speed using MySQL indexes?

Question

MySQL

There is a monstrous request which it would be good to optimize. There is no way to rewrite the query itself, but adding the necessary indexes would be very helpful.
Here's an example:

p .`id_product` is used here, but as we can see this column does not have an independent index. It is used in a composite :

am I correct in assuming that we need to create an index here specifically for `id_product` ?
Thanks

Answer 1

No, a separate index by id_product will not be used. Why take an index, find values ​​by it and then look for them by the primary key (in innodb, clustering by the primary key), if you can search immediately by the primary key?
Now, if the primary index were id_shop & id_product - then yes, an index on id_product would be useful.