optimize SQL query with multiple JOIN

Question

how to optimize this query and increase its performance,

SELECT user.id,user.name,profile.info,score.amount
FROM user
LEFT JOIN profile ON profile.user_id = user.id AND profile.type = 'language'
LEFT JOIN score ON score.user_id = user.id AND score.type = 'total'
WHERE email = '[email protected]'

return results:

[id]       =>    1060225
[name]     =>    john
[info]     =>    En
[ammount]  =>    533

return results 2:

[id]       =>    1022805
[name]     =>    karin
[info]     =>    
[ammount]  =>    11

tables:

users table

id     name         email
1      john      [email protected]
2      karin     [email protected]
3      Tom       [email protected]
4      kit       [email protected]

profile table

id     user_id       type          info
1        1          is_admin       true
2        1          language        En
3        1          active         true
4        2          is_admin       false
1        1          like           null
2        2          favorite       null
3        3          is_admin       false
4        2          experience       4

score table

id     user_id     type     amount
1         1        daily      33
2         1        total      533
3         2        total      11
4         3        daily      44

thanks,

Use explain ... to find out where you need an index.

Jens
– Jens

2014-08-16 07:42:52 +00:00
Commented Aug 16, 2014 at 7:42 — Jens
– Jens, Commented Aug 16, 2014 at 7:42
@Jens where to put "explain" in the query

Ted
– Ted

2014-08-16 07:48:13 +00:00
Commented Aug 16, 2014 at 7:48 — Ted
– Ted, Commented Aug 16, 2014 at 7:48
at the beginning. explian select ...

Jens
– Jens

2014-08-16 09:50:32 +00:00
Commented Aug 16, 2014 at 9:50 — Jens
– Jens, Commented Aug 16, 2014 at 9:50

Gordon Linoff · Accepted Answer · 2014-08-16 11:25:09Z

2

For this query:

SELECT u.id, u.name, p.info, s.amount
FROM user u LEFT JOIN
     profile p
     ON p.user_id = u.id AND p.type = 'language' LEFT JOIN
     score s
     ON s.user_id = u.id AND s.type = 'total'
WHERE u.email = '[email protected]';

(I just added table aliases to make the query more readable and to clarify where the columns come from.)

Try the following indexes:

create index idx_user_email_id_name on user(email, id, name);
create index idx_profile_userid_type on profile(user_id, type);
create index idx_score_userid_type on score(user_id, type);

answered Aug 16, 2014 at 11:25

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Barmar · Accepted Answer · 2014-08-16 07:46:03Z

1

You should give the profile and score tables composite indexes on (type, user_id). I'm not sure whether it will be better to have type or user_id first in the indexes, you should try each of them and see which is better; you can use EXPLAIN to compare the execution plans for this.

answered Aug 16, 2014 at 7:46

Barmar

789k57 gold badges555 silver badges669 bronze badges

Comments

nothingisnecessary · Accepted Answer · 2014-08-16 07:46:18Z

1

You could try creating indexes on the join columns (if not there already), and using subqueries instead of joins:

SELECT 
  id,
  name,
  (select info from profile where user_id = user.id AND type = 'language') as "info", 
  (select amount from score where user_id = user.id and type = 'total') as "amount"
FROM user
WHERE email = '[email protected]'

Other tips: Check the execution plan, add indexes to avoid full table scans where appropriate, denormalize tables by duplicating some data if necessary or using views.

answered Aug 16, 2014 at 7:46

nothingisnecessary

6,26138 silver badges64 bronze badges

4 Comments

Ted Over a year ago

when try subquries just took same time as join

nothingisnecessary Over a year ago

Is it running slow enough to cause problems? other things you could do is implement caching in your application (for user id and name, for example) instead of querying from the database each time. You could also move the info and amount columns into the user table, or duplicate them on update. If you've verified that indexes are being used there's still several things you could try, including: hardware upgrades, lookup tables, app-level caching, full-text index, etc. Make sure user.email is indexed if that is what you search on. Try using connection pooling to reduce startup/teardown time..

Ted Over a year ago

but duplicate tables columns is not best thing to do ? isn't it?

Gordon Linoff Over a year ago

@nothingisnecessary . . . This is not necessarily the same query. The first could return multiple rows for a given user if there are multiple matches in either table.

Collectives™ on Stack Overflow

optimize SQL query with multiple JOIN

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related