За последние 24 часа нас посетили 20258 программистов и 1080 роботов. Сейчас ищут 757 программистов ...

Как оптимизировать запрос?

Тема в разделе "MySQL", создана пользователем AlexProg, 20 авг 2020.

  1. AlexProg

    AlexProg Активный пользователь

    С нами с:
    13 май 2014
    Сообщения:
    320
    Симпатии:
    7
    Всем добра!

    Нашелся такой

    Код (Text):
    1. Count: 3  Time=5.17s (15s)  Lock=2.28s (6s)  Rows_sent=50.0 (150), Rows_examined=17590.3 (52771), casino777[casino777]@localhost
    2.   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):
    1. select t1.*, t2.login as creator_login,
    2. 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 )
    3. where t1.room_id=1 and t1.status in (4,5,6)
    4. group by t1.login
    5. order by t1.status asc, wait_opl desc, login desc LIMIT 0, 50
    Но как не помогло: Отображение строк 0 - 49 (50 всего, Запрос занял 5.2400 сек.) [status: 5 - 5]

    Как еще можно его оптимизировать?

    Спасибо!
     
  2. ADSoft

    ADSoft Старожил

    С нами с:
    12 мар 2007
    Сообщения:
    3.817
    Симпатии:
    735
    Адрес:
    Татарстан
    Индексы нужные есть?
     
    AlexProg нравится это.
  3. AlexProg

    AlexProg Активный пользователь

    С нами с:
    13 май 2014
    Сообщения:
    320
    Симпатии:
    7
    Вот всё что есть. photo_2020-08-20_17-38-42.jpg
     
  4. Artur_hopf

    Artur_hopf Активный пользователь

    С нами с:
    7 май 2018
    Сообщения:
    2.266
    Симпатии:
    405
    скинь дамп таблиц, полюбому запрос кривой из за from users t1 left join users
     
    AlexProg нравится это.
  5. AlexProg

    AlexProg Активный пользователь

    С нами с:
    13 май 2014
    Сообщения:
    320
    Симпатии:
    7
    Всех строк или структуру только?
     
  6. Artur_hopf

    Artur_hopf Активный пользователь

    С нами с:
    7 май 2018
    Сообщения:
    2.266
    Симпатии:
    405
    ну можешь рандомных строк создать штук 50
     
    AlexProg нравится это.
  7. AlexProg

    AlexProg Активный пользователь

    С нами с:
    13 май 2014
    Сообщения:
    320
    Симпатии:
    7
    Код (Text):
    1. CREATE TABLE IF NOT EXISTS `output` (
    2.   `id` int(5) NOT NULL,
    3.   `inv_code` char(10) DEFAULT NULL,
    4.   `login` char(20) NOT NULL DEFAULT '',
    5.   `ps` varchar(50) DEFAULT NULL,
    6.   `status` smallint(1) NOT NULL DEFAULT '0',
    7.   `date` int(10) NOT NULL DEFAULT '0',
    8.   `sum` decimal(7,2) NOT NULL DEFAULT '0.00',
    9.   `sum_out` decimal(7,2) NOT NULL DEFAULT '0.00'
    10. ) ENGINE=MyISAM AUTO_INCREMENT=40303 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
    11.  
    12. --
    13. -- Индексы таблицы `output`
    14. --
    15. ALTER TABLE `output`
    16.   ADD PRIMARY KEY (`id`);
    17.  
    18. --
    19. -- AUTO_INCREMENT для сохранённых таблиц
    20. --
    21.  
    22. --
    23. -- AUTO_INCREMENT для таблицы `output`
    24. --
    25. ALTER TABLE `output`
    26.   MODIFY `id` int(5) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=40303;
    27.  
    28. INSERT INTO `output` (`id`, `inv_code`, `login`, `ps`, `status`, `date`, `sum`, `sum_out`) VALUES
    29. (1, '689RKR6', 'bo**0', 'QIWI_7**1', 3, 1559575208, 4000.00, 3575.00),
    30. (38296, 'SJJLGT2', 'al**n', 'QIWI_7**5', 3, 1597048808, 7000.00, 6275.00),
    31. (38297, 'DWW008E', 'Vl**91', 'VISA/MASTERCARD_4**6', 3, 1597050178, 45000.00, 40475.00),
    32. (38298, 'TK65J39', 'te**4', 'QIWI_7**3', 3, 1597050729, 30000.00, 26975.00),
    33. (38299, 'MBOV31M', 'da**7', 'QIWI_7**8', 2, 1597053265, 11500.00, 10325.00),
    34. (38300, '8532ENJ', 'V**91', 'VISA/MASTERCARD_47**6', 3, 1597053423, 45000.00, 40475.00);

    Код (Text):
    1.  
    2. CREATE TABLE IF NOT EXISTS `users` (
    3.   `id` int(5) NOT NULL,
    4.   `login` char(20) NOT NULL DEFAULT '',
    5.   `pass` char(32) NOT NULL DEFAULT '',
    6.   `pin` char(4) NOT NULL DEFAULT '',
    7.   `email` varchar(100) DEFAULT NULL,
    8.   `mail_active_status` tinyint(1) NOT NULL DEFAULT '0',
    9.   `fio` varchar(100) NOT NULL DEFAULT '',
    10.   `wmr` char(13) NOT NULL DEFAULT '',
    11.   `qiwi` varchar(18) DEFAULT NULL,
    12.   `balance` decimal(14,2) DEFAULT '0.00',
    13.   `balance_bonus` decimal(14,2) DEFAULT '0.00',
    14.   `wager` int(11) NOT NULL DEFAULT '0',
    15.   `wager_bonus` int(11) NOT NULL DEFAULT '0',
    16.   `pay_points` decimal(14,2) DEFAULT '0.00',
    17.   `can_outpay` tinyint(1) NOT NULL DEFAULT '0',
    18.   `demobalance` decimal(14,2) DEFAULT '0.00',
    19.   `demomode` int(1) DEFAULT '0',
    20.   `creator` int(1) NOT NULL DEFAULT '1',
    21.   `ref_id` int(11) DEFAULT NULL COMMENT 'id юзера превлекшего данного игрока',
    22.   `reg_time` int(10) NOT NULL DEFAULT '0',
    23.   `go_time` int(10) NOT NULL DEFAULT '0',
    24.   `ip` char(15) DEFAULT '0',
    25.   `useragent` varchar(500) DEFAULT NULL,
    26.   `os` varchar(50) DEFAULT NULL,
    27.   `last_ge` varchar(50) NOT NULL DEFAULT 'root',
    28.   `status` smallint(1) NOT NULL DEFAULT '5',
    29.   `action` int(2) NOT NULL DEFAULT '0',
    30.   `room_id` int(10) NOT NULL DEFAULT '1',
    31.   `comment` varchar(150) DEFAULT NULL,
    32.   `graf_kind` enum('low','medium','high') NOT NULL DEFAULT 'high',
    33.   `denomination` decimal(5,2) NOT NULL DEFAULT '1.00',
    34.   `spin_bank` decimal(13,3) DEFAULT '0.000',
    35.   `bonus_bank` decimal(13,3) DEFAULT '0.000',
    36.   `double_bank` decimal(13,3) DEFAULT '0.000',
    37.   `sound` int(1) DEFAULT '1',
    38.   `preset_id` int(10) DEFAULT '0',
    39.   `point_on` int(1) NOT NULL DEFAULT '0',
    40.   `point` decimal(14,2) DEFAULT '0.00',
    41.   `lang` char(2) DEFAULT 'ru',
    42.   `payed_spins` int(11) NOT NULL DEFAULT '0',
    43.   `curspin` int(5) NOT NULL DEFAULT '0',
    44.   `garant` varchar(10) DEFAULT '1|5',
    45.   `curspin_bonus` int(5) NOT NULL DEFAULT '0',
    46.   `garant_bonus` varchar(10) DEFAULT '100000000',
    47.   `payin` decimal(12,2) DEFAULT '0.00',
    48.   `payin_total` decimal(12,2) DEFAULT '0.00',
    49.   `gift` int(1) DEFAULT NULL COMMENT '1-рега, 2-ежедневка,3-первый депозит',
    50.   `rating` int(3) NOT NULL DEFAULT '1',
    51.   `firstname` varchar(50) DEFAULT NULL,
    52.   `lastname` varchar(50) DEFAULT NULL,
    53.   `birthday` date DEFAULT NULL
    54. ) ENGINE=MyISAM AUTO_INCREMENT=22766 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
    55.  
    56. --
    57. -- Индексы таблицы `users`
    58. --
    59. ALTER TABLE `users`
    60.   ADD PRIMARY KEY (`id`),
    61.   ADD UNIQUE KEY `login` (`login`) USING BTREE,
    62.   ADD UNIQUE KEY `qiwi` (`qiwi`) USING BTREE,
    63.   ADD UNIQUE KEY `email` (`email`) USING BTREE,
    64.   ADD KEY `point_on` (`point_on`) USING BTREE,
    65.   ADD KEY `status` (`status`) USING BTREE,
    66.   ADD KEY `action` (`action`) USING BTREE;
    67.  
    68. --
    69. -- AUTO_INCREMENT для сохранённых таблиц
    70. --
    71.  
    72. --
    73. -- AUTO_INCREMENT для таблицы `users`
    74. --
    75. ALTER TABLE `users`
    76.   MODIFY `id` int(5) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=22766;
    77.  
    78. 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
    79. (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),
    80. (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),
    81. (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);
     
  8. Artur_hopf

    Artur_hopf Активный пользователь

    С нами с:
    7 май 2018
    Сообщения:
    2.266
    Симпатии:
    405
    Смотри . это условие у тебя не работает output.status =0 оно нужно?
     
  9. AlexProg

    AlexProg Активный пользователь

    С нами с:
    13 май 2014
    Сообщения:
    320
    Симпатии:
    7
    Нужно!
    оно работает. выводит выше всех у кого заявка на выплату (output status = 0 )
     
  10. Artur_hopf

    Artur_hopf Активный пользователь

    С нами с:
    7 май 2018
    Сообщения:
    2.266
    Симпатии:
    405
    так не быстрее?
    Код (Text):
    1. CREATE TEMPORARY TABLE IF NOT EXISTS my_user
    2. SELECT *
    3. FROM `users`
    4. WHERE `room_id` = 1
    5. AND `status` IN (4,5,6);
    6.  
    7. SELECT `t1`.* , `t2`.login AS `creator_login`, COUNT(`output`.id) AS `wait_opl`
    8. FROM `my_user` AS `t1`
    9. LEFT JOIN `users` AS `t2` ON `t1`.creator = `t2`.id
    10. LEFT JOIN `output` ON t1.login = `output`.login AND `output`.status = 0
    11. GROUP BY `t1`.login
    12. ORDER BY `t1`.status ASC, `wait_opl` DESC, `t1`.`login` DESC
    13. LIMIT 0, 50;
    14.  
    15. DROP TEMPORARY TABLE my_user;
    или так еще попробовать
    Код (Text):
    1. CREATE TEMPORARY TABLE IF NOT EXISTS my_user
    2. SELECT `users`.* , COUNT(`output`.id) AS `wait_opl`
    3. FROM `users`
    4. LEFT JOIN `output` ON `users`.login = `output`.login AND `output`.status = 0
    5. WHERE `users`.`room_id` = 1
    6. AND `users`.`status` IN (4,5,6)
    7. GROUP BY `users`.login;
    8.  
    9. SELECT `t1`.* , `t2`.login AS `creator_login`
    10. FROM `my_user` AS `t1`
    11. LEFT JOIN `users` AS `t2` ON `t1`.creator = `t2`.id
    12. ORDER BY `t1`.status ASC, `wait_opl` DESC, `t1`.`login` DESC
    13. LIMIT 0, 50;
    14.  
    15. DROP TEMPORARY TABLE my_user;
     
    AlexProg нравится это.
  11. AlexProg

    AlexProg Активный пользователь

    С нами с:
    13 май 2014
    Сообщения:
    320
    Симпатии:
    7
    Первый вариант: # MySQL вернула пустой результат (т.е. ноль строк).

    Второй вариант: # MySQL вернула пустой результат (т.е. ноль строк).
     
  12. Artur_hopf

    Artur_hopf Активный пользователь

    С нами с:
    7 май 2018
    Сообщения:
    2.266
    Симпатии:
    405
    вы получсаете результат отсюда я так понал DROP TEMPORARY TABLE my_user;, через php смотрите?
     
    AlexProg нравится это.
  13. AlexProg

    AlexProg Активный пользователь

    С нами с:
    13 май 2014
    Сообщения:
    320
    Симпатии:
    7
    Если удалить DROP TEMPORARY TABLE my_user; , то второй вариант такой

    Отображение строк 0 - 49 (50 всего, Запрос занял 0.0048 сек.) [status: 5 - 5] [login: SUJUNBAJAJBEK - ZHOLDASOOVA06]
    --- Добавлено ---
    Да, phpmyadmin
     
  14. Artur_hopf

    Artur_hopf Активный пользователь

    С нами с:
    7 май 2018
    Сообщения:
    2.266
    Симпатии:
    405
    быстро потому что вы не удалили таблицу и она осталась с прошлого запроса.
     
    AlexProg нравится это.
  15. AlexProg

    AlexProg Активный пользователь

    С нами с:
    13 май 2014
    Сообщения:
    320
    Симпатии:
    7
    Так как быть?

    Удаляю часть кода - пусто вообще.
    Не удаляю - летает ))
     
    Drunkenmunky нравится это.
  16. Artur_hopf

    Artur_hopf Активный пользователь

    С нами с:
    7 май 2018
    Сообщения:
    2.266
    Симпатии:
    405
    после выборки обязательно удалять временную таблицу, вот и все.
     
  17. Artur_hopf

    Artur_hopf Активный пользователь

    С нами с:
    7 май 2018
    Сообщения:
    2.266
    Симпатии:
    405
    Код (Text):
    1. DROP TEMPORARY TABLE IF EXISTS my_user;
    2.  
    3. CREATE TEMPORARY TABLE IF NOT EXISTS my_user
    4. SELECT `users`.* , COUNT(`output`.id) AS `wait_opl`
    5. FROM `users`
    6. LEFT JOIN `output` ON `users`.login = `output`.login AND `output`.status = 0
    7. WHERE `users`.`room_id` = 1
    8. AND `users`.`status` IN (4,5,6)
    9. GROUP BY `users`.login;
    10.  
    11. SELECT `t1`.* , `t2`.login AS `creator_login`
    12. FROM `my_user` AS `t1`
    13. LEFT JOIN `users` AS `t2` ON `t1`.creator = `t2`.id
    14. ORDER BY `t1`.status ASC, `wait_opl` DESC, `t1`.`login` DESC
    15. LIMIT 0, 50;
     
  18. AlexProg

    AlexProg Активный пользователь

    С нами с:
    13 май 2014
    Сообщения:
    320
    Симпатии:
    7
    Код (Text):
    1. $res = mysql_query($sql_lim);
    2. var_dump($res);
    false хоть убей

    P.S. За древний mysql знаю :(
     
  19. Artur_hopf

    Artur_hopf Активный пользователь

    С нами с:
    7 май 2018
    Сообщения:
    2.266
    Симпатии:
    405
    PHP:
    1. mysql_query('CREATE TEMPORARY TABLE IF NOT EXISTS my_user
    2. SELECT `users`.* , COUNT(`output`.id) AS `wait_opl`
    3. FROM `users`
    4. LEFT JOIN `output` ON `users`.login = `output`.login AND `output`.status = 0
    5. WHERE `users`.`room_id` = 1
    6. AND `users`.`status` IN (4,5,6)
    7. GROUP BY `users`.login');
    8.  
    9. $sql_lim = 'SELECT `t1`.* , `t2`.login AS `creator_login`
    10. FROM `my_user` AS `t1`
    11. LEFT JOIN `users` AS `t2` ON `t1`.creator = `t2`.id
    12. ORDER BY `t1`.status ASC, `wait_opl` DESC, `t1`.`login` DESC
    13. LIMIT 0, 50';
    14.  
    15. $res = mysql_query($sql_lim);
    16.  
    17. var_dump($res);
    18.  
    19. mysql_query('DROP TEMPORARY TABLE IF EXISTS my_user');
     
    AlexProg нравится это.
  20. AlexProg

    AlexProg Активный пользователь

    С нами с:
    13 май 2014
    Сообщения:
    320
    Симпатии:
    7
    Теперь в логах этот как тяжелый висит. Ничего страшного? Или овчинка выделки не стоит!?.

    Код (Text):
    1. Count: 5  Time=5.07s (25s)  Lock=0.01s (0s)  Rows_sent=0.0 (0), Rows_examined=13216.0 (66080), **@localhost
    2.   CREATE TEMPORARY TABLE IF NOT EXISTS my_user
    3.   SELECT `users`.* , COUNT(`output`.id) AS `wait_opl`
    4.   FROM `users`
    5.   LEFT JOIN `output` ON `users`.login = `output`.login AND `output`.status = N
    6.   WHERE `users`.`room_id` = N
    7.   AND `users`.`status` IN (N,N,N)
    8.   GROUP BY `users`.login
     
  21. Drunkenmunky

    Drunkenmunky Активный пользователь

    С нами с:
    12 авг 2020
    Сообщения:
    1.476
    Симпатии:
    281
    Так и не удаляйте. Обновляйте.
    В связанные таблицы добавьте триггеры.
     
  22. AlexProg

    AlexProg Активный пользователь

    С нами с:
    13 май 2014
    Сообщения:
    320
    Симпатии:
    7
    Какие?
    Будет ли смысл?
    Пока так же 5 секунд.
     
  23. Drunkenmunky

    Drunkenmunky Активный пользователь

    С нами с:
    12 авг 2020
    Сообщения:
    1.476
    Симпатии:
    281
    Промежуточные результаты объединений, группировок, на которые тратится основное время.