Is this query supposed to be slow?

Question

Select * from (select tmp.userid userid, tmp.username ,tmp.likescore+tmp.commentscore+tmp.sharescore+tmp.tagscore+tmp.userscore+tmp.lsharescore+tmp.friendscore as score from 
    (
    SELECT u.userid, u.username, 
    (select 10*count(1) from b_likes l where l.userid = u.userid) likescore,
    (select 15*count(1) from b_comments c where c.userid = u.userid) commentscore,
    (select 25*count(1) from b_page_shares s where s.userid = u.userid  ) sharescore ,
    (select 20*count(1) from b_tags t where t.userid = u.userid ) tagscore,
    (select count(1)*10 score from (
                            Select Distinct friendid,userid
            from b_friends f ) a
            where a.userid=u.userid ) friendscore,
    (select 25*count(1) from b_shares ls where ls.userid = u.userid ) lsharescore,
    (SELECT (100 * count(rightflag)) 
            FROM `b_scores` s
            WHERE  rightflag='1' 
            and level_no!=''  
            and u.userid=s.userid) userscore
    FROM `b_users_data` u) tmp) tmp2 where tmp2.score != 0
    order by tmp2.score desc, tmp2.username asc  limit 10;

The slow query log says it took 72 seconds and 12.7k rows were examined. Apart from id, no other columns are indexed in any table.

Answer 1

Yes, it is supposed to be slow. Why?

• There are multiple subqueries:
  • 3 levels of nested derived tables
  • 7 inline correlated subqueries that each join to a different table, using the userid which as you say is not indexed. One of the subqueries has a further nested derived table in its FROM clause.
• There are WHERE conditios on un-indexed columns
• There is an ORDER BY clause - it always adds one more operation
• There is a DISTINCT quantifier - filtering out duplicates is a costly operation
• There are some calculations being made

It really looks very bizarre. I guess it can be rewritten to a neat form using JOINs and it would run much faster. You would have to post the database structure and the result you want to achieve.

Answer 2

(not a complete answer, but a comment that needs formatting)

Select  *
    from  
      ( SELECT  tmp.userid ...
      ) tmp2
    where  tmp2.score != 0
    order by  tmp2.score desc, tmp2.username asc
    limit  10;

can be simplified to:

( SELECT  tmp.userid ...
      HAVING tmp2.score != 0
)
order by  score desc, username asc
limit  10;

(The main "real answer" is to add suitable INDEXes.)