How to optimize queries in mysql?

Question

MySQL

There are requests that, at a lower load, are executed in less than a second, then, as there is a certain load of 300-400 users online, they start to be executed from 5 seconds to 120s+
Request No. 1

SELECT count(DISTINCT product.id)
FROM `product`
JOIN `city` ON city.id=product.city_id
JOIN `company` ON company.id=product.company_id
WHERE ((((`product`.`id` in (
          select product_id from product_to_city
          join city on (city.id = product_to_city.city_id)
          where city.id = 104 or city.parent_id = 104
        )))) AND (`product`.`publish` = 1 AND `product`.`archive` != 1 AND `product`.`delete` != 1));

Request #2

SELECT count(*)
FROM (SELECT `product`.`id`, `product`.`position`, `product`.`description`, `product`.`city_id`,  `product`.`price`,  `product`.`company_id`, `company`.`company_type`, `city`.`name` AS `city_name`
FROM `product_to_tag` `p2t`
JOIN `product` ON product.id = p2t.product_id
JOIN `product_to_city` `ptc` ON product.id = ptc.product_id
JOIN `city` `c` ON c.id = ptc.city_id
LEFT JOIN `city` `city` ON city.id=product.city_id
LEFT JOIN `company` ON company.id=product.company_id
WHERE ((((`product`.`id` in (
          select product_id from product_to_city
          join city on (city.id = product_to_city.city_id)
          where city.id = 1 or city.parent_id = 1
        )))) AND (`product`.`publish` = 1 AND `product`.`archive` != 1 AND `product`.`delete` != 1)) AND (product.id !=  513568) AND (c.id = 1 or c.parent_id = 1) AND ((`p2t`.`tag_id` IN (14, 30, 37, 500)))
GROUP BY `product`.`id`
ORDER BY `product`.`timestamp_update` DESC) as count;

in logs of long queries after the second query even simple queries start to execute slowly
# Query_time: 256.971239 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
UPDATE product SET `view_count` = `view_count` + 1 WHERE id = '2797283';
# Query_time: 554.572351 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
UPDATE product SET `view_count` = `view_count` + 1 WHERE id = '2156047';
# Query_time: 646.604457 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
UPDATE product SET `view_count` = `view_count` + 1 WHERE id = '2104649';
how else to optimize? All indexes are placed

Answer 1

Try without subqueries:

SELECT count(DISTINCT product.id)
    FROM `product`
    JOIN `product_to_city` ON `product`.`publish` = 1 
        AND `product`.`archive` != 1 AND `product`.`delete` != 1
        AND `product_to_city`.`product_id` = `product`.`id`
    JOIN `city` AS `c` ON (`c`.`id` = 104 OR `c`.`parent_id` = 104)
        AND `c`.`id` = `product_to_city`.`city_id`
    JOIN `city` ON `city`.`id` = `product`.`city_id`
    JOIN `company` ON `company`.`id` = `product`.`company_id`

Answer 2

caching, procedurals, indexes