Mysql query is very slow

MySQLmysql-5mysql-5.1mysql-5.5mysql-workbench

I am developing a website like any other social networking site using mysql.

I wish it give people suggestion to my users, and I have implemented this functionality in my application, but It is working very slow. this process take 2-3 seconds to fetch result from server. It has all the necessary indexes, relations on table. I have used EXPLAIN command to understand it, but I got nothing problematic in it.
I can't understand what is the basic problem in it. Please help me.

here is my 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 are linked with UserMaster.UserID

Here is my select query :

SELECT upm.UserID, upm.UserName, upm.FirstName, 
    COALESCE(mf.TotMutualFriends,0) TotMutualFriends
FROM 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         
    FROM (  
        SELECT uf.UserID, uf.UserID2, uf.ApprovalStatus
        FROM UserFriends uf
        UNION ALL
        SELECT uf.UserID2 UserID, uf.UserID UserID2, uf.ApprovalStatus
        FROM UserFriends uf 
    ) uf 
    INNER JOIN (    
        SELECT IF(uf.UserID = 1, uf.UserID2, uf.UserID) UserID2
        FROM UserFriends uf
        WHERE ( uf.UserID = 1 OR uf.UserID2 = 1 )
          AND uf.ApprovalStatus = 1
    ) uf1 
        on uf1.UserID2 = uf.UserID2
    WHERE uf.ApprovalStatus = 1 
    GROUP BY uf.UserID 
) mf 
    on mf.UserID = upm.UserID
LEFT JOIN (     
    SELECT DISTINCT usar.UserID2
    FROM UserSuggAutoRejct usar
    WHERE usar.UserID = 1
    UNION 
    SELECT IF(uf.UserID = 1, uf.UserID2, uf.UserID) UserID2
    FROM UserFriends uf
    WHERE ( uf.UserID = 1 OR uf.UserID2 = 1) 
) usar 
    ON usar.UserID2 = upm.UserID
WHERE upm.UserStatus IN(10,11)
AND upm.UserID <> 1 
AND upm.UserID NOT IN (1221,2191) 
AND usar.UserID2 IS NULL 
ORDER BY ( CASE WHEN COALESCE(mf.TotMutualFriends,0) > 0 
                THEN 0 ELSE 1 END ), 
         ( CASE WHEN COALESCE(mf.TotMutualFriends,0) > 0 
                THEN RAND() ELSE NULL END ),
         ( CASE upm.CityID   WHEN 1 THEN 0 ELSE 1 END ), 
         ( CASE upm.CityID   WHEN 1 THEN RAND() ELSE NULL END ), 
         ( CASE ct.StateID   WHEN 1 THEN 0 ELSE 1 END ), 
         ( CASE ct.StateID   WHEN 1 THEN RAND() ELSE NULL END ), 
         ( CASE st.CountryID WHEN 91 THEN 0 ELSE 1 END ),
         ( CASE st.CountryID WHEN 91 THEN RAND() ELSE NULL END ) 
LIMIT 10

This is performing very slow. It takes 2-3 seconds to evolute.

Best Answer

SELECT ... FROM ...
JOIN ( SELECT ... ) ON ...
JOIN ( SELECT ... ) ON ...

has an inherent optimization problem. There will be no indexes on those subqueries. If they have, say, 1000 rows each, then it will take a million (1000*1000) operations to do the JOINs.

If these truly need to be LEFT JOINs (not plain JOINs), then the optimizer cannot even start with one of those subqueries, thereby avoiding one of the 1000x multipliers.

One possible solution is to do CREATE TEMPORARY TABLE mf ( PRIMARY KEY (UserID) ) SELECT ...; -- where the SELECT is the first subquery. Ditto for usar.

Note that I am suggesting creating the necessary index as the temp table is built.

Related Question