mysql - how can i optimize performance with this query? -

I have three tables, users, activities and purchases.

The user has many activities and purchases, 4 types of activities.

I have to ask users like this:

  [{user_id: 1, // activities from post_count: 2, updated_count: 3, print_count: 4, Share_count: 5, // calculation from purchase: 6}, ...]  

I use this SQL:

  SELECT u.id, Post .post_count, updated.update_count, print.print_count, share.share_count, users post.purchase_count as U LEFT (Activities where USER_ID selects activity_type = 1 group from POST_COUNT as user_id, activity_type, count ( *)). Id = post.user_id LEFT JOIN (SELECT user_id, activity_type, count (*) activities as updated_count WHERE activity_type = 2 GROUP by user_id) updated on u.id = updated.user_id LEFT JOIN electronic user_id, activity_type From the activities of count (*), print_count where activity_type = 3 by group user_id) include u.id = print.user_id print left (select user_id, activity_type, counting (*) as the form SHARE_COUNT Where activity_type = 4 user_id by group uidiid = share.user_id (Share with user ID, URL is purchased as UNID, count (*)) Click on UID = Purchase. User ID =  

How can I optimize performance with this query?

---

I modified it on my query, then it works.

  Select user as UID.SUM (if ((activities.activity_type = 1), 1.0), as a postcount, SM (IF (activities. Print_count, SUM (IF (activities.activity_type = 4), 1.0, as ACT_type = 2), 1,0)) AS update_count, SUM (IF ((activities.activity_type = 3), 1,0)) )), As per the number of shares, IFNULL (purchase.count, 0) exclude purchase activities from consumers = Users.id LEFT JOIN (SELECT user_id, count (*) by user_id by the group purchased by AS users Users.id = users.id = buy.user_id GROUP by users.id  

current In you run the table of activities 4 times - it can be added to one :

  SELECT users.id AS User_id, SUM (iff (activites.activity_type = 1,1,0)) as postcode, SUM (IF (activites.activity_type = 2,1) , 0)) as update_count, print_count as SUM (if (activites.activity_type = 3,1, 0)), SUM ((activites.activity_type if = 4,1,0)) as SHARE_COUNT, IFNULL (COUNT) (purchases.id), 0) Include activities.user_id = users.id on purchase_count interiors from users Include purchases at activities LEFT expense. User.id.group users.id