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.

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.

share|improve this question
    
There is probably something to say for actually sharing the EXPLAIN? –  Nanne Apr 9 '13 at 14:13
add comment

1 Answer 1

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 nee 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.

share|improve this answer
add comment

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.