Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Here is my schema:

CREATE TABLE `UserMst` (
  `UserID` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `EMailID` varchar(50) DEFAULT NULL,
  `UserName` varchar(20) DEFAULT NULL,
  `FullName` varchar(41) DEFAULT NULL,
  `UserStatus` tinyint(3) unsigned DEFAULT NULL,
  `CityID` mediumint(8) unsigned DEFAULT NULL,
  `CountryID` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`UserID`),
  UNIQUE KEY `UX_UserMst_UserName` (`UserName`),
  UNIQUE KEY `UX_UserMst_EMailID` (`EMailID`),
  KEY `FK_UserMst_CityMst_CityID_idx` (`CityID`),
  KEY `FK_UserMst_CountryMst_CountryID_idx` (`CountryID`),
  CONSTRAINT `FK_UserMst_CityMst_CityID` FOREIGN KEY (`CityID`) REFERENCES `CityMst` (`CityID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `FK_UserMst_CountryMst_CountryID` FOREIGN KEY (`CountryID`) REFERENCES `CountryMst` (`CountryID`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=97828 DEFAULT CHARSET=utf8;

CREATE TABLE `FriendDet` (
  `FriendDetID` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `UserID` mediumint(8) unsigned NOT NULL,
  `FriendID` mediumint(8) unsigned NOT NULL,
  `ApprovalStatus` tinyint(3) unsigned NOT NULL DEFAULT '2',
  PRIMARY KEY (`FriendDetID`),
  UNIQUE KEY `UX_FriendDet_UserID_FriendID` (`UserID`,`FriendID`) USING BTREE,
  KEY `FK_FriendDet_UserMst_FriendID_idx` (`FriendID`),
  CONSTRAINT `FK_FriendDet_UserMst_FriendID` FOREIGN KEY (`FriendID`) REFERENCES `UserMst` (`UserID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `FK_FriendDet_UserMst_UserID` FOREIGN KEY (`UserID`) REFERENCES `UserMst` (`UserID`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=451361 DEFAULT CHARSET=latin1;

CREATE TABLE `Rejected` (
  `RejectID` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `UserID` mediumint(8) unsigned NOT NULL,
  `FriendID` mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (`RejectID`),
  UNIQUE KEY `UX_Rejected_UserID_FriendID` (`UserID`,`FriendID`),
  KEY `FK_Rejected_UserMst_FriendID_idx` (`FriendID`),
  KEY `IX_Rejected_CreatedDate` (`CreatedDate`) USING BTREE,
  CONSTRAINT `FK_Rejected_UserMst_FriendID` FOREIGN KEY (`FriendID`) REFERENCES `UserMst` (`UserID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `FK_Rejected_UserMst_UserID` FOREIGN KEY (`UserID`) REFERENCES `UserMst` (`UserID`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=318279 DEFAULT CHARSET=utf8;

CREATE TABLE `CityMst` (
  `CityID` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `City` varchar(100) NOT NULL,
  `StateID` mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (`CityID`),
  UNIQUE KEY `UX_CityMaster_CityName_StateID` (`City`,`StateID`),
  KEY `IX_CityMaster` (`StateID`)
) ENGINE=InnoDB AUTO_INCREMENT=2190681 DEFAULT CHARSET=utf8;

CREATE TABLE `StateMst` (
  `StateID` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `State` varchar(100) NOT NULL,
  `StateCode` varchar(10) DEFAULT NULL,
  `CountryID` smallint(5) unsigned NOT NULL,
  PRIMARY KEY (`StateID`),
  UNIQUE KEY `UX_StateMst_State_CountryID` (`State`,`CountryID`),
  KEY `IX_StateMst_StateID` (`StateID`,`CountryID`),
  KEY `FK_StateMst_CountryMst_CountryID_idx` (`CountryID`),
  CONSTRAINT `FK_StateMst_CountryMst_CountryID` FOREIGN KEY (`CountryID`) REFERENCES `CountryMst` (`CountryID`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3968 DEFAULT CHARSET=utf8;

CREATE TABLE `CountryMst` (
  `CountryID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `Country` varchar(100) NOT NULL,
  PRIMARY KEY (`CountryID`),
  UNIQUE KEY `UX_CountryMaster_Country` (`Country`),
) ENGINE=InnoDB AUTO_INCREMENT=275 DEFAULT CHARSET=utf8;

Here is my query:

SELECT SQL_NO_CACHE upm.UserID fid, upm.UserName uname, upm.FullName fname, upm.imgname img, ct.city,
        ifnull(mf.TotMutualFriends,0) mutual, 
       -- coalesce(mf.TotMutualFriends, If(upm.CityID = 1,0,If(ct.StateID = 1,-1,If(st.CountryID = 91,-2,null)))) OrdBy 
FROM UserMst upm
LEFT JOIN CityMst ct on ct.CityID = upm.CityID
LEFT JOIN StateMst st on st.StateID = ct.StateID
LEFT JOIN ( SELECT ufd.UserID, COUNT(1) TotMutualFriends
            FROM FriendDet ufd
            INNER JOIN FriendDet fof on fof.FriendID = ufd.FriendID
            WHERE ufd.ApprovalStatus = 1
            AND fof.UserID = 1 AND fof.ApprovalStatus = 1
            GROUP BY ufd.UserID 
            ORDER BY 1, 2) mf on mf.UserID = upm.UserID
LEFT JOIN ( SELECT usar.FriendID
            FROM Rejected usar
            WHERE usar.UserID = 1
            UNION 
            SELECT ufd.FriendID 
            FROM FriendDet ufd
            WHERE ufd.UserID = 1 
          ) usar ON usar.FriendID = upm.UserID
WHERE upm.UserID <> 1
AND upm.UserStatus IN(10,11) 
AND usar.FriendID IS NULL 
ORDER BY mf.TotMutualFriends DESC,
         ( CASE upm.CityID   WHEN 1 THEN 0 ELSE 1 END ), 
         ( CASE ct.StateID   WHEN 1 THEN 0 ELSE 1 END ), 
         ( CASE st.CountryID WHEN 91 THEN 0 ELSE 1 END ),
         upm.FullName LIMIT 25;

and finally my qeury execution plan

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'PRIMARY', 'upm', 'range', 'PRIMARY', 'PRIMARY', '3', NULL, '49105', 'Using where; Using temporary; Using filesort'
'1', 'PRIMARY', 'ct', 'eq_ref', 'PRIMARY', 'PRIMARY', '3', 'upm.CityID', '1', NULL
'1', 'PRIMARY', 'st', 'eq_ref', 'PRIMARY,IX_StateMst_StateID', 'PRIMARY', '3', 'ct.StateID', '1', NULL
'1', 'PRIMARY', '<derived2>', 'ref', '<auto_key0>', '<auto_key0>', '3', 'upm.UserID', '10', NULL
'1', 'PRIMARY', '<derived3>', 'ref', '<auto_key0>', '<auto_key0>', '3', 'upm.UserID', '10', 'Using where; Not exists; Using index'
'3', 'DERIVED', 'usar', 'ref', 'UX_Rejected_UserID_FriendID', 'UX_Rejected_UserID_FriendID', '3', 'const', '2', 'Using index'
'4', 'UNION', 'ufd', 'ref', 'UX_FriendDet_UserID_FriendID', 'UX_FriendDet_UserID_FriendID', '3', 'const', '107', 'Using index'
NULL, 'UNION RESULT', '<union3,4>', 'ALL', NULL, NULL, NULL, NULL, NULL, 'Using temporary'
'2', 'DERIVED', 'fof', 'ref', 'UX_FriendDet_UserID_FriendID,FK_FriendDet_UserMst_FriendID_idx', 'UX_FriendDet_UserID_FriendID', '3', 'const', '107', 'Using where; Using temporary; Using filesort'
'2', 'DERIVED', 'ufd', 'ref', 'UX_FriendDet_UserID_FriendID,FK_FriendDet_UserMst_FriendID_idx', 'FK_FriendDet_UserMst_FriendID_idx', '3', 'fof.FriendID', '2', 'Using where'

The above query performs poorly with an order by clause, taking about 2 seconds to execute. UserMst has 100000 records and FriendDet table has 500000 records.

If I wrap this in a subquery it takes 1 second but scans more rows.

SELECT * FROM (
    SELECT upm.UserID fid, upm.UserName uname, upm.FullName fname, upm.imgname img, ct.city,
           coalesce(mf.TotMutualFriends, If(upm.CityID = 1,0,If(ct.StateID = 1,-1,If(st.CountryID = 91,-2,null)))) OrdBy 
    FROM UserMst upm
    LEFT JOIN CityMst ct on ct.CityID = upm.CityID
    LEFT JOIN StateMst st on st.StateID = ct.StateID
    LEFT JOIN ( SELECT ufd.UserID, COUNT(1) TotMutualFriends
                FROM FriendDet ufd
                INNER JOIN FriendDet fof on fof.FriendID = ufd.FriendID
                WHERE ufd.ApprovalStatus = 1
                AND fof.UserID = 1 AND fof.ApprovalStatus = 1
                GROUP BY ufd.UserID 
                ORDER BY 1, 2) mf on mf.UserID = upm.UserID
    LEFT JOIN ( SELECT usar.FriendID
                FROM Rejected usar
                WHERE usar.UserID = 1
                UNION 
                SELECT ufd.FriendID 
                FROM FriendDet ufd
                WHERE ufd.UserID = 1 
              ) usar ON usar.FriendID = upm.UserID
    WHERE upm.UserID <> 1
    AND upm.UserStatus IN(10,11) 
    AND usar.FriendID IS NULL ) a
    ORDER BY OrdBy desc, a.fname LIMIT 25 

In above query query plan shows me 1st rows changed as follows

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'PRIMARY', '<derived2>', 'ALL', NULL, NULL, NULL, NULL, '5454396', 'Using filesort'

Please check "Rows" and "extra" field carefully. Is it ok or am I doing something wrong? As per my opinion this is still bad performance. I expect this should be come in 200-250ms.

This is my second try. I have posted previously this post

share|improve this question
1  
Why the ORDER BY 1,2 in the subquery? It's redundant and can be removed. –  ypercube Mar 12 at 11:26
    
And why is the extra wrapping SELECT * FROM (...) a ... needed? Isn't the query faster if you remove that nesting? –  ypercube Mar 12 at 11:26
    
@ypercube, Order by 1,2 can be removable. I just want to check, and If I remove extra warpping, query takes 2 seconds, means performance is bed. I have explained in detaild –  Manish Sapkal Mar 12 at 11:30
    
You probably need some composite indexes to improve the mf subquery. Another thing would be to use UNION ALL in the usar subquery. –  ypercube Mar 12 at 11:38
    
UNION ALL may result duplicate data, and subquery has already using index –  Manish Sapkal Mar 12 at 11:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.