fast-je
@fast-je
Пишу на php.

Как эффективно и быстро посчитать древовидную структуру через mysql?

Версия mysql 5.7.
В чем суть вопроса. У меня есть таблица tb_users c вот такой структурой.
CREATE TABLE tb_users (
id int not null unsigned primary key,
username varchar(20),
id_referer int unsigned INDEX
);

Думаю не нужно объяснять что такое рефер и реферал.

Мне нужно посчитать кол-во рефералов для 3 уровней для каждого юзера.

Я создал вот такой запрос

EXPLAIN SELECT `tb1`.*, COUNT(`t3`.`id`) as `one_referals`, count( `t4`.`id`) as `two_referals`, count( `t5`.`id`) as `three_referals`
FROM `tb_users` `tb1`
LEFT JOIN `tb_users` `t3` ON `t3`.`id_referer` = `tb1`.`id`
LEFT JOIN `tb_users` `t4` ON `t4`.`id_referer` = `t3`.`id`
LEFT JOIN `tb_users` `t5` ON `t5`.`id_referer` = `t4`.`id`
GROUP BY `tb1`.`id`
LIMIT 0, 20



Тратится на него очень много кастов.
Добавим в запрос EXPLAIN format=json

И получаем вот такой результат.

Очень много кастов и у меня есть еще более тупой запрос который очень много джойнит таблиц.

Ужасный большой запрос

SELECT `still_socialseti`.`youtube_video`, `still_socialseti`.`youtube_podp`, `still_socialseti`.`youtube_like`, `still_socialseti`.`youtube_money`, `still_socialseti`.`youtube_money_rekl`, `still_socialseti`.`vk_groups`, `still_socialseti`.`vk_repost`, `still_socialseti`.`vk_likes`, `still_socialseti`.`vk_money`, `still_socialseti`.`vk_money_rekl`, COUNT( `be`.`id`) `num_chs_isp`, COUNT( `br`.`id`) as `num_chs_r`, COUNT( `bw`.`id`) as `num_white`, `tb1`.*, `us`.`profit_ref_ad`, `us`.`profit_ref_1`, `us`.`profit_ref_2`, `us`.`profit_ref_3`, COUNT( `ca`.`id_user`) AS count_advertiser, COUNT(`be2`.`id`) `task_bl_u`, COUNT(`t3`.`id`) as `one_referals`, count( `t4`.`id`) as `two_referals`, count( `t5`.`id`) as `three_referals`
FROM `tb_users` `tb1` 
LEFT JOIN `still_socialseti` ON `still_socialseti`.`idu` = `tb1`.`id` 
LEFT JOIN `users_statistics` `us` ON `tb1`.`id`=`us`.`id_user`
LEFT JOIN `still_block_earnings` `be` ON `be`.`idb` = `tb1`.`id`
LEFT JOIN `still_block_reklam` `br` ON `br`.`idb` = `tb1`.`id`
LEFT JOIN `still_block_white` `bw` ON `bw`.`idb` = `tb1`.`id`
LEFT JOIN `users_statistics` `ca` ON `ca`.`id_user` = `tb1`.`id` AND `ca`.`money_spent_total` > 0
LEFT JOIN `still_block_earnings` `be2` ON `be2`.`idu` = `tb1`.`id`
LEFT JOIN `tb_users` `t3` ON `t3`.`id_referer` = `tb1`.`id`
LEFT JOIN `tb_users` `t4` ON `t4`.`id_referer` = `t3`.`id`
LEFT JOIN `tb_users` `t5` ON `t5`.`id_referer` = `t4`.`id`
GROUP BY `tb1`.`id`
ORDER BY `us`.`profit_ref_1` DESC LIMIT 0, 20



Касты для второго
Выполняется порядка 9 секунд это ужас.
Индекс везде есть.

Я думаю тут дело не в индексах, а в неправильном решении задачи.
Что-то тут явно делаю не так и не пойму что, и как сделать первый и второй запрос более быстрым и с наименьшими затратами.
Думаю что обход и подсчет рефералов как минимум неправильный.

tb_users = 70к записей
users_statistics = 70к записей.
still_block_earnings = 2к записей.
still_socialseti = 50к записей.
still_block_reklam = 1к записей.
still_block_white = 1к записей.
  • Вопрос задан
  • 160 просмотров
Решения вопроса 3
@MaximaXXl
Я не очень помню когда в MySQL появилась WITH RECURSIVE, если у Вас заработает - это Ваше
И брошу пример как организовывал рекурсию на старых MySQL, надеюсь разберетесь
select  id,
        name,
        parent_id 
from    (select * from products
         order by parent_id, id) products_sorted,
        (select @pv := '2') initialisation
where   find_in_set(parent_id, @pv)
and     length(@pv := concat(@pv, ',', id))
Ответ написан
serginhold
@serginhold
тебе нужно выбрать структуру хранения дерева https://qna.habr.com/answer?answer_id=391617#answe...
Ответ написан
xmoonlight
@xmoonlight
https://sitecoder.blogspot.com
+3 колонки к каждому пользователю: в каждом поле - храните суммарное количество рефералов на 1-ом, на 1-м и 2-м, и на 1, 2 и 3-м уровне.
И не забывайте их обновлять при реге нового реферала.
При выборке - ничего не нужно будет считать!)
Ответ написан
Пригласить эксперта
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы