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

Wertos

Пьянь местная
Регистрация
13 Апр 2007
Сообщения
178
Реакции
39
Код:
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Гб
Может индексов не хватает каких нибудь
 
Код:
show create table phpbb_tracker_torrents
И остальных таблиц в студию.
Код:
EXPLAIN EXTENDED SELECT COUNT ...
и второго запроса в студию.
 
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
 
Wertos, предоставьте пожалуйста SHOW CREATE TABLE всех таблиц, участвующих в запросе. И
EXPLAIN EXTENDED - в читабельном виде.
 
Для начала я бы на это безумие
Код:
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)
создал таблицу и уже ее джойнил в запрос.
 
Назад
Сверху