Beatdown_Info View Update

[farrellw]

Let's optimize some of our views to help the database out.

Beatdown_info view is currently:

select
    `B`.`bd_date` AS `Date`,
    (
    select
        `f3wheaton`.`aos`.`ao`
    from
        `f3wheaton`.`aos`
    where
        (`f3wheaton`.`aos`.`channel_id` = `B`.`ao_id`)) AS `AO`,
    `U1`.`user_name` AS `Q`,
    (
    select
        `f3wheaton`.`users`.`user_name`
    from
        `f3wheaton`.`users`
    where
        (`f3wheaton`.`users`.`user_id` = `B`.`coq_user_id`)) AS `CoQ`,
    `B`.`pax_count` AS `pax_count`,
    `B`.`fngs` AS `fngs`,
    `B`.`fng_count` AS `fng_count`
from
    (`f3wheaton`.`beatdowns` `B`
join `f3wheaton`.`users` `U1` on
    ((`U1`.`user_id` = `B`.`q_user_id`)))
order by
    `B`.`bd_date`,
    (
    select
        `f3wheaton`.`aos`.`ao`
    from
        `f3wheaton`.`aos`
    where
        (`f3wheaton`.`aos`.`channel_id` = `B`.`ao_id`));

We should migrate to this:

select
    `B`.`bd_date` AS `Date`,
     `a`.ao  AS `AO`,
    `U1`.`user_name` AS `Q`,
    `U2`.`user_name` AS `CoQ`,
     `B`.`pax_count` AS `pax_count`,
    `B`.`fngs` AS `fngs`,
    `B`.`fng_count` AS `fng_count`
from `f3devcommunity`.`beatdowns` `B`
LEFT join `f3devcommunity`.`users` `U1` on `U1`.`user_id` = `B`.`q_user_id`
LEFT join `f3devcommunity`.`users` `U2` on `U2`.`user_id` = `B`.`coq_user_id`
LEFT JOIN `f3devcommunity`.`aos` `a` on `a`.`channel_id` = `B`.`ao_id`
order by `B`.`bd_date`, `a`.ao

Efficiency: First Query: It uses subqueries within the SELECT clause to retrieve the AO and CoQ values. Subqueries like these are executed for each row in the result set, which can significantly slow down performance, especially with large datasets.

Second Query: It uses LEFT JOIN operations to retrieve the AO and CoQ values directly. Joins are generally more efficient than subqueries because the database engine can optimize the join operations, leading to better performance.

[farrellw]

Atttendance View Currently vs proposed

Current:

select
    `bd`.`date` AS `Date`,
    `ao`.`ao` AS `AO`,
    `u`.`user_name` AS `PAX`,
    `t1`.`Q` AS `Q`
from
    ((`f3wheaton`.`aos` `ao`
join `f3wheaton`.`users` `u`)
join (`f3wheaton`.`bd_attendance` `bd`
left join (
    select
        `f3wheaton`.`users`.`user_id` AS `user_id`,
        `f3wheaton`.`users`.`user_name` AS `Q`
    from
        `f3wheaton`.`users`) `t1` on
    ((`bd`.`q_user_id` = `t1`.`user_id`))))
where
    ((`bd`.`ao_id` = `ao`.`channel_id`)
        and (`u`.`user_id` = `bd`.`user_id`))
order by
    `bd`.`date` desc,
    `ao`.`ao`;

Proposed:

select
    `bd`.`date` AS `Date`,
    `ao`.`ao` AS `AO`,
    `u`.`user_name` AS `PAX`,
    `q`.`user_name` AS `Q`
from
    `f3wheaton`.`bd_attendance` `bd`
left join `f3wheaton`.`aos` `ao` on `bd`.`ao_id` = `ao`.`channel_id`
left join `f3wheaton`.`users` `u` on `bd`.`user_id` = `u`.`user_id`
left join `f3wheaton`.`users` `q` on `bd`.`q_user_id` = `q`.`user_id`
order by
    `bd`.`date` desc,
    `ao`.`ao`;

[farrellw]

Backblast current vs proposed Current:

select
    `B`.`bd_date` AS `Date`,
    (
    select
        `f3wheaton`.`aos`.`ao`
    from
        `f3wheaton`.`aos`
    where
        (`f3wheaton`.`aos`.`channel_id` = `B`.`ao_id`)) AS `AO`,
    `U1`.`user_name` AS `Q`,
    (
    select
        `f3wheaton`.`users`.`user_name`
    from
        `f3wheaton`.`users`
    where
        (`f3wheaton`.`users`.`user_id` = `B`.`coq_user_id`)) AS `CoQ`,
    `B`.`pax_count` AS `pax_count`,
    `B`.`fngs` AS `fngs`,
    `B`.`fng_count` AS `fng_count`,
    coalesce(`B`.`backblast_parsed`, `B`.`backblast`) AS `backblast`
from
    (`f3wheaton`.`beatdowns` `B`
join `f3wheaton`.`users` `U1` on
    ((`U1`.`user_id` = `B`.`q_user_id`)))
order by
    `B`.`bd_date`,
    (
    select
        `f3wheaton`.`aos`.`ao`
    from
        `f3wheaton`.`aos`
    where
        (`f3wheaton`.`aos`.`channel_id` = `B`.`ao_id`));

Proposed:

select
    `B`.`bd_date` AS `Date`,
    `A`.`ao` AS `AO`,
    `U1`.`user_name` AS `Q`,
    `U2`.`user_name` AS `CoQ`,
    `B`.`pax_count` AS `pax_count`,
    `B`.`fngs` AS `fngs`,
    `B`.`fng_count` AS `fng_count`,
    coalesce(`B`.`backblast_parsed`, `B`.`backblast`) AS `backblast`
from
    `f3wheaton`.`beatdowns` `B`
join `f3wheaton`.`users` `U1` on `U1`.`user_id` = `B`.`q_user_id`
left join `f3wheaton`.`aos` `A` on `A`.`channel_id` = `B`.`ao_id`
left join `f3wheaton`.`users` `U2` on `U2`.`user_id` = `B`.`coq_user_id`
order by
    `B`.`bd_date`,
    `A`.`ao`;

[farrellw]

Completed for all regions.