Форум MySQL

Здравствуйте. Помогите пожалуйста разобраться, почему этот запрос так долго выполняется (в таблице всего 100 строк, на время выполнения 6 секунд) и как его оптимизировать?

SELECT SQL_CALC_FOUND_ROWS                        `XeronReferralsMarket`.`ID`,                        `XeronReferralsMarket`.`sellerID`,                        `XeronReferralsMarket`.`referralID`,                        `XeronReferralsMarket`.`cost`,                        `XeronReferralsMarket`.`costCurrency`,                        `XeronReferralsMarket`.`buyerID`,                        `XeronReferralsMarket`.`status`,                        `XeronUsers`.`nickName` as `referralNick`,                        DATE_FORMAT( `XeronUsers`.`signupDate`, '%d.%m.%Y') as referralSignupDate,                        DATE_FORMAT( `XeronUsers`.`activityDate`, '%d.%m.%Y') as referralActivityDate,                        ROUND((`XeronUsers`.`partnerUSDProfit` * 30 + `XeronUsers`.`partnerRUBProfit`), 2) as referralProfit,                        ROUND(`XeronUsers`.`partnerUSDProfit`, 2) as referralUSDProfit,                        ROUND(`XeronUsers`.`partnerRUBProfit`, 2) as referralRUBProfit,                        (SELECT COUNT(*) FROM `XeronUsers` WHERE `partnerID` = `XeronReferralsMarket`.`referralID`) as referralInvolvedCount,                        ROUND( `XeronUsers`.`visitsDaysCount`/(TO_DAYS(NOW()) - TO_DAYS(`XeronUsers`.`signupDate`) + 1), 2) as referralActivity,                        ROUND( IF( `XeronUsers`.`KNBVictoriesCount` = 0, 0, `XeronUsers`.`KNBVictoriesCount` / (`XeronUsers`.`KNBVictoriesCount` + `XeronUsers`.`KNBDefeatsCount`) ), 2 ) as referralEfficiency,                        ROUND(`XeronUsers`.`rating`, 2) as referralRating,                        ROUND(IF( `XeronReferralsMarket`.`costCurrency` = 'USD', `XeronReferralsMarket`.`cost`, `XeronReferralsMarket`.`cost` / 30), 2) as costUSD,                        ROUND(IF( `XeronReferralsMarket`.`costCurrency` = 'RUB', `XeronReferralsMarket`.`cost`, `XeronReferralsMarket`.`cost` * 30), 2) as costRUB,                        IF((SELECT COUNT(*) FROM `XeronOnline` WHERE `userID` = `XeronReferralsMarket`.`referralID`) > 0, TRUE, FALSE) as isOnline,                        IF((SELECT COUNT(*) FROM `XeronKNBGames` WHERE (`serverID` = `XeronReferralsMarket`.`referralID` OR `clientID` = `XeronReferralsMarket`.`referralID`) AND `date` > NOW() - INTERVAL 5 MINUTE), TRUE, FALSE) as isGamer,                        `XeronReferralsMarket`.`comment`,                        ROUND(`XeronReferralsMarket`.`cost` - IF(ISNULL(`XeronReferralsMarket`.`reserveCost`), 0, `XeronReferralsMarket`.`reserveCost`), 2) as payCost,                        IF(`XeronReferralsMarket`.`sellerID` = 6, TRUE, FALSE) isMyReferral,                        IF(`XeronReferralsMarket`.`status` = 'RESERVE' AND `XeronReferralsMarket`.`buyerID` = 6, TRUE, FALSE) as isMyReserved                FROM `XeronReferralsMarket` LEFT JOIN `XeronUsers` ON (`XeronReferralsMarket`.`referralID` = `XeronUsers`.`ID`) HAVING `referralID` != 0 AND `referralID` != 6 AND (`status` = 'SALE' OR `isMyReserved` = TRUE) ORDER BY `referralNick` ASC LIMIT 0, 5

У вас довольно много вложенных запросов. Если разбить эти запросы на отдельные - можно ускориться, более того, будет проще их профилировать и оптимизировать по скорости.

Смотрите что у вас происходит:
1. выбирается вся таблица `XeronReferralsMarket`. (допустим 100 строк)
2. выбирается вся таблица `XeronUsers`. (допустим 50 строк)
3. Они объединяются (JOIN) -получается таблица из 50*100=5000 строк
4. Эта таблица фильтруется по условию (ON) - остается 100 строк (т.к. LEFT JOIN)
5. Для этих 100 строк для каждой вычисляется 25 полей (т.е. 2500 вычислений).
При чем ТРИ(!!!) из этих вычислений - это креллирующие подзапросы.
6. Из полученной таблицы выкидывается все, что не подходит под условие HAVING.

Спрашивается:
Почему проверка условия (HAVING) происходит в самом конце, вместо того чтоб быть в самом начале?

Отвечается:
Чтоб побольше посчитать =)

Где ошибки?
В пунктах 2, 3, 5 и 6!

Я бы исправил так:

