Explain Query (mysql)
EXPLAIN SELECT
c.company_id,
c.name AS comp_fr,
c.short_quote_about_program AS short_quote_about_program_fr,
c.quote_about_program AS quote_about_program_fr,
c.author_quote_about_program AS author_quote_about_program_fr,
c.full_length_mission_statement as full_length_mission_statement_fr,
c.company_url,
c.city AS city_fr,
c.featured_in_carousel,
c.logo_url,
c.carousel_photo,
companiesLang.l_name AS comp_en,
companiesLang.l_city AS city_en,
companiesLang.l_short_quote_about_program AS short_quote_about_program_en,
companiesLang.l_quote_about_program AS quote_about_program_en,
companiesLang.l_author_quote_about_program AS author_quote_about_program_en,
companiesLang.l_full_length_mission_statement as full_length_mission_statement_en,
nb_users_per_org.nb_families
FROM
`companies` c
LEFT JOIN companiesLang ON (companiesLang.company_id = c.company_id AND companiesLang.lang_id = 'en')
LEFT JOIN (
SELECT
users.company_id,
COUNT(users.user_id) AS nb_families
FROM
users
INNER JOIN subscriptions ON (subscriptions.user_id = users.user_id AND subscriptions.active = 1)
WHERE
users.company_id IN (SELECT company_id FROM companies WHERE charitable_partner = 1)
GROUP BY
users.company_id
) nb_users_per_org ON (nb_users_per_org.company_id = c.company_id)
WHERE
c.`charitable_partner` = 1 AND nb_users_per_org.nb_families > 0
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | c | ALL | PRIMARY | 20 | 10.00 | Using where | ||||
| 1 | PRIMARY | companiesLang | ref | fk_companiesLang_companies_id | fk_companiesLang_companies_id | 4 | securelufacom.c.company_id | 1 | 100.00 | Using where | |
| 1 | PRIMARY | <derived2> | ref | <auto_key0> | <auto_key0> | 5 | securelufacom.c.company_id | 218 | 100.00 | ||
| 2 | DERIVED | companies | ALL | PRIMARY | 20 | 10.00 | Using where; Using temporary | ||||
| 2 | DERIVED | users | ref | PRIMARY, fk_user_company | fk_user_company | 5 | securelufacom.companies.company_id | 37278 | 100.00 | Using index | |
| 2 | DERIVED | subscriptions | eq_ref | uniq_sub_per_user, idx_user_id, subscriptions_active_index | uniq_sub_per_user | 4 | securelufacom.users.user_id | 1 | 29.37 | Using where |