Better MySQL Syntax for my query

Question

HI I have the following query:

SELECT PAS_User.user_user_id, 
PAS_User.user_city, 
PAS_User.user_company, 
PAS_User.user_country, 
PAS_User.user_account_type, 
PAS_User.user_account_premium, 
PAS_User.user_sign_up_date, 
PAS_User.user_first_name, 
PAS_User.user_last_name, 
PAS_User.user_avatar_url, 
PAS_User.user_cover_image_url, 
PAS_User.user_bio, 
PAS_User.user_sector, 
PAS_User.user_job_type, 
(SELECT COUNT(*) FROM `PAS_Follow` WHERE `folw_follower_user_id`=:sid) AS user_following_count,
(SELECT COUNT(*) FROM `PAS_Follow` WHERE `folw_followed_user_id`=:sid) AS user_followed_count,
(SELECT COUNT(*) FROM `PAS_Post` WHERE `post_user_id`=:sid) AS user_post_count, 
(SELECT COUNT(*) FROM `PAS_Follow` WHERE `folw_follower_user_id`=:sid AND `folw_followed_user_id`=:cid) AS user_this_user_is_following, 
(SELECT COUNT(*) FROM `PAS_Follow` WHERE `folw_followed_user_id`=:cid AND `folw_follower_user_id`=:sid) AS user_this_user_is_followed 
FROM PAS_User 
WHERE `PAS_User`.`user_user_id`=:sid

Which is designed to get counts from other tables for a profile page and the basic user details where :sid = 1 and :cid = 2.

The question is, is there any better way of achieving this in perhaps a smaller query or in a cleaner way?

The tables used are

PAS_User , PAS_Follow & PAS_Post

Thanks

Justin

One can never be too verbose. While it's technically not needed here it doesn't cost any performance. — Niels Keurentjes
– Niels Keurentjes, Commented Jun 3, 2013 at 14:13

Niels Keurentjes · Accepted Answer · 2013-06-03 13:53:37Z

1

You should denormalize all those counter fields and only update them when a user posts something, or presses a Follow button. Your current query is going to blow up your database server in the foreseeable future if your website gets actual active users.

answered Jun 3, 2013 at 13:53

Niels Keurentjes

42.1k9 gold badges103 silver badges138 bronze badges

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

2 Comments

Justin Erswell Over a year ago

I thought about that however the query is designed so that as a user arrives at a profile the information is live and realtime however I will look into Denormailsation

Niels Keurentjes Over a year ago

It's the only possible optimization, and if you read the linked page you should pay extra attention to the last line: "Denormalization techniques are often used to improve the scalability of Web applications". That's what it's for - preventing server problems and speeding up queries, in situations where fast responses are of critical importance.

Gordon Linoff · Accepted Answer · 2013-06-03 14:31:42Z

I'm going to be honest. I don't really like nested select statements (select within select). However, in your case, the alternative may be uglier and prone to performance errors. The temptation is to do something like:

select . . .
from PAS_User u left outer join
     (select folw_foller_user_id, count(*) as user_following_count
      from PAS_Follow pf
      group by folw_foller_user_id
     ) pf
     on pf.folw_foller_user_id = u.user_user_id left outer join
     . . .

Fine. But this will perform much worse than your original query, because of the outside filter on user_user_id. To fix this, you would repeat the condition in the subquery:

select . . .
from PAS_User u left outer join
     (select folw_foller_user_id, count(*) as user_following_count
      from PAS_Follow pf
      where folw_foller_user_id = :sid
      group by folw_foller_user_id
     ) pf
     on pf.folw_foller_user_id = u.user_user_id left outer join
     . . .

Or even:

select . . .
from PAS_User u cross join
     (select count(*) as user_following_count
      from PAS_Follow pf
      where folw_foller_user_id = :sid
     ) pf
     on pf.folw_foller_user_id = u.user_user_id left outer join
     . . .

And I might even argue that repeating the condition in one subquery would be good. I cannot make that argument for five subqueries.

To make this clear, the best syntax is to user window functions:

select . . .
       count(*) over (partition by folw_fllower_user_id) as user_following_count,
       . . .

Alas, MySQL does not support window functions. A very reasonable replacement, in my opinion, are subqueries (which would normally be correlated). This supports your initial syntax.

Collectives™ on Stack Overflow

Better MySQL Syntax for my query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related