Таких запросов в день до 1000. Сильно нагружают базу данных.
Вот скрины (кому не удобно читать ниже текст):
1) Сам запрос -
2) План запроса -
3) Профилирование -
Время запроса: 13277.17 ms Память запроса: 0.021 MB Выбрано строк: 27
SELECT c.id, c.asset_id, c.access, c.alias, c.checked_out, c.checked_out_time,
c.created_time, c.created_user_id, c.description, c.extension, c.hits, c.language, c.level,
c.lft, c.metadata, c.metadesc, c.metakey, c.modified_time, c.note, c.params, c.parent_id,
c.path, c.published, c.rgt, c.title, c.modified_user_id, c.version,
CASE WHEN CHAR_LENGTH(c.alias)!= 0 THEN CONCAT_WS(':', c.id, c.alias) ELSE c.id END as slug,COUNT(i.`id`) AS numitems
FROM mqpn1_categories as c
LEFT JOIN (SELECT cat.id as id
FROM mqpn1_categories AS cat JOIN mqpn1_categories AS parent
ON cat.lft BETWEEN parent.lft
AND parent.rgt
WHERE parent.extension = 'com_content'
AND parent.published != 1
GROUP BY cat.id) AS badcats
ON badcats.id = c.id
LEFT JOIN `mqpn1_content` AS i
ON i.`catid` = c.id
AND i.state = 1
WHERE (c.extension='com_content' OR c.extension='system')
AND c.access IN (1,1,5)
AND c.published = 1
AND badcats.id is null
GROUP BY c.id, c.asset_id, c.access, c.alias, c.checked_out, c.checked_out_time,
c.created_time, c.created_user_id, c.description, c.extension, c.hits, c.language, c.level,
c.lft, c.metadata, c.metadesc, c.metakey, c.modified_time, c.note, c.params, c.parent_id,
c.path, c.published, c.rgt, c.title, c.modified_user_id, c.version
ORDER BY c.lft
План SQL-запросов (Explain)
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> system NULL Индекс не используется NULL NULL 0 const row not found
1 PRIMARY c ALL cat_idx,idx_access Индекс не используется NULL NULL 30 Using where; Using temporary; Используется filesort
1 PRIMARY i ref idx_state,idx_catid idx_state 1 const 12810
2 DERIVED parent range cat_idx,idx_left_right cat_idx 153 NULL 2 Using where; Using temporary; Используется filesort
2 DERIVED cat index idx_left_right idx_left_right 8 NULL 30 Using where; Using index; Using join buffer
Профилирование SQL-запросов
Status Duration
starting 0.54 ms
checking permissions 0.01 ms
checking permissions 0.01 ms
checking permissions 0.01 ms
checking permissions 0.01 ms
Opening tables 0.09 ms
System lock 0.35 ms
optimizing 0.04 ms
statistics 0.26 ms
preparing 0.05 ms
Creating tmp table 0.09 ms
executing 0.01 ms
Copying to tmp table 0.15 ms
Sorting result 0.03 ms
Sending data 0.01 ms
removing tmp table 0.02 ms
Sending data 0.01 ms
init 0.24 ms
optimizing 0.04 ms
statistics 0.42 ms
preparing 0.09 ms
Creating tmp table 6.78 ms
executing 0.04 ms
Copying to tmp table 13261.10 ms
25.12.2025, 21:10:01