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
useridwhich as you say is not indexed. One of the subqueries has a further nested derived table in its
- There are
WHEREconditios on un-indexed columns
- There is an
ORDER BYclause – it always adds one more operation
- There is a
DISTINCTquantifier – 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.)