Skip to content
Advertisement

SQL multiple left join as new columns performance

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 google@goog.com
4 2 2 apple@apple.com

Expected result after query

id name Phone Email
1 Google 555-444-333 google@goog.com
2 Apple 111-111-111 apple@apple.com

I have a query like this :

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.

Advertisement

Answer

What you need here is a pivot query:

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement