CREATE TABLE `videos` (
`id` int(11) NOT NULL,
`partner_id` int(11) NOT NULL DEFAULT 0,
`server_group_id` int(11) NOT NULL DEFAULT 0,
`type_id` tinyint(1) UNSIGNED NOT NULL DEFAULT 0,
`feed_id` int(11) UNSIGNED NOT NULL DEFAULT 0,
`name` varchar(240) DEFAULT NULL,
`name_en` varchar(240) DEFAULT NULL,
`year` tinyint(4) NOT NULL DEFAULT 0,
`tag_ids` varchar(240) DEFAULT NULL,
`descr` text DEFAULT NULL,
`duration` varchar(40) DEFAULT NULL,
`is_hd` tinyint(1) NOT NULL DEFAULT 0,
`slug` varchar(244) DEFAULT NULL,
`location` varchar(500) DEFAULT NULL,
`add_date` datetime NOT NULL,
`published` tinyint(1) UNSIGNED NOT NULL DEFAULT 1,
`is_review_need` tinyint(1) UNSIGNED NOT NULL DEFAULT 0,
`status_id` tinyint(2) NOT NULL DEFAULT 0,
`translate_status_id` tinyint(2) NOT NULL DEFAULT 0,
`player_data` text DEFAULT NULL,
`videos_data` text DEFAULT NULL,
`format_files` text DEFAULT NULL,
`rotations` text DEFAULT NULL,
`embed` text DEFAULT NULL,
`related_ids` varchar(240) DEFAULT NULL,
`parts_ids` varchar(244) DEFAULT NULL,
`external_key` varchar(32) DEFAULT NULL,
`views` int(11) UNSIGNED NOT NULL DEFAULT 0,
`likes` int(11) UNSIGNED NOT NULL DEFAULT 0,
`dislikes` int(11) UNSIGNED NOT NULL DEFAULT 0,
`translated` tinyint(1) UNSIGNED NOT NULL DEFAULT 0,
`flag` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
`total_comments` int(11) UNSIGNED NOT NULL DEFAULT 0,
`allowed_countries` varchar(50) DEFAULT NULL,
`other_names` text DEFAULT NULL,
`age_rest_id` tinyint(2) NOT NULL DEFAULT 0,
`chapters_count` tinyint(4) NOT NULL DEFAULT 0,
`telegraph` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Дамп данных таблицы `videos`
--
--
-- Индексы сохранённых таблиц
--
--
-- Индексы таблицы `videos`
--
ALTER TABLE `videos`
ADD PRIMARY KEY (`id`),
ADD KEY `id` (`id`),
ADD KEY `published` (`published`),
ADD KEY `duration` (`duration`),
ADD KEY `add_date` (`add_date`),
ADD KEY `views` (`views`),
ADD KEY `likes` (`likes`),
ADD KEY `ip` (`id`,`published`),
ADD KEY `partner_id` (`partner_id`),
ADD KEY `feed_id` (`feed_id`),
ADD KEY `external_key` (`external_key`),
ADD KEY `slug` (`slug`);
ALTER TABLE `videos` ADD FULLTEXT KEY `name` (`name`,`descr`);
--
-- AUTO_INCREMENT для сохранённых таблиц
--
--
-- AUTO_INCREMENT для таблицы `videos`
--
ALTER TABLE `videos`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=18168;
COMMIT;
/*!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 */;
выше таблица, сам конфиг:
source videos_t1
{
type = mysql
sql_host = localhost
sql_user = t1
sql_pass = t1
sql_db = t1
sql_port = 3306
sql_query_pre = SET NAMES utf8
sql_query = \
SELECT v.*, v.id AS video_id, UNIX_TIMESTAMP(v.add_date) AS add_date_ts, \
(SELECT REPLACE(GROUP_CONCAT(CONCAT_WS(',', tags.name, tags.synonyms)), '|', '') FROM tags \
JOIN videos_tags ON (videos_tags.tag_id = tags.id) \
WHERE videos_tags.tag_id = tags.id AND videos_tags.video_id=v.id) AS `tags`, \
tag_ids AS tag_ids_array, \
p.id AS p_partner_id, p.name AS partner_name, p.site_url AS partner_site_url, \
p.method_id AS partner_method_id, p.preview_type AS partner_preview_type \
FROM videos AS v \
JOIN partners AS p ON p.id = v.partner_id \
WHERE v.published = 1
sql_field_string = tags
sql_field_string = tag_ids
sql_attr_multi = uint tag_ids_array from field
sql_attr_uint = partner_method_id
sql_attr_uint = partner_preview_type
sql_attr_string = partner_name
sql_attr_string = partner_site_url
sql_attr_uint = partner_id
sql_attr_uint = server_group_id
sql_attr_uint = type_id
sql_attr_uint = feed_id
sql_field_string = name
sql_field_string = descr
sql_attr_string = duration
sql_attr_uint = is_hd
sql_attr_string = location
sql_field_string = add_date
sql_field_string = add_date_ts
#sql_attr_uint = published
sql_attr_uint = is_review_need
sql_attr_string = player_data
sql_attr_string = videos_data
sql_attr_string = format_files
sql_attr_string = rotations
sql_attr_string = embed
sql_attr_string = related_ids
sql_attr_string = external_key
sql_attr_uint = views
sql_attr_uint = likes
sql_attr_uint = dislikes
sql_attr_uint = translated
sql_attr_uint = flag
sql_attr_uint = total_comments
}
index videos_index_t1
{
source = videos_t1
path = /var/lib/manticore/videos_index_t1
#docinfo = extern
morphology = stem_ru,stem_en
min_word_len = 1
min_infix_len = 1
index_exact_words = 1
charset_table = 0..9, A..Z->a..z, _, a..z, U+410..U+42F->U+430..U+44F, U+430..U+44F
}
searchd {
listen = 127.0.0.1:9312
listen = 127.0.0.1:9306:mysql
#listen = 127.0.0.1:9308:http
log = /var/log/manticore/searchd.log
query_log = /var/log/manticore/query.log
pid_file = /var/run/manticore/searchd.pid
query_log_min_msec = 300
access_plain_attrs = mlock
access_blob_attrs = mlock
access_doclists = mlock
access_hitlists = mlock
threads = 2
#max_threads_per_query = 1
pseudo_sharding = 0
telemetry = 0
net_workers = 2
}
запросы обычные, простой вывод по пагинации , и вывод записей с определенными тегами (tag_ids_array) с пагинацией
ну и поиск по name и descr с пагинацией