SQL multiple left join as new columns performance-CodePudding

I have 3 tables :

account

id	name
1	Google
2	Apple

custom_field

id	name
1	Phone
2	Email

custom_field_submission

id	custom_field_id	entity_id	value
1	1	1	555-444-333
2	1	2	111-111-111
3	2	1	[email protected]
4	2	2	[email protected]

Expected result after query

id	name	Phone	Email
1	Google	555-444-333	[email protected]
2	Apple	111-111-111	[email protected]

I have a query like this :

SELECT
a.id,
a.name,
phone.value as phone,
email.value as email

FROM account a

LEFT JOIN ( 
  SELECT DISTINCT custom_field_submission.value, custom_field_submission.entity_id
  FROM custom_field_submission
  WHERE custom_field_submission.custom_field_id = 1) AS phone 
ON phone.entity_id = a.id

LEFT JOIN ( 
  SELECT DISTINCT custom_field_submission.value, custom_field_submission.entity_id
  FROM custom_field_submission
  WHERE custom_field_submission.custom_field_id = 2) AS email 
ON email.entity_id = a.id

In the reality I have 20 custom fields for 10 000 accounts. Where I run the query It is very slow (3-4 seconds)

Do you have an idea to manage optimize this ?

Thanks.

CodePudding user response：

What you need here is a pivot query:

SELECT
    a.id,
    a.name,
    MAX(CASE WHEN cf.name = 'Phone' THEN cfs.value END) AS Phone,
    MAX(CASE WHEN cf.name = 'Email' THEN cfs.value END) AS Email
FROM account a
LEFT JOIN custom_field_submission cfs
    ON cfs.entity_id = a.id
LEFT JOIN custom_field cf
    ON cf.id = cfs.custom_field_id
GROUP BY
    a.id,
    a.name;