Всем добра! Нашелся такой Код (Text): Count: 3 Time=5.17s (15s) Lock=2.28s (6s) Rows_sent=50.0 (150), Rows_examined=17590.3 (52771), casino777[casino777]@localhost SELECT SQL_CALC_FOUND_ROWS t1.*, t2.login as creator_login, count(output.id) as wait_opl from users t1 left join users t2 on (t1.creator=t2.id) left join output ON ( t1.login = output.login AND output.status =N ) where t1.room_id=N and t1.status in (N,N,N) group by t1.login order by t1.status asc, wait_opl desc, login desc LIMIT N, N Сделал вот так: Код (Text): select t1.*, t2.login as creator_login, count(output.id) as wait_opl from users t1 left join users t2 on (t1.creator=t2.id) left join output ON ( t1.login = output.login AND output.status =0 ) where t1.room_id=1 and t1.status in (4,5,6) group by t1.login order by t1.status asc, wait_opl desc, login desc LIMIT 0, 50 Но как не помогло: Отображение строк 0 - 49 (50 всего, Запрос занял 5.2400 сек.) [status: 5 - 5] Как еще можно его оптимизировать? Спасибо!
Спойлер: output Код (Text): CREATE TABLE IF NOT EXISTS `output` ( `id` int(5) NOT NULL, `inv_code` char(10) DEFAULT NULL, `login` char(20) NOT NULL DEFAULT '', `ps` varchar(50) DEFAULT NULL, `status` smallint(1) NOT NULL DEFAULT '0', `date` int(10) NOT NULL DEFAULT '0', `sum` decimal(7,2) NOT NULL DEFAULT '0.00', `sum_out` decimal(7,2) NOT NULL DEFAULT '0.00' ) ENGINE=MyISAM AUTO_INCREMENT=40303 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; -- -- Индексы таблицы `output` -- ALTER TABLE `output` ADD PRIMARY KEY (`id`); -- -- AUTO_INCREMENT для сохранённых таблиц -- -- -- AUTO_INCREMENT для таблицы `output` -- ALTER TABLE `output` MODIFY `id` int(5) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=40303; INSERT INTO `output` (`id`, `inv_code`, `login`, `ps`, `status`, `date`, `sum`, `sum_out`) VALUES (1, '689RKR6', 'bo**0', 'QIWI_7**1', 3, 1559575208, 4000.00, 3575.00), (38296, 'SJJLGT2', 'al**n', 'QIWI_7**5', 3, 1597048808, 7000.00, 6275.00), (38297, 'DWW008E', 'Vl**91', 'VISA/MASTERCARD_4**6', 3, 1597050178, 45000.00, 40475.00), (38298, 'TK65J39', 'te**4', 'QIWI_7**3', 3, 1597050729, 30000.00, 26975.00), (38299, 'MBOV31M', 'da**7', 'QIWI_7**8', 2, 1597053265, 11500.00, 10325.00), (38300, '8532ENJ', 'V**91', 'VISA/MASTERCARD_47**6', 3, 1597053423, 45000.00, 40475.00); Спойлер: users Код (Text): CREATE TABLE IF NOT EXISTS `users` ( `id` int(5) NOT NULL, `login` char(20) NOT NULL DEFAULT '', `pass` char(32) NOT NULL DEFAULT '', `pin` char(4) NOT NULL DEFAULT '', `email` varchar(100) DEFAULT NULL, `mail_active_status` tinyint(1) NOT NULL DEFAULT '0', `fio` varchar(100) NOT NULL DEFAULT '', `wmr` char(13) NOT NULL DEFAULT '', `qiwi` varchar(18) DEFAULT NULL, `balance` decimal(14,2) DEFAULT '0.00', `balance_bonus` decimal(14,2) DEFAULT '0.00', `wager` int(11) NOT NULL DEFAULT '0', `wager_bonus` int(11) NOT NULL DEFAULT '0', `pay_points` decimal(14,2) DEFAULT '0.00', `can_outpay` tinyint(1) NOT NULL DEFAULT '0', `demobalance` decimal(14,2) DEFAULT '0.00', `demomode` int(1) DEFAULT '0', `creator` int(1) NOT NULL DEFAULT '1', `ref_id` int(11) DEFAULT NULL COMMENT 'id юзера превлекшего данного игрока', `reg_time` int(10) NOT NULL DEFAULT '0', `go_time` int(10) NOT NULL DEFAULT '0', `ip` char(15) DEFAULT '0', `useragent` varchar(500) DEFAULT NULL, `os` varchar(50) DEFAULT NULL, `last_ge` varchar(50) NOT NULL DEFAULT 'root', `status` smallint(1) NOT NULL DEFAULT '5', `action` int(2) NOT NULL DEFAULT '0', `room_id` int(10) NOT NULL DEFAULT '1', `comment` varchar(150) DEFAULT NULL, `graf_kind` enum('low','medium','high') NOT NULL DEFAULT 'high', `denomination` decimal(5,2) NOT NULL DEFAULT '1.00', `spin_bank` decimal(13,3) DEFAULT '0.000', `bonus_bank` decimal(13,3) DEFAULT '0.000', `double_bank` decimal(13,3) DEFAULT '0.000', `sound` int(1) DEFAULT '1', `preset_id` int(10) DEFAULT '0', `point_on` int(1) NOT NULL DEFAULT '0', `point` decimal(14,2) DEFAULT '0.00', `lang` char(2) DEFAULT 'ru', `payed_spins` int(11) NOT NULL DEFAULT '0', `curspin` int(5) NOT NULL DEFAULT '0', `garant` varchar(10) DEFAULT '1|5', `curspin_bonus` int(5) NOT NULL DEFAULT '0', `garant_bonus` varchar(10) DEFAULT '100000000', `payin` decimal(12,2) DEFAULT '0.00', `payin_total` decimal(12,2) DEFAULT '0.00', `gift` int(1) DEFAULT NULL COMMENT '1-рега, 2-ежедневка,3-первый депозит', `rating` int(3) NOT NULL DEFAULT '1', `firstname` varchar(50) DEFAULT NULL, `lastname` varchar(50) DEFAULT NULL, `birthday` date DEFAULT NULL ) ENGINE=MyISAM AUTO_INCREMENT=22766 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; -- -- Индексы таблицы `users` -- ALTER TABLE `users` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `login` (`login`) USING BTREE, ADD UNIQUE KEY `qiwi` (`qiwi`) USING BTREE, ADD UNIQUE KEY `email` (`email`) USING BTREE, ADD KEY `point_on` (`point_on`) USING BTREE, ADD KEY `status` (`status`) USING BTREE, ADD KEY `action` (`action`) USING BTREE; -- -- AUTO_INCREMENT для сохранённых таблиц -- -- -- AUTO_INCREMENT для таблицы `users` -- ALTER TABLE `users` MODIFY `id` int(5) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=22766; INSERT INTO `users` (`id`, `login`, `pass`, `pin`, `email`, `mail_active_status`, `fio`, `wmr`, `qiwi`, `balance`, `balance_bonus`, `wager`, `wager_bonus`, `pay_points`, `can_outpay`, `demobalance`, `demomode`, `creator`, `ref_id`, `reg_time`, `go_time`, `ip`, `useragent`, `os`, `last_ge`, `status`, `action`, `room_id`, `comment`, `graf_kind`, `denomination`, `spin_bank`, `bonus_bank`, `double_bank`, `sound`, `preset_id`, `point_on`, `point`, `lang`, `payed_spins`, `curspin`, `garant`, `curspin_bonus`, `garant_bonus`, `payin`, `payin_total`, `gift`, `rating`, `firstname`, `lastname`, `birthday`) VALUES (18322, 'iv**0', 'd0cdf9**1aaf15', '', 'ivan**@mail.ru', 0, '', '', NULL, 20187.00, 0.00, 0, 0, 5000.00, 1, 25187.00, 0, 1, NULL, 1591627408, 1591713415, '95.153.134.36', NULL, NULL, 'index', 5, 4, 1, NULL, 'high', 1.00, 0.000, 0.000, 0.000, 1, 0, 0, 0.00, 'ru', 159, 0, ' 1|50', 91, '100000000', 5653877.00, 5000.00, 2, 2, NULL, NULL, NULL), (18335, 'Ka**a', 'dc7736**e64fcca', '', 'Ka**@yandex.ru', 0, '', '', NULL, 41111.00, 0.00, 0, 0, 5000.00, 1, 41000.00, 0, 1, NULL, 1591673791, 1591756434, '176.51.3.148', NULL, NULL, 'index', 5, 0, 1, NULL, 'high', 1.00, 0.000, 0.000, 0.000, 1, 0, 0, 0.00, 'ru', 0, 0, '1|5', 367, '100000000', 5659310.00, 5000.00, 1, 2, NULL, NULL, NULL), (18326, 'V**e', 'a0e7**dbf5', '', 'Vl**@mail.ru', 0, '', '', NULL, 40156.00, 0.00, 0, 0, 5000.00, 1, 5000.00, 0, 1, NULL, 1591633928, 1597033536, '89.113.143.11', NULL, NULL, 'logout', 5, 0, 1, NULL, 'high', 1.00, 0.000, 0.000, 0.000, 1, 0, 0, 0.00, 'ru', 0, 0, '1|5', 802, '100000000', 5652710.00, 5000.00, 1, 2, NULL, NULL, NULL);
так не быстрее? Код (Text): CREATE TEMPORARY TABLE IF NOT EXISTS my_user SELECT * FROM `users` WHERE `room_id` = 1 AND `status` IN (4,5,6); SELECT `t1`.* , `t2`.login AS `creator_login`, COUNT(`output`.id) AS `wait_opl` FROM `my_user` AS `t1` LEFT JOIN `users` AS `t2` ON `t1`.creator = `t2`.id LEFT JOIN `output` ON t1.login = `output`.login AND `output`.status = 0 GROUP BY `t1`.login ORDER BY `t1`.status ASC, `wait_opl` DESC, `t1`.`login` DESC LIMIT 0, 50; DROP TEMPORARY TABLE my_user; или так еще попробовать Код (Text): CREATE TEMPORARY TABLE IF NOT EXISTS my_user SELECT `users`.* , COUNT(`output`.id) AS `wait_opl` FROM `users` LEFT JOIN `output` ON `users`.login = `output`.login AND `output`.status = 0 WHERE `users`.`room_id` = 1 AND `users`.`status` IN (4,5,6) GROUP BY `users`.login; SELECT `t1`.* , `t2`.login AS `creator_login` FROM `my_user` AS `t1` LEFT JOIN `users` AS `t2` ON `t1`.creator = `t2`.id ORDER BY `t1`.status ASC, `wait_opl` DESC, `t1`.`login` DESC LIMIT 0, 50; DROP TEMPORARY TABLE my_user;
Первый вариант: # MySQL вернула пустой результат (т.е. ноль строк). Второй вариант: # MySQL вернула пустой результат (т.е. ноль строк).
Если удалить DROP TEMPORARY TABLE my_user; , то второй вариант такой Отображение строк 0 - 49 (50 всего, Запрос занял 0.0048 сек.) [status: 5 - 5] [login: SUJUNBAJAJBEK - ZHOLDASOOVA06] --- Добавлено --- Да, phpmyadmin
Код (Text): DROP TEMPORARY TABLE IF EXISTS my_user; CREATE TEMPORARY TABLE IF NOT EXISTS my_user SELECT `users`.* , COUNT(`output`.id) AS `wait_opl` FROM `users` LEFT JOIN `output` ON `users`.login = `output`.login AND `output`.status = 0 WHERE `users`.`room_id` = 1 AND `users`.`status` IN (4,5,6) GROUP BY `users`.login; SELECT `t1`.* , `t2`.login AS `creator_login` FROM `my_user` AS `t1` LEFT JOIN `users` AS `t2` ON `t1`.creator = `t2`.id ORDER BY `t1`.status ASC, `wait_opl` DESC, `t1`.`login` DESC LIMIT 0, 50;
PHP: mysql_query('CREATE TEMPORARY TABLE IF NOT EXISTS my_user SELECT `users`.* , COUNT(`output`.id) AS `wait_opl` FROM `users` LEFT JOIN `output` ON `users`.login = `output`.login AND `output`.status = 0 WHERE `users`.`room_id` = 1 AND `users`.`status` IN (4,5,6) GROUP BY `users`.login'); $sql_lim = 'SELECT `t1`.* , `t2`.login AS `creator_login` FROM `my_user` AS `t1` LEFT JOIN `users` AS `t2` ON `t1`.creator = `t2`.id ORDER BY `t1`.status ASC, `wait_opl` DESC, `t1`.`login` DESC LIMIT 0, 50'; $res = mysql_query($sql_lim); var_dump($res); mysql_query('DROP TEMPORARY TABLE IF EXISTS my_user');
Теперь в логах этот как тяжелый висит. Ничего страшного? Или овчинка выделки не стоит!?. Код (Text): Count: 5 Time=5.07s (25s) Lock=0.01s (0s) Rows_sent=0.0 (0), Rows_examined=13216.0 (66080), **@localhost CREATE TEMPORARY TABLE IF NOT EXISTS my_user SELECT `users`.* , COUNT(`output`.id) AS `wait_opl` FROM `users` LEFT JOIN `output` ON `users`.login = `output`.login AND `output`.status = N WHERE `users`.`room_id` = N AND `users`.`status` IN (N,N,N) GROUP BY `users`.login