SELECT
cmns.id AS id,
cmns.title AS title,
cmns.cdate AS cdate,
cmns.status AS status,
(SELECT IF((cmns.account_id > 0),(SELECT a.status FROM accounts AS a WHERE a.id = cmns.account_id),cmns.status)) AS common_status,
cmns.account_id AS account_id,
CONCAT(cmns.title,cmns.status,(SELECT IFNULL(SUM(cmpl.damage),0)FROM complaints as cmpl WHERE cmns.id = defendant_id),
(SELECT IFNULL(SUM(refunded),0) FROM complaints WHERE cmns.id = defendant_id ),(SELECT COUNT(*)
FROM complaints as cmpl
INNER JOIN accounts as a ON(cmpl.claimant_id = a.id)
WHERE cmpl.status <> 'deleted'
AND a.status <> 'deleted'
AND cmpl.defendant_id = cmns.id),(SELECT COUNT(*)
FROM comments as com
INNER JOIN complaints as cmpl ON(com.complaint_id = cmpl.id)
WHERE com.status <> 'deleted'
AND cmpl.status <> 'deleted'
AND com.account_id = cmns.account_id)) AS search,(SELECT SUM(damage) FROM complaints WHERE cmns.id = defendant_id ) AS sum_damage,(SELECT SUM(damage) FROM complaints WHERE cmns.id = defendant_id AND refunded > 0 ) AS sum_refunded,(
SELECT COUNT(*)
FROM complaints as cmpl
INNER JOIN accounts as a ON(cmpl.claimant_id = a.id)
WHERE cmpl.status <> 'deleted'
AND a.status <> 'deleted'
AND cmpl.defendant_id = cmns.id
) AS cnt_complaints,
(SELECT COUNT(*)
FROM comments as com
INNER JOIN complaints as cmpl ON(com.complaint_id = cmpl.id)
INNER JOIN accounts as a ON(cmpl.claimant_id = a.id)
INNER JOIN companies as c2 ON(cmpl.defendant_id = c2.id)
WHERE com.status <> 'deleted'
AND cmpl.status <> 'deleted'
AND a.status <> 'deleted'
AND c2.status <> 'deleted'
AND com.account_id = cmns.account_id
) AS cnt_comments
FROM companies as cmns WHERE cmns.status IN ('active') ORDER BY title ASC LIMIT 239500,20;
Если лимит установить в LIMIT 1,20 загружается за 0.4 секунды.
Если лимит установлен в LIMIT 239500,20 то уже за 28.8 секунд.
В базе пока 300 тысяч записей. Но скоро там будет 6-8 млн. записей.
Я думаю заменить что можно на JOIN но как заменить конкатенацию агрегаций на JOIN ума не приложу возможно ли это?