How to optimize a query to the database in Bitrix?

Question

SQL

There is an Info Block in which clicks are recorded when going to certain pages.
There are more than 600 thousand entries.
Now, when displaying the elements of this IB in the admin panel, an error appears
https://drive.google.com/file/d/1ysZguqoAHI0vznnrp...
In addition, at the front, there is an output of these data for statistics
. The server admin writes that the server is overloaded and describes This
SELECT FPS0.PROPERTY_46 as PROPERTY_SUMM_VALUE, concat(BE.ID , ':' , 46) as PROPERTY_SUMM_VALUE_ID,DATE_FORMAT(BE.DATE_CREATE, '%d.%m.%Y %H:%i:%s') as DATE_CREATE \ SELECT FPS0.PROPERTY_46 as PROPERTY_SUMM_VALUE, concat(BE.ID , ':' , 46) as PROPERTY_SUMM_VALUE_ID,DATE_FORMAT(BE.DATE_CREATE, '%d.%m.%Y %H:%i:%s') as DATE_CREATE \ 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 INNER JOIN b_iblock_element_prop_s9 FPS0 ON FPS0.IBLOCK_ELEMENT_ID = BE.ID
WHERE 1=1 AND ( (((BE .IBLOCK_ID = '9')))) AND ((((BE.DATE_CREATE > '2017-09-07 14:13:38')))) AND ((((FPS0.PROPERTY_48 = '903022'))) ) ) AND (((BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL)));”
a typical request takes 4 seconds, such in the show proccess thread ~ 30.
explain the request
“id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE B const PRIMARY PRIMARY 4 const 1 1 SIMPLE L const PRIMARY PRIMARY 2 const 1 Using index
1 SIMPLE BE ref PRIMARY,ix_iblock_element_1,ix_iblock_element_4,ix_iblock_element_3,ix_iblock_element_3,ix_iblock_element_code 6_iblock_element_1
1 SIMPLE FPS0 eq_ref PRIMARY PRIMARY 4 bitrix_81.BE.ID 1 Using where”
apparently this happens when they
enter at the front
How to decide that there is no overload, well, errors in the Admin

Answer 1

Optimizing a query is the wrong way to ask a question. To reduce the load on the server based on the information provided by the admin, you must:
- find the Bitrix api that generates this request
- find a place in the site code: a component or just code on the page where this api is called
- see if whether it is called in a loop over an unknown number of elements, keep in mind that the loop may not be obvious if, for example, the call to this api is in a file that is twitching in the ajax loop
- take this api out of the loop, read how to do it in Perfect Code by Steve McConnell is an awesome book that always comes to my rescue in situations like this.
- wrap the cache in the place where this api is called
- check that the cache works as it should