В чём может быть причина очень долгого выполнения запроса?

Question

[DERBIAGLOBALISTO]

select
				c.id,
				c.name,
				(select
					sum(uc.health) + sum(uc.power) + sum(uc.agil) + sum(uc.intell)
				from users_combat uc
				where id_user in(select cp.id_user from clan_part cp where cp.id_clan = c.id)) as score
			from clan c
			order by score desc
			limit 0, 10

В чём здесь может быть проблема? Заранее благодарен.

Comments

[Stalker_RED]

Большая таблица и нет индексов?
Сделайте explain, угадывать сложно.

[DERBIAGLOBALISTO]

Stalker_RED,

[Stalker_RED]

а если так?

select 
  id,
  name,
  sum(uc.health) + sum(uc.power) + sum(uc.agil) + sum(uc.intell) as score
from clan c
left join clan_part cp ON c.id = cp.id_clan
left join users_combat uc ON uc.id_user = cp.id_user
group by us.id_user
order by score desc

[DERBIAGLOBALISTO]

Stalker_RED, Спасибо

Answer 1

[Rsa97]

Проблема во вложенном подзапросе.

SELECT `c`.`id`, `c`.`name`, `s`.`score`
  FROM (
    SELECT `cp`.`id_clan` AS `id_clan`, 
           SUM(`uc`.`health`) + SUM(`uc`.`power`) 
           + SUM(`uc`.`agil`) + SUM(`uc`.`intell`) AS `score`
      FROM `clan_part` AS `cp` 
      JOIN `users_combat` AS `uc` ON `uc`.`id_user` = `cp`.`id_user`
      GROUP BY `cp`.`id_clan`
      ORDER BY `score` DESC
      LIMIT 0, 10
  ) AS `s`
  JOIN `clan` AS `c` ON `c`.`id` = `s`.`id_clan`

Comments

[DERBIAGLOBALISTO]

Спасибо большое