Here is the second query from your log, looking into others. To help on this, I slightly restructured to get rid of the subquery and turned into an INNER JOIN. Also, to help others not knowing your tables, always qualify your field references as table.column or alias.column such as to know where your level_depth, nleft, nright are coming from. It may help with indexes.
FOR indexes, I would have the following per table.
ps_category, index ON( active, id_category, id_lang, nleft, nright, level_depth )
ps_category_lang, index ON( id_category, id_shop, id_lang )
ps_category_shop, index ON( id_category, id_shop )
ps_category_group, index ON( id_category, id_group )
ps_lang, index ON( id_lang, active )
slightly rewritten query
SELECT
c.id_parent,
c.id_category,
cl.name,
cl.description,
cl.link_rewrite
FROM
ps_category c
INNER JOIN ps_category_lang cl
ON c.id_category = cl.id_category
AND cl.id_shop = 1
AND cl.id_lang = 2
INNER JOIN ps_category_shop cs
ON c.id_category = cs.id_category
AND cs.id_shop = 1
INNER JOIN ps_category_group psg
ON c.id_category = psg.id_category
AND psg.id_group = 1
WHERE
( c.active = 1
OR c.id_category = 2)
AND c.id_category != 1
AND level_depth <= 7
AND nleft >= 350 AND nright <= 351
ORDER BY
level_depth ASC,
cs.position ASC;
Third query. Notice in this case, some of the key elements that are specific are moved to the top, hence I have them in the key index too, THEN the rest in the where clause (mostly readability adjustments, but helps to see "specific" elements for index advantages )
SELECT
c.id_category,
cl.name,
cl.link_rewrite
FROM
ps_category c
LEFT JOIN ps_category_lang cl
ON c.id_category = cl.id_category
AND cl.id_shop = 1
INNER JOIN ps_category_shop category_shop
ON c.id_category = category_shop.id_category
AND category_shop.id_shop = 1
WHERE
c.active = 1
AND cl.id_lang = 2
AND c.nleft between 2 and 350
AND c.nright between 351 and 625
AND c.level_depth > 1
ORDER BY
c.level_depth ASC;
The next query you need to confirm what you really wanted... You have a left-join to the language table, but then add "AND l.active = 1" in the where clause which in-fact would turn it into an INNER JOIN. If you want it truly to a LEFT-JOIN, move the l.active to the join part such as I adjusted here
SELECT
l.id_lang,
c.link_rewrite
FROM
ps_category_lang AS c
LEFT JOIN ps_lang AS l
ON c.id_lang = l.id_lang
AND l.active = 1
WHERE
c.id_category = 324
Hopefully these indexes and sample clarification / readability of queries might help improve your logs. If still problems with others, post as needed.
REVISION FOR FIRST QUERY IN SLOW LOG REPORT
In the first query, it appears you are looking for things out of a specific product shop. However, you are starting the query with the product category and left-joining to products, then to product shops. Since these products will ultimately be associated with a specific category via the where, it creates an INNER JOIN anyhow. I would slightly restructure as below
I would have a single index on ps_product_shop ON ( id_shop, active, visibility, id_product, id_category_default )
SELECT
p.*,
ps.*,
stock.out_of_stock,
IFNULL(stock.quantity, 0) as quantity,
MAX(pas.id_product_attribute) id_product_attribute,
pas.minimal_quantity AS product_attribute_minimal_quantity,
pl.description,
pl.description_short,
pl.available_now,
pl.available_later,
pl.link_rewrite,
pl.meta_description,
pl.meta_keywords,
pl.meta_title,
pl.name,
MAX(image_shop.id_image) id_image,
il.legend,
m.name AS manufacturer_name,
cl.name AS category_default,
DATEDIFF(ps.`date_add`, DATE_SUB(NOW(),INTERVAL 20 DAY)) > 0 AS new,
ps.price AS orderprice
FROM
( select @thisLanguage := 1 ) sqlvars,
ps_product_shop ps
INNER JOIN ps_product p
ON ps.id_product = p.id_product
INNER JOIN ps_category_product cp
ON id_product = cp.id_product
AND cp.id_category = 2
LEFT JOIN ps_product_attribute pa
ON p.id_product = pa.id_product
LEFT JOIN ps_product_attribute_shop pas
ON pa.id_product_attribute = pas.id_product_attribute
AND ps.id_shop = pas.id_shop
AND pas.default_on = 1
LEFT JOIN ps_stock_available stock
ON p.id_product = stock.id_product
AND ps.id_shop = stock.id_shop
AND stock.id_shop_group = 0
AND stock.id_product_attribute = IFNULL(pas.id_product_attribute, 0)
LEFT JOIN ps_product_lang pl
ON p.id_product = pl.id_product
AND ps.id_shop = pl.id_shop
AND pl.id_lang = @thisLanguage
LEFT JOIN ps_image i
ON p.id_product = i.id_product
LEFT JOIN ps_image_shop image_shop
ON i.id_image = image_shop.id_image
AND ps.id_shop = image_shop.id_shop
AND image_shop.cover = 1
LEFT JOIN ps_image_lang il
ON image_shop.id_image = il.id_image
AND il.id_lang = @thisLanguage
LEFT JOIN ps_manufacturer m
ON p.id_manufacturer = m.id_manufacturer
LEFT JOIN ps_category_lang cl
ON ps.id_category_default = cl.id_category
AND cl.id_shop = ps.id_shop
AND cl.id_lang = @thisLanguage
WHERE
ps.id_shop = 1
AND ps.active = 1
AND ps.visibility IN ("both", "catalog")
GROUP BY
ps.id_product
ORDER BY
cp.position ASC
LIMIT
0,8;
As for creating the multiple field index as a single index, example:
CREATE INDEX act_id_lang ON ps_category( active, id_category, id_lang, nleft, nright, level_depth );
give a simple index name... on whatever table... with ALL the keys as they will be effective in the query.
