Оптимизация нескольких запросов.

Тема в разделе "Базы данных", создана пользователем Wertos, 6 май 2018.

Модераторы: latteo
  1. Wertos

    Wertos Пьянь местная

    Регистр.:
    13 апр 2007
    Сообщения:
    159
    Симпатии:
    36
    Код:
    SELECT COUNT(distinct(t.topic_id)) AS lt_count
    FROM
    phpbb_topics AS t,
    phpbb_users AS u,
    phpbb_tracker_torrents AS tr
    WHERE
    ( t.topic_type != 3 ) AND
    tr.forum_id IN (1,2,29,30,31,32,35,70,33,10,42,43,73,78,44,45,40,41,3,18,36,37,39,4,46,47,48,49,50,51,52,53,76,6,56,57,58,59,60,61,62,63,64,65,66,5,54,55,74,7,67,68,20) AND
    t.topic_poster = u.user_id AND t.topic_approved = 1 AND  t.topic_id=tr.topic_id AND tr.forb < 1
    Код:
    SELECT COUNT(topic_id) AS num_topics
    FROM phpbb_topics
    WHERE ((topic_type = 3)
    OR topic_type = 2)
    AND topic_approved = 1
    AND topic_moved_id = 0
    AND (forum_id = 0 OR (forum_id <> 2 AND forum_id <> 29 AND forum_id <> 30 AND forum_id <> 31 AND forum_id <> 32 AND forum_id <> 35 AND forum_id <> 70 AND forum_id <> 33 AND forum_id <> 3 AND forum_id <> 18 AND forum_id <> 36 AND forum_id <> 37 AND forum_id <> 38 AND forum_id <> 39 AND forum_id <> 10 AND forum_id <> 40 AND forum_id <> 41 AND forum_id <> 42 AND forum_id <> 44 AND forum_id <> 45 AND forum_id <> 43 AND forum_id <> 4 AND forum_id <> 46 AND forum_id <> 47 AND forum_id <> 48 AND forum_id <> 49 AND forum_id <> 50 AND forum_id <> 51 AND forum_id <> 52 AND forum_id <> 53 AND forum_id <> 5 AND forum_id <> 54 AND forum_id <> 55 AND forum_id <> 6 AND forum_id <> 56 AND forum_id <> 57 AND forum_id <> 58 AND forum_id <> 59 AND forum_id <> 60 AND forum_id <> 61 AND forum_id <> 62 AND forum_id <> 63 AND forum_id <> 64 AND forum_id <> 65 AND forum_id <> 66 AND forum_id <> 7 AND forum_id <> 67 AND forum_id <> 68 AND forum_id <> 20 AND forum_id <> 17 AND forum_id <> 69))
    Последний запрос очень долгий ! База 6Гб
    Может индексов не хватает каких нибудь
     
  2. Black Hat

    Black Hat

    Регистр.:
    15 май 2015
    Сообщения:
    155
    Симпатии:
    101
    Код:
    show create table phpbb_tracker_torrents
    
    И остальных таблиц в студию.
    Код:
    EXPLAIN EXTENDED SELECT COUNT ...
    
    и второго запроса в студию.
     
  3. Wertos

    Wertos Пьянь местная

    Регистр.:
    13 апр 2007
    Сообщения:
    159
    Симпатии:
    36
    show create table phpbb_tracker_torrents
    Код:
    CREATE TABLE `phpbb_tracker_torrents` (
    `id` int(10) unsigned NOT NULL DEFAULT '0',
    `post_msg_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
    `topic_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
    `poster_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
    `info_hash` binary(20) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
    `numfiles` int(10) unsigned NOT NULL DEFAULT '0',
    `times_completed` int(10) unsigned NOT NULL DEFAULT '0',
    `leechers` int(10) unsigned NOT NULL DEFAULT '0',
    `seeders` int(10) unsigned NOT NULL DEFAULT '0',
    `size` bigint(20) unsigned NOT NULL DEFAULT '0',
    `free` tinyint(3) unsigned NOT NULL DEFAULT '0',
    `upload` tinyint(3) unsigned NOT NULL DEFAULT '0',
    `forb` tinyint(2) NOT NULL DEFAULT '0',
    `forb_user_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
    `forb_reason` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
    `forb_date` int(11) unsigned NOT NULL DEFAULT '0',
    `added` int(11) unsigned NOT NULL DEFAULT '0',
    `req_upload` bigint(20) unsigned NOT NULL DEFAULT '0',
    `req_ratio` decimal(6,3) unsigned NOT NULL DEFAULT '0.000',
    `private` tinyint(1) unsigned NOT NULL DEFAULT '0',
    `tsl_speed` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
    `ip` varchar(15) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
    `unreg` tinyint(1) unsigned NOT NULL DEFAULT '0',
    `lastseed` int(11) unsigned NOT NULL DEFAULT '0',
    `lastleech` int(11) unsigned NOT NULL DEFAULT '0',
    `forum_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
    `lastcleanup` int(11) unsigned NOT NULL DEFAULT '0',
    `rem_seeders` int(11) unsigned NOT NULL DEFAULT '0',
    `rem_leechers` int(11) unsigned NOT NULL DEFAULT '0',
    `rem_times_completed` int(11) unsigned NOT NULL DEFAULT '0',
    `lastremote` int(11) unsigned NOT NULL DEFAULT '0',
    `thanks` mediumint(8) unsigned NOT NULL DEFAULT '0',
    `lastseedreq` int(11) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    UNIQUE KEY `info_hash` (`info_hash`),
    UNIQUE KEY `id` (`id`),
    UNIQUE KEY `topic_id` (`topic_id`) USING BTREE,
    KEY `poster_id` (`poster_id`),
    KEY `post_msg_id` (`post_msg_id`),
    KEY `forb` (`forb`),
    KEY `forum_id` (`forum_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 
    
    Код:
    EXPLAIN EXTENDED SELECT COUNT(distinct(t.topic_id)) AS lt_count
    FROM
    phpbb_topics AS t,
    phpbb_users AS u,
    phpbb_tracker_torrents AS tr
    WHERE
    ( t.topic_type != 3 ) AND
    tr.forum_id IN (1,2,29,30,31,32,35,70,33,10,42,43,73,78,44,45,40,41,3,18,36,37,39,4,46,47,48,49,50,51,52,53,76,6,56,57,58,59,60,61,62,63,64,65,66,5,54,55,74,7,67,68,20) AND
    t.topic_poster = u.user_id AND t.topic_approved = 1 AND  t.topic_id=tr.topic_id AND tr.forb < 1
    1    SIMPLE    tr  
        NULL
        ALL    topic_id,forb,forum_id  
        NULL
      
        NULL
      
        NULL
        143581    50.00    Using where  
    1    SIMPLE    t  
        NULL
        eq_ref    PRIMARY,topic_id,topic_approved    PRIMARY    3    xxx.tr.topic_id    1    45.00    Using where  
    1    SIMPLE    u  
        NULL
        eq_ref    PRIMARY,user_id    PRIMARY    3    xxx.t.topic_poster    1    100.00    Using index  
    
    
     
  4. Black Hat

    Black Hat

    Регистр.:
    15 май 2015
    Сообщения:
    155
    Симпатии:
    101
    Wertos, предоставьте пожалуйста SHOW CREATE TABLE всех таблиц, участвующих в запросе. И
    EXPLAIN EXTENDED - в читабельном виде.
     
  5. M@estro

    M@estro Писатель

    Регистр.:
    12 апр 2018
    Сообщения:
    2
    Симпатии:
    1
    Для начала я бы на это безумие
    Код:
    IN (1,2,29,30,31,32,35,70,33,10,42,43,73,78,44,45,40,41,3,18,36,37,39,4,46,47,48,49,50,51,52,53,76,6,56,57,58,59,60,61,62,63,64,65,66,5,54,55,74,7,67,68,20)
    создал таблицу и уже ее джойнил в запрос.