sql structure
PHP Code:
CREATE TABLE IF NOT EXISTS `torrents` ( `id` int(10) unsigned NOT NULL, `info_hash` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '', `filename` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `save_as` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '', `search_text` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL, `descr` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `ori_descr` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL, `category` int(10) unsigned NOT NULL DEFAULT 0, `size` bigint(20) unsigned NOT NULL DEFAULT 0, `added` datetime NOT NULL DEFAULT '2000-01-01 01:01:01', `type` enum('single','multi') CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT 'single', `numfiles` int(10) unsigned NOT NULL DEFAULT 0, `comments` int(10) unsigned NOT NULL DEFAULT 0, `views` int(10) unsigned NOT NULL DEFAULT 0, `hits` int(10) unsigned NOT NULL DEFAULT 0, `times_completed` int(10) unsigned NOT NULL DEFAULT 0, `leechers` int(10) unsigned NOT NULL DEFAULT 5, `seeders` int(10) unsigned NOT NULL DEFAULT 10, `last_action` datetime NOT NULL DEFAULT '2000-01-01 01:01:01', `visible` enum('yes','no') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'yes', `banned` enum('yes','no') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'no', `owner` int(10) unsigned NOT NULL DEFAULT 0, `numratings` int(10) unsigned NOT NULL DEFAULT 0, `ratingsum` int(10) unsigned NOT NULL DEFAULT 0, `nfo` text CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL, `free` enum('yes','no') CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT 'no', `smalld` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL, `recommended` enum('yes','no') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'no', `poster` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL, `bgsubs` enum('yes','no') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'no', `bgaudio` enum('yes','no') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'no', `genre` varchar(40) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL, `genre1` varchar(40) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL, `genre2` varchar(40) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL, `sticky` enum('yes','no') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'no', `pretime` datetime NOT NULL DEFAULT '2000-01-01 01:01:01', `afterpre` text CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL, `url` varchar(80) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL, `double_upload` enum('yes','no') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'no', `seen` blob NOT NULL, `subs` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '', `external` blob NOT NULL, `x2` enum('yes','no') CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT 'no', `description` varchar(120) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '', `anon` enum('yes','no') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'no', `announce` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '', `image1` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL, `image2` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL, `torrentlang` int(10) unsigned NOT NULL DEFAULT 1, `torrent` int(10) unsigned NOT NULL, `path` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `filesize` int(10) unsigned NOT NULL, `tube` varchar(80) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '', `updated` enum('0','1') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '0', `imdb` varchar(125) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, `addbookmark` char(3) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'no', `thanks` longtext CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL, `points` int(10) NOT NULL DEFAULT 0, `freeleech` enum('0','1') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '1', `trailers` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL, `viptorrents` text CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, `recommendedx` enum('yes','no') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no', `top` enum('yes','no') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no', `likes` int(10) unsigned NOT NULL DEFAULT 0, `love` int(10) unsigned NOT NULL DEFAULT 0, `dislike` int(10) unsigned NOT NULL DEFAULT 0, `angry` int(10) unsigned NOT NULL DEFAULT 0, `wow` int(10) unsigned NOT NULL DEFAULT 0, `rosubbed` enum('yes','no') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no', `hardcoded` enum('yes','no') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no', `italiansub` enum('yes','no') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no', `dublatro` enum('yes','no') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no', `dubbed` enum('yes','no') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no', `multilang` enum('yes','no') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no', `remux` enum('yes','no') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no', `HDR` enum('yes','no') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no', `3d` enum('yes','no') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no', `flacdst` enum('yes','no') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no', `adson` enum('yes','no') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no', `VR` enum('yes','no') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no', `frenchsub` enum('yes','no') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no', `russiansub` enum('yes','no') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no', `engsub` enum('yes','no') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no', `germansub` enum('yes','no') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no', `4khdr` enum('yes','no') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no', `UHD` enum('yes','no') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no', `fhd` enum('yes','no') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no', `SD` enum('yes','no') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no', `KoreanHC` enum('yes','no') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no', `tmdb` varchar(125) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;---- Indexes for dumped tables------ Indexes for table `torrents`--ALTER TABLE `torrents` ADD PRIMARY KEY (`id`) USING BTREE, ADD UNIQUE KEY `info_hash` (`info_hash`), ADD KEY `owner` (`owner`), ADD KEY `visible` (`visible`), ADD KEY `category_visible` (`category`,`visible`) USING BTREE, ADD KEY `id` (`id`), ADD KEY `id_2` (`id`), ADD KEY `category` (`category`), ADD KEY `idx_torrents_category` (`category`), ADD KEY `idx_torrents_torrentlang` (`torrentlang`), ADD KEY `idx_torrents_owner` (`owner`), ADD FULLTEXT KEY `ft_search` (`search_text`,`ori_descr`);ALTER TABLE `torrents` ADD FULLTEXT KEY `name` (`name`);---- AUTO_INCREMENT for dumped tables------ AUTO_INCREMENT for table `torrents`--ALTER TABLE `torrents` MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT;/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
how can i add force index in this
Quote:
|
//Get Total For Pager$res = mysql_query("SELECT COUNT(*) FROM torrents LEFT JOIN categories ON category = categories.id $where") or die(mysql_error());$row = mysql_fetch_array($res);$count = $row[0];//get sql infoif ($count) { list($pagertop, $pagerbottom, $limit) = pager(35, $count, "torrents.php?" . $addparam);$query = "SELECT torrents.id, torrents.category, ...., torrents.name, torrents.size, torrents.added, torrents.torrentlang, torrents.owner, categories.name AS cat_name, torrentlang.name AS lang_name, torrentlang.image AS lang_image, categories.parent_cat AS cat_parent, categories.image AS cat_pic, users.username, users.privacy, IF(torrents.numratings < 2, NULL, ROUND(torrents.ratingsum / torrents.numratings, 1)) AS rating FROM torrents LEFT JOIN categories ON category = categories.id LEFT JOIN torrentlang ON torrents.torrentlang = torrentlang.id LEFT JOIN users ON torrents.owner = users.id $where $orderby $limit" ; $res = mysql_query($query) or die(mysql_error());}else{ unset($res);}
|
Last edited by Extremlym; 18th June 2023 at 19:38.
|