Хранение тегов

[yunasc]

Вот думаю как хранить теги в базе данных лучше всего, что-б получить быстрый поиск по ним, без использования Sphinx итд.

FULLTEXT - фигня т.к завязка на mySQL
Sphinx - на хостинге не поставишь
LIKE '%tag%' - медленно

Пока-что пришел в такому варианту, но не устраивает план выполнения запроса:

SELECT t.id, t.moderated, t.moderatedby, t.category, t.leechers, t.seeders, t.free,
t.name, t.times_completed, t.size, t.added, t.comments, t.numfiles, t.filename, t.not_sticky,
t.owner, IF(t.numratings < 1, NULL, ROUND(t.ratingsum / t.numratings, 1)) AS rating,
c.name AS cat_name, c.image AS cat_pic, u.username, u.class, EXISTS(
SELECT *
FROM readtorrents
WHERE readtorrents.userid = 1 AND readtorrents.torrentid = t.id) AS readtorrent
FROM torrents AS t
LEFT JOIN categories AS c ON t.category = c.id
LEFT JOIN users AS u ON t.owner = u.id
INNER JOIN tag_index AS ti ON ti.tid = t.id
INNER JOIN tags AS ta ON ta.id = ti.tag
WHERE t.visible = 'yes' AND (ta.name = 'elementary' OR ta.name = '720p')
GROUP BY t.id
ORDER BY t.not_sticky DESC, t.id DESC
LIMIT 0,25

Ну и сами таблицы вот:

CREATE TABLE `tags` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL DEFAULT '',
    `count` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
) ENGINE=MyISAM;

INSERT INTO `tags` (`id`, `name`, `count`) VALUES (1, 'elementary', 1);
INSERT INTO `tags` (`id`, `name`, `count`) VALUES (2, 'marvel', 1);
INSERT INTO `tags` (`id`, `name`, `count`) VALUES (3, '720p', 2);

