select - Executing extremely slow MySQL query -

this query has multiple join including aggregate functions

executing query approximately 6000 users took 20 seconds.

is there other method run query faster?

select users.id, sum(orders.totalcost) bought, count(comment.id) commentscount, count(topics.id) topicscount, count(users_login.id) logincount, count(users_download.id) downloadscount  users  left join orders on users.id=orders.userid , orders.status=1  left join comment on users.id=comment.userid  left join topics on users.id=topics.userid  left join users_login on users.id=users_login.userid  left join users_download on users.id=users_download.userid  users.id='$userid' group users.id  order `bought` desc 

the result of running explain

the explain output shows doing full-table scans on except users. need create secondary (non-unique) indexes on userid on other tables in join. speed queries on individual users.

however, if you're going process users in 1 pass single select without where users.id= clause. aggregation returns 1 row per user , should create single resultset containing rows , iterate on that, instead of reissuing query once per user. in case secondary indexes may still counts can determined index alone without looking @ tables themselves.