Добрый день. Есть запрос на поиск в Названии и в Описании (там ключевые слова через запятую прописаны как теги).
Запрос по тегам:
[1] => 0.028709888458252 [SELECT t.*, bookmarks.userid, snatched.userid AS suid FROM torrents AS t LEFT JOIN bookmarks ON bookmarks.userid = 3 AND bookmarks.torrentid = t.id LEFT JOIN snatched ON snatched.userid = 3 AND snatched.torrent = t.id WHERE t.keywords LIKE '%mamma%' ORDER BY t.added DESC LIMIT 0,25]
второй запрос по жанрам:[1] => 0.025276184082031 [SELECT t.*, bookmarks.userid, snatched.userid AS suid FROM torrents AS t LEFT JOIN bookmarks ON bookmarks.userid = 3 AND bookmarks.torrentid = t.id LEFT JOIN snatched ON snatched.userid = 3 AND snatched.torrent = t.id WHERE t.description LIKE '%Аниме%' ORDER BY t.added DESC LIMIT 0,25]
Поиск идет по таблице
торрентс, потом подгружаются таблицы
snatched и
bookmarks.
Таблица с индексами:CREATE TABLE IF NOT EXISTS `torrents` (
`id` mediumint(7) unsigned NOT NULL,
`info_hash` varbinary(40) NOT NULL DEFAULT '',
`name` varchar(175) NOT NULL DEFAULT '',
`keywords` text NOT NULL,
`description` varchar(255) NOT NULL,
`filename` varchar(165) NOT NULL DEFAULT '',
`image1` varchar(15) NOT NULL,
`images_sm` varchar(15) NOT NULL,
`descr` varchar(5000) NOT NULL,
`fulldescr` mediumtext NOT NULL,
`fulldescr_html` mediumtext NOT NULL,
`info` text NOT NULL,
`category` tinyint(1) unsigned NOT NULL DEFAULT '0',
`incategory` tinyint(2) unsigned NOT NULL DEFAULT '0',
`voice` tinyint(1) unsigned NOT NULL DEFAULT '0',
`tryd` tinyint(1) unsigned NOT NULL DEFAULT '0',
`webdl` tinyint(1) unsigned NOT NULL DEFAULT '0',
`reliz` tinyint(1) unsigned NOT NULL DEFAULT '0',
`size` bigint(20) unsigned NOT NULL DEFAULT '0',
`added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`comments` tinyint(3) unsigned NOT NULL DEFAULT '0',
`views` mediumint(7) unsigned NOT NULL DEFAULT '0',
`hits` mediumint(7) unsigned NOT NULL DEFAULT '0',
`times_completed` mediumint(7) unsigned NOT NULL DEFAULT '0',
`leechers` mediumint(7) unsigned NOT NULL DEFAULT '0',
`remote_leechers` mediumint(7) unsigned NOT NULL DEFAULT '0',
`seeders` mediumint(7) unsigned NOT NULL DEFAULT '0',
`remote_seeders` mediumint(7) unsigned NOT NULL DEFAULT '0',
`last_action` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`last_mt_update` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`last_reseed` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`visible` enum('yes','no') NOT NULL DEFAULT 'yes',
`banned` enum('yes','no') NOT NULL DEFAULT 'no',
`owner` mediumint(5) unsigned NOT NULL DEFAULT '0',
`owner_name` varchar(20) NOT NULL,
`owner_class` tinyint(2) unsigned NOT NULL,
`free` enum('bril','yes','silver','no') DEFAULT 'no',
`not_sticky` enum('yes','no') NOT NULL DEFAULT 'yes',
`multitracker` enum('yes','no') NOT NULL DEFAULT 'no',
`report` enum('yes','no') DEFAULT 'no',
`allow_comments` enum('yes','no') NOT NULL DEFAULT 'yes',
`dostup` enum('adm','mod','upl','vip','uhd','1080p','user') NOT NULL DEFAULT 'mod',
`updatess` enum('yes','no') NOT NULL DEFAULT 'no',
`kptime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`kinopoisk` int(10) unsigned NOT NULL,
`imdbtime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`imdb` varchar(15) NOT NULL DEFAULT '0',
`relizs` varchar(3000) NOT NULL,
`recommend` varchar(3000) NOT NULL,
`portalid` mediumint(7) unsigned NOT NULL
) ENGINE=MyISAM AUTO_INCREMENT=1445 DEFAULT CHARSET=utf8;
ALTER TABLE `torrents`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `info_hash` (`info_hash`),
ADD UNIQUE KEY `owner` (`owner`,`id`,`owner_name`,`owner_class`) USING BTREE,
ADD UNIQUE KEY `views_id_name_images_sm` (`views`,`id`,`name`,`images_sm`) USING BTREE,
ADD UNIQUE KEY `name_id` (`name`,`id`) USING BTREE,
ADD UNIQUE KEY `multitracker_added` (`multitracker`,`added`) USING BTREE,
ADD UNIQUE KEY `voice_added` (`voice`,`added`) USING BTREE,
ADD UNIQUE KEY `webdl_added` (`webdl`,`added`) USING BTREE,
ADD UNIQUE KEY `tryd_added` (`tryd`,`added`) USING BTREE,
ADD UNIQUE KEY `incategory_added` (`incategory`,`added`) USING BTREE,
ADD UNIQUE KEY `reliz_added` (`reliz`,`added`) USING BTREE,
ADD UNIQUE KEY `name_added` (`name`,`added`) USING BTREE,
ADD UNIQUE KEY `category_added` (`category`,`added`) USING BTREE,
ADD UNIQUE KEY `added` (`added`),
ADD UNIQUE KEY `free_added` (`free`,`added`) USING BTREE,
ADD KEY `visible` (`visible`),
ADD KEY `size` (`size`) USING BTREE,
ADD KEY `dostup` (`dostup`) USING BTREE,
ADD KEY `updatess` (`updatess`) USING BTREE,
ADD KEY `visible_added` (`visible`,`added`) USING BTREE,
ADD KEY `seeders_id` (`seeders`,`id`) USING BTREE,
ADD KEY `kinopoisk` (`kptime`,`id`,`kinopoisk`) USING BTREE,
ADD KEY `id_dostup_owner` (`id`,`dostup`,`owner`) USING BTREE,
ADD KEY `remote_seeders_id` (`remote_seeders`,`id`) USING BTREE,
ADD KEY `remote_leechers_id` (`remote_leechers`,`id`) USING BTREE,
ADD KEY `seeders_added` (`seeders`,`added`) USING BTREE,
ADD KEY `leechers_seeders` (`leechers`,`seeders`),
ADD FULLTEXT KEY `description` (`description`);
ALTER TABLE `torrents`
ADD FULLTEXT KEY `keywords` (`keywords`);
Можно улучшить поиск или убрать LIKE ? Пробовал через:
WHERE CONTAINS(keywords, $dsearchstr);
где
$dsearchstr - введеное слово через текстовое поле.
И поиск ничего не находит, хотя через LIKE находит все релизы где прописаны слова-ключи.
Где я ошибся ? Спасибо за ответ.
Отредактированно Nemezida (16.06.2021 20:36:42)