CREATE TABLE `tag_index` (
    `tid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    `tag` INT(10) UNSIGNED NULL DEFAULT '0',
    UNIQUE INDEX `tagged` (`tid`, `tag`)
) ENGINE=MyISAM;

INSERT INTO `tag_index` (`tid`, `tag`) VALUES (1, 1);
INSERT INTO `tag_index` (`tid`, `tag`) VALUES (1, 3);
INSERT INTO `tag_index` (`tid`, `tag`) VALUES (2, 2);
INSERT INTO `tag_index` (`tid`, `tag`) VALUES (2, 3);

INSERT INTO `torrents` (`id`, `info_hash`, `name`, `filename`, `save_as`, `descr`, `ori_descr`, `image1`, `image2`, `image3`, `image4`, `image5`, `category`, `size`, `added`, `type`, `numfiles`, `comments`, `views`, `hits`, `times_completed`, `leechers`, `seeders`, `last_action`, `last_reseed`, `visible`, `banned`, `owner`, `numratings`, `ratingsum`, `free`, `not_sticky`, `moderated`, `moderatedby`, `keywords`, `description`) VALUES (1, 0x32393761383636333965333862386137393764353336306563663836303164386265363130343638, 'Elementary.S02E14.720p.HDTV.X264-DIMENSION.mkv', 'Elementary.S02E14.720p.HDTV.X264-DIMENSION.mkv.torrent', 'Elementary.S02E14.720p.HDTV.X264-DIMENSION.mkv', 'Elementary S02E14 720p DIMENSION', 'Elementary S02E14 720p DIMENSION', '', '', '', '', '', 11, 887499089, '2014-02-24 14:46:48', 'single', 1, 0, 1, 0, 0, 0, 0, '2014-02-24 14:46:48', '0000-00-00 00:00:00', 'yes', 'no', 1, 0, 0, 'silver', 'yes', 'yes', 1, '', '');
INSERT INTO `torrents` (`id`, `info_hash`, `name`, `filename`, `save_as`, `descr`, `ori_descr`, `image1`, `image2`, `image3`, `image4`, `image5`, `category`, `size`, `added`, `type`, `numfiles`, `comments`, `views`, `hits`, `times_completed`, `leechers`, `seeders`, `last_action`, `last_reseed`, `visible`, `banned`, `owner`, `numratings`, `ratingsum`, `free`, `not_sticky`, `moderated`, `moderatedby`, `keywords`, `description`) VALUES (2, 0x61653536626238323165656239643739366566633934336264383063376661666536616531396366, 'Thor.The.Dark.World.2013.720p.WEB-DL.Rus.Eng.HDCLUB.mkv', 'Thor.The.Dark.World.2013.720p.WEB-DL.Rus.Eng.HDCLUB.mkv.torrent', 'Thor.The.Dark.World.2013.720p.WEB-DL.Rus.Eng.HDCLUB.mkv', 'Thor', 'Thor', '', '', '', '', '', 14, 4099320914, '2014-02-27 12:43:21', 'single', 1, 0, 0, 0, 0, 0, 0, '2014-02-27 12:43:21', '0000-00-00 00:00:00', 'yes', 'no', 1, 0, 0, 'no', 'yes', 'yes', 1, '', '');

+----+--------------------+--------------+--------+----------------------+---------+---------+------------+------+---------------------------------------------------------------------+
| id | select_type        | table        | type   | possible_keys        | key     | key_len | ref        | rows | extra                                                               |
+----+--------------------+--------------+--------+----------------------+---------+---------+------------+------+---------------------------------------------------------------------+
|  1 | PRIMARY            | t            | ref    | PRIMARY,visible,vnsi | visible | 1       | const      | 1    | Using index condition; Using where; Using temporary; Using filesort |
|  1 | PRIMARY            | c            | eq_ref | PRIMARY              | PRIMARY | 4       | t.category | 1    |                                                                     |
|  1 | PRIMARY            | u            | eq_ref | PRIMARY,user         | PRIMARY | 4       | t.owner    | 1    |                                                                     |
|  1 | PRIMARY            | ti           | ref    | tagged               | tagged  | 4       | t.id       | 1    | Using where; Using index                                            |
|  1 | PRIMARY            | ta           | eq_ref | PRIMARY              | PRIMARY | 4       | ti.tag     | 1    | Using where                                                         |
|  2 | DEPENDENT SUBQUERY | readtorrents | eq_ref | read                 | read    | 8       | const,func | 1    | Using where; Using index                                            |
+----+--------------------+--------------+--------+----------------------+---------+---------+------------+------+---------------------------------------------------------------------+

И вот результат, как по мне так ужасен...

У кого какие идеи?

[Apmyp]

Стоит попробовать тэги тянуть без джойнов.
Замерить скорость когда тэгов скажем 10к, 100к.

Еще словарь в памяти держать можно - обходить быстрее.

[mft39]

Можно ещё глянуть, как на Gazelle это реализовано. https://github.com/WhatCD/Gazelle

[yunasc]

HighQuality, что именно ты предлагаешь?
Не понял

[Apmyp]

Зачем париться на счет скорости, если прямо сейчас проблем нет?

[yunasc]

Затем что лучше пусть оно сразу работает чуть-чуть медленней, чем потом работает медленно.

[vanechek233]

а может забить на хостинг и использовать сфинкс, ибо сейчас vds любому нищеброду по карману.

[yunasc]

не хочу забивать на хостинги, ибо много кто ставит трекера дома, под виндой.

я знаю что это плохо и такой трекер не проживет долго, ибо скорее всего винду убьет, или диск сдохнет.
но под виндой поднимать сфинкс - не так просто.
цель именно что скрипт установки делает всё за тебя, а скрипт сис админом работать не умеет.

но ты можешь дать конфиг сфинкса под теги и пример использования (я со сфинксом не работал), могу поколупать...

[Apmyp]

Не Sphinx, а elasticsearch ;)

[yunasc]

Всё равно одна картина...

[vanechek233]

а в чем преимущество elasticsearch над sphinx

[yunasc]

realtime индексы без ограничений.

я все еще жду примера конфига под sphinx для замены поиска по torrents.tags LIKE '%тег%'

[yunasc]

Кажется что наиболее разумным вариантом, в таком случае, будет сначала сделать

$ids_list = mysql_fetch_row(sql_query(
"SELECT GROUP_CONCAT(t.id) FROM torrents INNER JOIN torrent_tags AS tt ON tt.tid = t.id
WHERE tt.tag IN ('animation', 'pr0n', 'action') GROUP BY t.id
ORDER BY t.not_sticky DESC, t.id DESC LIMIT 0,25"));

А потом в browse.php делать выборку по этим ID:

WHERE t.id IN ($ids_list)

Для блока релизов такая фишка серьезно ускорила его работу (когда релизов, у меня, уже больше чем 10000)

Есть идеи получше?