Is this query supposed to be slow?

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.

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.

(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.)