Amen
Новичок
как оптимизировать
подскажите как оптимизировать запрос ?
сам запрос :
[sql]
SELECT SQL_CALC_FOUND_ROWS U.UserID
FROM users U
LEFT JOIN sessions S ON U.UserID = S.SessionUserID
LEFT JOIN cities_around CA ON U.UserCityID = CA.RelatedCityID
LEFT JOIN users_fetishes_options FE ON U.UserID = FE.UserID
LEFT JOIN users_body_parts_options BP ON U.UserID = BP.UserID
LEFT JOIN users_relationships_options RS ON U.UserID = RS.UserID
WHERE UserApproved != '4'
AND UserSeekingGender = '1'
AND UserGender = '2'
AND UserDateOfBirth <= '1990-02-10'
AND UserDateOfBirth >= '1972-02-15'
AND users_fetishes_options.UserOptionID
IN (
'1'
)
AND users_body_parts_options.UserOptionID
IN (
'5'
)
AND users_relationships_options.UserOptionID
IN (
'2', '5'
)
AND (
users.UserApproved = '1'
OR users.UserID = '57871'
)
GROUP BY users.UserID
ORDER BY UserMembershipID DESC
LIMIT 0 , 10
[/sql]
EXPLAIN :
show create :
подскажите как оптимизировать запрос ?
сам запрос :
[sql]
SELECT SQL_CALC_FOUND_ROWS U.UserID
FROM users U
LEFT JOIN sessions S ON U.UserID = S.SessionUserID
LEFT JOIN cities_around CA ON U.UserCityID = CA.RelatedCityID
LEFT JOIN users_fetishes_options FE ON U.UserID = FE.UserID
LEFT JOIN users_body_parts_options BP ON U.UserID = BP.UserID
LEFT JOIN users_relationships_options RS ON U.UserID = RS.UserID
WHERE UserApproved != '4'
AND UserSeekingGender = '1'
AND UserGender = '2'
AND UserDateOfBirth <= '1990-02-10'
AND UserDateOfBirth >= '1972-02-15'
AND users_fetishes_options.UserOptionID
IN (
'1'
)
AND users_body_parts_options.UserOptionID
IN (
'5'
)
AND users_relationships_options.UserOptionID
IN (
'2', '5'
)
AND (
users.UserApproved = '1'
OR users.UserID = '57871'
)
GROUP BY users.UserID
ORDER BY UserMembershipID DESC
LIMIT 0 , 10
[/sql]
EXPLAIN :
Код:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE users_body_parts_options ref UserID,UserOptionID UserOptionID 1 const 152111 Using where; Using temporary; Using filesort
1 SIMPLE users_fetishes_options ref UserID,UserOptionID UserID 4 sevgilimol.BP.UserID 1 Using where
1 SIMPLE users_relationships_options ref UserID,UserOptionID UserID 4 sevgilimol.FE.UserID 2 Using where
1 SIMPLE users eq_ref PRIMARY,UserDateOfBirth PRIMARY 4 sevgilimol.RS.UserID 1 Using where
1 SIMPLE sessions ref SessionUserID SessionUserID 4 sevgilimol.FE.UserID 32 Using index
1 SIMPLE cities_around ref RelatedCityID RelatedCityID 1 sevgilimol.U.UserCityID 81 Using indexshow create :
Код:
Table Create Table
users CREATE TABLE `users` (\n
`UserID` int(8) unsigned NOT NULL auto_increment,\n
`UserMembershipID` enum('1','2','3') NOT NULL default '1',\n
`UserMembershipExpire` date NOT NULL default '0000-00-00',\n
`UserHighlightExpire` date NOT NULL default '0000-00-00',\n
`UserUsername` varchar(15) NOT NULL default '',\n
`UserPassword` varchar(15) NOT NULL default '',\n
`UserPasswordQuestion` enum('0','1','2','3','4','5','6','7') NOT NULL default '0',\n
`UserPasswordAnswer` varchar(32) NOT NULL default '',\n
`UserGender` enum('1','2','3') NOT NULL default '1',\n
`UserDateOfBirth` date NOT NULL default '0000-00-00',\n
`UserEmail` varchar(50) NOT NULL default '',\n
`UserEmailNotifications` enum('0','1') NOT NULL default '1',\n
`UserCountryID` tinyint(3) unsigned NOT NULL default '0',\n
`UserCityID` tinyint(3) unsigned NOT NULL default '0',\n
`UserStatus` enum('0','1','2','3','4','5') NOT NULL default '0',\n
`UserBodyType` enum('0','1','2','3','4','5','6') NOT NULL default '0',\n
`UserHeight` tinyint(3) unsigned NOT NULL default '3',\n
`UserEyeColor` enum('0','1','2','3','4','5','6') NOT NULL default '0',\n
`UserHairColor` enum('0','1','2','3','4','5','6','7') NOT NULL default '0',\n
`UserSexuallyAre` enum('0','1','2','3','4','5','6','7','8') NOT NULL default '0',\n
`UserFetishes` enum('0','1','2','3','4','5','6','7','8','9','10','11') NOT NULL default '0',\n
`UserHaveACar` enum('0','1','2','3') NOT NULL default '0',\n
`UserIncome` enum('0','1','2','3','4','5','6') NOT NULL default '0',\n
`UserHaveWebcam` enum('0','1','2') NOT NULL default '0',\n
`UserDrink` enum('0','1','2','3') NOT NULL default '0',\n
`UserSmoke` enum('0','1','2','3') NOT NULL default '0',\n
`UserSeekingGender` enum('0','1','2','3') NOT NULL default '0',\n
`UserSeekingAgesFrom` tinyint(2) unsigned NOT NULL default '0',\n
`UserSeekingAgesTo` tinyint(2) unsigned NOT NULL default '0',\n
`UserRating` double(3,2) unsigned NOT NULL default '7.00',\n
`UserMessagesSent` tinyint(3) unsigned NOT NULL default '0',\n
`UserWinksSent` tinyint(3) unsigned NOT NULL default '0',\n
`UserHomePageProfile` enum('0','1') NOT NULL default '0',\n
`UserMainImage` enum('0','1') NOT NULL default '0',\n
`UserVideo` enum('0','1','2','3') NOT NULL default '0',\n
`UserChatModerator` enum('0','1') NOT NULL default '0',\n
`UserChatTime` int(4) unsigned NOT NULL default '0',\n
`UserApproved` enum('0','1','2','3','4') NOT NULL default '0',\n
`UserRefer` varchar(50) NOT NULL default '',\n
`UserLastLogin` datetime NOT NULL default '0000-00-00 00:00:00',\n
`UserLastUpdate` datetime NOT NULL default '0000-00-00 00:00:00',\n
`UserRegistered` datetime NOT NULL default '0000-00-00 00:00:00',\n
`UserProfileUpdated` enum('0','1') NOT NULL default '0',\n
PRIMARY KEY (`UserID`),\n
KEY `UserUsername` (`UserUsername`),\n
KEY `UserCityID` (`UserCityID`)\n
) ENGINE=MyISAM AUTO_INCREMENT=57872 DEFAULT CHARSET=latin1