Explain Query (mysql)
EXPLAIN SELECT
ROUND(SUM(CASE WHEN nb_adults_and_children = 1 THEN 1 ELSE 0 END)/COUNT(user_id),2)*100 AS 'individual',
ROUND(SUM(CASE WHEN nb_adults_and_children BETWEEN 2 AND 3 THEN 1 ELSE 0 END)/COUNT(user_id),2)*100 AS 'between2To3Pers',
ROUND(SUM(CASE WHEN nb_adults_and_children BETWEEN 4 AND 5 THEN 1 ELSE 0 END)/COUNT(user_id),2)*100 AS 'between4To5Pers',
ROUND(SUM(CASE WHEN nb_adults_and_children >= 6 THEN 1 ELSE 0 END)/COUNT(user_id),2)*100 AS 'plus6ToPers',
COUNT(user_id) AS nb_families
FROM (
SELECT
u.user_id,
SUM(u.family_size) AS nb_adults_and_children
FROM
subscriptions s
INNER JOIN `users` u ON (u.user_id = s.user_id AND u.charitable_account = 1)
WHERE
s.active = 1
GROUP BY u.user_id) tmp
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | <derived2> | ALL | 15997 | 100.00 | ||||||
| 2 | DERIVED | s | ref | uniq_sub_per_user, idx_user_id, subscriptions_active_index | subscriptions_active_index | 2 | const | 159978 | 100.00 | Using temporary | |
| 2 | DERIVED | u | eq_ref | PRIMARY, user_email_UNIQUE, payment_profile_id, idx_employ_status, community_representative_group_id, max_rows, idx_plan_id, fk_user_company, idx_never_call, dg_coordinator_company_id_fk, Users_First_Name_Index, Users_Last_Name_Index | PRIMARY | 4 | securelufacom.s.user_id | 1 | 10.00 | Using where |