mysql - Query result is very slow -

i developing website other social networking site using mysql.

i wish give people suggestion users, , have implemented functionality in application, working slow. process take 2-3 seconds fetch result server. has necessary indexes, relations on table. have used explain command understand it, got nothing problematic in it. can't understand basic problem in it. please me.

here table structure : table : usermaster ~~~~~~~~~~~~~~~~~~

create table `usermaster` (         `userid` mediumint(8) unsigned not null auto_increment,     `username` varchar(20) default null,         `emailid` varchar(50) default null,       `firstname` varchar(20) not null,       `lastname` varchar(20) not null,       `cityid` mediumint(8) unsigned default null,       primary key (`userid`),       unique key `ux_usermaster_username` (`username`),       unique key `ux_usermaster_emailid` (`emailid`),         key `fk_usermaster_citymst_cityid_idx` (`cityid`),           key `fk_usermaster_countrymst_countryid_idx` (`countryid`),       constraint `fk_usermaster_citymst_cityid`                foreign key (`cityid`) references `citymst` (`cityid`)    on delete no action,       constraint `fk_usermaster_countrymst_countryid` foreign key countryid references countrymst (countryid) on delete no action on update cascade  )  engine=innodb auto_increment=19722 default charset=utf8$$ 

table : userfriends ~~~~~~~~~~~~~~~~~~~

create table `userfriends` (     `friendid` mediumint(8) unsigned not null auto_increment,       `userid` mediumint(8) unsigned not null,       `userid2` mediumint(8) unsigned not null,       `requestdate` timestamp null default current_timestamp,       `status` tinyint(3) unsigned not null default '2',       `approvaldate` timestamp null default null,       primary key (`friendid`),       unique key `ux_userfriends_userid_userid2` (`userid`,`userid2`),       key `ix_userfriens_userid_approvalstatus` (`userid`,`approvalstatus`,`userid2`,`friendid`,`requestdate`,`approvaldate`),       key `fk_userfriends_usermaster_userid_idx` (`userid`),       key `fk_userfriends_usermaster_userid2_idx` (`userid2`),       constraint `fk_userfriends_usermaster_userid` foreign key (`userid`) references `usermaster` (`userid`) on delete no action on update cascade,      constraint `fk_userfriends_usermaster_userid2`  foreign key (`userid2`)  references `usermaster` (`userid`) on delete no action on update cascade  )      engine=innodb auto_increment=50825 default charset=utf8$$ 

userid & userid2 both fields linked usermaster.userid

here select query : ~~~~~~~~~~~~~~~~~~~~~~~~~

select     upm.userid,     upm.username,     upm.firstname,      coalesce(mf.totmutualfriends,0) totmutualfriends usermaster upm left join citymst  ct on ct.cityid  = upm.cityid left join statemst st on st.stateid = ct.stateid left join (     select uf.userid, count(1) totmutualfriends     (         select uf.userid, uf.userid2, uf.approvalstatus         userfriends uf         union         select uf.userid2 userid, uf.userid userid2, uf.approvalstatus         userfriends uf     ) uf      inner join (          select if(uf.userid = 1, uf.userid2, uf.userid) userid2         userfriends uf         (uf.userid = 1 or uf.userid2 = 1)           , uf.approvalstatus = 1     ) uf1 on uf1.userid2 = uf.userid2     uf.approvalstatus = 1      group uf.userid ) mf on mf.userid = upm.userid left join (     select distinct usar.userid2     usersuggautorejct usar     usar.userid = 1     union      select if(uf.userid = 1, uf.userid2, uf.userid) userid2     userfriends uf     (uf.userid = 1 or uf.userid2 = 1) ) usar on usar.userid2 = upm.userid upm.userstatus in(10,11)   , upm.userid <> 1    , upm.userid not in (1221,2191)    , usar.userid2 null  order    (case when coalesce(mf.totmutualfriends,0) > 0 0      else 1    end),     (case when coalesce(mf.totmutualfriends,0) > 0 rand() else null end),    (case upm.cityid   when  1 0      else    1 end),     (case upm.cityid   when  1 rand() else null end),     (case ct.stateid   when  1 0      else    1 end),     (case ct.stateid   when  1 rand() else null end),     (case st.countryid when 91 0      else    1 end),    (case st.countryid when 91 rand() else null end) limit 10 

this performing slow. takes 2-3 seconds evolute.

relational database might not relevant social networking sites. because joining table slow, try using other no-sql databases(nosql type database). if u still insist using mysql, try not join in query.

sorry bad english, if any.