SELECT SQL_CALC_FOUND_ROWS         T.`ID`,         T.`sellerID`,         T.`referralID`,         T.`cost`,         T.`costCurrency`,         T.`buyerID`,         T.`status`,         X.`nickName` as `referralNick`,         DATE_FORMAT( X.`signupDate`, '%d.%m.%Y') as referralSignupDate,         DATE_FORMAT( X.`activityDate`, '%d.%m.%Y') as referralActivityDate,         ROUND(( X.`partnerUSDProfit` * 30 + X.`partnerRUBProfit`), 2) as referralProfit,         ROUND( X.`partnerUSDProfit`, 2) as referralUSDProfit,         ROUND( X.`partnerRUBProfit`, 2) as referralRUBProfit,         C1.referralInvolvedCount,         ROUND( X.`visitsDaysCount`/(TO_DAYS(NOW()) - TO_DAYS(X.`signupDate`) + 1), 2) as referralActivity,         ROUND( IF( X.`KNBVictoriesCount` = 0, 0, X.`KNBVictoriesCount` / (X.`KNBVictoriesCount` + X.`KNBDefeatsCount`) ), 2 ) as referralEfficiency,         ROUND(X.`rating`, 2) as referralRating,         ROUND(IF( T.`costCurrency` = 'USD', T.`cost`, T.`cost` / 30), 2) as costUSD,         ROUND(IF( T.`costCurrency` = 'RUB', T.`cost`, T.`cost` * 30), 2) as costRUB,         IF(C2.co > 0, TRUE, FALSE) as isOnline,         IF((SELECT COUNT(*) FROM `XeronKNBGames` WHERE (`serverID` = T.`referralID` OR `clientID` = T.`referralID`) AND `date` > NOW() - INTERVAL 5 MINUTE), TRUE, FALSE) as isGamer,         T.`comment`,         ROUND(T.`cost` - IF(ISNULL(T.`reserveCost`), 0, T.`reserveCost`), 2) as payCost,         IF(T.`sellerID` = 6, TRUE, FALSE) isMyReferral,         IF(T.`status` = 'RESERVE' AND T.`buyerID` = 6, TRUE, FALSE) as isMyReserved  FROM( SELECT * FROM `XeronReferralsMarket`       WHERE `referralID` NOT IN(0,6)         AND( `status`='SALE' OR (`status`='RESERVE' AND `buyerID`=6) ) )T LEFT JOIN `XeronUsers` X ON (T.`referralID` = X.`ID`) LEFT JOIN( SELECT `partnerID`, COUNT(*) as referralInvolvedCount FROM `XeronUsers` GROUP BY `partnerID` )C1 ON C1.`partnerID` = T.`referralID` LEFT JOIN( SELECT `userID`, COUNT(*) as co FROM `XeronOnline` GROUP BY `userID` )C2 ON C2.`userID` = T.`referralID` ORDER BY `referralNick` LIMIT 5

Я один из подзапросов не стал выносить, т.к. не знаю структуру БД и логику этого подзапроса не особо понял.

Однако уже даже так должно быть значительно быстрее. Проверьте.

Идеально! Спасибо, стало гораздо быстрее работать!!!!

> Идеально!
Далеко не идеально.
Но чтобы сделать значительно лучше - это уже нужно видеть живые таблицы. Их структуру и данные. Просто до сих пор у вас вообще логика выборки была нарушена.

> стало гораздо быстрее работать!
Было 6 секунд. А "гораздо быстрее" -это сколько? Просто интересно.

Думаю вот так

SELECT SQL_CALC_FOUND_ROWS         T.`ID`,         T.`sellerID`,         T.`referralID`,         T.`cost`,         T.`costCurrency`,         T.`buyerID`,         T.`status`,         X.`nickName` as `referralNick`,         DATE_FORMAT( X.`signupDate`, '%d.%m.%Y') as referralSignupDate,         DATE_FORMAT( X.`activityDate`, '%d.%m.%Y') as referralActivityDate,         ROUND(( X.`partnerUSDProfit` * 30 + X.`partnerRUBProfit`), 2) as referralProfit,         ROUND( X.`partnerUSDProfit`, 2) as referralUSDProfit,         ROUND( X.`partnerRUBProfit`, 2) as referralRUBProfit,         C1.referralInvolvedCount,         ROUND( X.`visitsDaysCount`/(TO_DAYS(NOW()) - TO_DAYS(X.`signupDate`) + 1), 2) as referralActivity,         ROUND( IF( X.`KNBVictoriesCount` = 0, 0, X.`KNBVictoriesCount` / (X.`KNBVictoriesCount` + X.`KNBDefeatsCount`) ), 2 ) as referralEfficiency,         ROUND(X.`rating`, 2) as referralRating,         ROUND(IF( T.`costCurrency` = 'USD', T.`cost`, T.`cost` / 30), 2) as costUSD,         ROUND(IF( T.`costCurrency` = 'RUB', T.`cost`, T.`cost` * 30), 2) as costRUB,         IF( `referralID` IN( SELECT DISTINCT `userID` FROM `XeronOnline` ), 1, 0) as isOnline,         IF( `referralID` IN( SELECT `serverID` FROM `XeronKNBGames` UNION SELECT `clientID` FROM `XeronKNBGames`)            AND `date` > NOW() - INTERVAL 5 MINUTE), 1, 0) as isGamer,        T.`comment`,         ROUND(T.`cost` - IF(ISNULL(T.`reserveCost`), 0, T.`reserveCost`), 2) as payCost,         IF(T.`sellerID` = 6, TRUE, FALSE) isMyReferral,         IF(T.`status` = 'RESERVE' AND T.`buyerID` = 6, TRUE, FALSE) as isMyReserved  FROM( SELECT * FROM `XeronReferralsMarket`       WHERE `referralID` NOT IN(0,6)         AND( `status`='SALE' OR (`status`='RESERVE' AND `buyerID`=6) ) )T LEFT JOIN `XeronUsers` X ON (T.`referralID` = X.`ID`) JOIN( SELECT `partnerID`, COUNT(*) as referralInvolvedCount FROM `XeronUsers` GROUP BY `partnerID` )C1 ON C1.`partnerID` = T.`referralID` ORDER BY `referralNick` LIMIT 5

должно быть еще немного быстрее. Но уже не на много.