How to find a method with a slow query to the database and how to reduce the execution time?

Question

MySQL

Hello.

When monitoring performance, slow queries to the database were revealed, and most of them are pictures.


1. How to find which method loads the database in such a way?

Here's the problematic request:

SELECT
   COUNT(DISTINCT
         BE.ID) as C
FROM
   b_iblock B
   INNER JOIN b_lang L ON
      B.LID = L.LID
   INNER JOIN b_iblock_element BE ON
      BE.IBLOCK_ID = B.ID
WHERE 1 = 1
   AND ((
         EXISTS (
            SELECT
               IBLOCK_ID
            FROM
               b_iblock_site
            WHERE
               IBLOCK_ID = B.ID
               AND (SITE_ID = 's1') ) )
      AND ( (
            BE.ACTIVE_TO >= now()
            OR BE.ACTIVE_TO IS NULL )
         AND (BE.ACTIVE_FROM <= now()
            OR BE.ACTIVE_FROM IS NULL ) )
   AND (BE.ACTIVE = 'Y')
      AND (BE.IBLOCK_ID = '2'))
   AND (BE.WF_STATUS_ID = 1
      AND BE.WF_PARENT_ELEMENT_ID IS NULL )
   AND ( B.ID IN (
         SELECT IBLOCK_ID
         FROM b_iblock_group IBG
         WHERE IBG.GROUP_ID IN (2)
            AND IBG.PERMISSION >= 'R'
            AND (
               IBG.PERMISSION = 'X'
               OR B.ACTIVE = 'Y'
            ))
      OR (
         B.RIGHTS_MODE = 'E'
         AND EXISTS (
            SELECT
               ER.ELEMENT_ID
            FROM
               b_iblock_element_right ER
               INNER JOIN b_iblock_right IBR ON
                  IBR.ID = ER.RIGHT_ID
               INNER JOIN b_user_access UA ON
                  UA.ACCESS_CODE = IBR.GROUP_CODE
                  AND UA.USER_ID = 0
            WHERE
               ER.ELEMENT_ID = BE.ID
               AND IBR.OP_EREAD = 'Y'
         ) ))

There are a lot of nested conditions, how can I reduce this?

Answer 1

Do you really have these pictures on the file? It looks like there is no picture, instead it returns 404, which is returned by Bitrix. I would suggest opening the 404 in the browser, enabling Bitrix debugging (hermitage panel, expand, "Debug" button) and look for the culprit in the output debugging information.
On your screenshot in the statistics, the query execution time is about 0.25-0.35 seconds, but the execution time of the entire page reaches 4-5 seconds. It looks like some heavy component on 404 is running with caching turned off.
And even better - configure NGINX to give pictures, and 404 on pictures too.

Answer 2

profiler.
see query plan.

Answer 3

Shitty request, though. My thoughts on improvements:
COUNT DISTINCT => COUNT(*), GROUP BY
EXISTS => LEFT JOIN
IN (SELECT ...) => INNER JOIN
Not 100% correct.
And, in general, such requests should be split up. Such complex queries are a sign of poor design. It is better to use additional tables for storing states.
Materialized View Template