Как оптимизировать запрос с подзапросами с COUNT?

Question

[maksam07]

Добрый день. В плагине для вордпресса есть запрос такого характера:

SELECT *
  ,(SELECT COUNT(comment_system.id) FROM comment_system WHERE itemtype='qqq' AND item_id = ebids.id) AS has_qqq_comment 
  ,(SELECT COUNT(comment_system.id) FROM comment_system WHERE itemtype='www' AND item_id = ebids.id) AS has_www_comment 
  ,(SELECT COUNT(comment_system.id) FROM comment_system WHERE itemtype='eee' AND item_id = ebids.id) AS has_eee_comment
FROM ebids
WHERE status != 'auto'
ORDER BY id DESC
LIMIT 0, 100

Когда используются подзапросы: (SELECT COUNT(comment_system.id) FROM ... ) AS has_qqq_comment
То весь запрос выполняется на 2-4 секунды дольше
В таблице comment_system всего около 50к записей. Индексы на itemtype и item_id стоят вместе и по отдельности.
Можно это как-то оптимизировать?
Без подзапросов запрос выполняется считай моментально

explain

Answer 1

[Rsa97]

DEPENDENT SUBQUERY - это очень плохо. Это значит, что подзапрос вызывается заново для каждой строки из основной таблицы.

SELECT `ebids`.*, IFNULL(`c`.`qqq`, 0) AS `has_qqq_comment`,
       IFNULL(`c`.`www`, 0) AS `has_www_comment`, IFNULL(`c`.`eee`, 0) AS `has_eee_comment`
  FROM `ebids`
  LEFT JOIN (
    SELECT `item_id`, SUM(`itemtype` = 'qqq') AS 'qqq',
           SUM(`itemtype` = 'www') AS 'www', SUM(`itemtype` = 'eee') AS 'eee'
      FROM `comment_system`
      GROUP BY `item_id`
  ) AS `c` ON `c`.`item_id` = `ebids`.`id`
  WHERE `ebids`.`status` != 'auto'
  ORDER BY `ebids`.`id` DESC
  LIMIT 0, 100

Answer 2

[Akina]

SELECT ebids.*
     , SUM(comment_system.itemtype='qqq') AS has_qqq_comment 
     , SUM(comment_system.itemtype='www') AS has_www_comment 
     , SUM(comment_system.itemtype='eee') AS has_eee_comment 
FROM ebids
JOIN comment_system ON comment_system.item_id = ebids.id
WHERE ebids.status != 'auto'
GROUP BY ebids.id
ORDER BY ebids.id DESC
LIMIT 0, 100

Answer 3

[mayton2019]

Вот эта колбаса - по смыслу - group-by с фильтрацией. Попробуй сделать из 3 запросов 1.

,(SELECT COUNT(comment_system.id) FROM comment_system WHERE itemtype='qqq' AND item_id = ebids.id) AS has_qqq_comment 
  ,(SELECT COUNT(comment_system.id) FROM comment_system WHERE itemtype='www' AND item_id = ebids.id) AS has_www_comment 
  ,(SELECT COUNT(comment_system.id) FROM comment_system WHERE itemtype='eee' AND item_id = ebids.id) AS has_eee_comment