У меня есть таблица из примерно 5 миллионов строк данных (статей). У меня есть следующий запрос для полнотекстового поиска в заголовке статей на двух разных языках. Проблема заключается в том, что для выполнения требуется около 15 секунд. MySQL version: 5.6.29-log
Вот запрос:
SELECT `id`, `title`, `title_fa` FROM (SELECT `p`.`id` AS `id`, `p`.`title` AS `title`, `p`.`title_fa` AS `title_fa`, `p`.`unique` AS `unique`, `p`.`date` AS `date` FROM `articles` `p` LEFT JOIN `authors` `a` ON `p`.`unique` = `a`.`unique` WHERE 1 AND MATCH (`p`.`title`) AGAINST ('"heat"' IN BOOLEAN MODE) UNION SELECT `p`.`id` AS `id`, `p`.`title` AS `title`, `p`.`title_fa` AS `title_fa`, `p`.`unique` AS `unique`, `p`.`date` AS `date` FROM `articles` `p` LEFT JOIN `authors` `a` ON `p`.`unique` = `a`.`unique` WHERE 1 AND MATCH (`p`.`title_fa`) AGAINST ('"گرما"' IN BOOLEAN MODE)) AS `subQuery` GROUP BY `unique` ORDER BY `date` DESC LIMIT 0,10;
Это структура таблицы:
CREATE TABLE `articles` ( `id` int(10) unsigned NOT NULL, `title` text COLLATE utf8_persian_ci NOT NULL, `title_fa` text COLLATE utf8_persian_ci NOT NULL, `description` text COLLATE utf8_persian_ci NOT NULL, `description_fa` text COLLATE utf8_persian_ci NOT NULL, `date` date NOT NULL, `unique` tinytext COLLATE utf8_persian_ci NOT NULL, ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci; ALTER TABLE `articles` ADD PRIMARY KEY (`id`), ADD KEY `unique` (`unique`(128)), ADD FULLTEXT KEY `TtlDesc` (`title`,`description`); ADD FULLTEXT KEY `Title` (`title`); ADD FULLTEXT KEY `faTtlDesc` (`title_fa`,`description_fa`); ADD FULLTEXT KEY `faTitle` (`title_fa`); MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT;
Первый шаг улучшения:
По поиску так я наткнулся на этот пост:
Объединение операций UNION и LIMIT в запросе MySQL
Используя предложенный метод, я изменил свой запрос следующим образом:
SELECT `id`, `title`, `title_fa` FROM (SELECT `p`.`id` AS `id`, `p`.`title` AS `title`, `p`.`title_fa` AS `title_fa`, `p`.`date` AS `date`, `p`.`unique` AS `unique` FROM `articles` `p` LEFT JOIN `authors` `a` ON `p`.`unique` = `a`.`unique` WHERE MATCH (`p`.`title`) AGAINST ('"heat"' IN BOOLEAN MODE) LIMIT 0,100 UNION SELECT `p`.`id` AS `id`, `p`.`title` AS `title`, `p`.`title_fa` AS `title_fa`, `p`.`date` AS `date`, `p`.`unique` AS `unique` FROM `articles` `p` LEFT JOIN `authors` `a` ON `p`.`unique` = `a`.`unique` WHERE MATCH (`p`.`title_fa`) AGAINST ('"گرما"' IN BOOLEAN MODE) LIMIT 0,100) AS `subQuery` GROUP BY `unique` ORDER BY `date` DESC LIMIT 0,10
Производительность была удивительной, и запрос занял около 0,04 секунды. Проблема заключалась в сортировке, которая мне нравилась, когда были опубликованы более поздние статьи, но этот запрос не может этого сделать. Также я не знаю, как можно получить и показать следующий набор результатов (т. Е. Следующие 10 результатов – вторая страница результатов).
Второй шаг улучшения:
Ищите больше на SO Я столкнулся с этим:
SQL Query – использование порядка в UNION
И мой запрос выглядел следующим образом:
SELECT `id`, `title`, `title_fa`, `unique`, `date` FROM (SELECT `p`.`id` AS `id`, `p`.`title` AS `title`, `p`.`title_fa` AS `title_fa`, `p`.`date` AS `date`, `p`.`unique` AS `unique` FROM `articles` `p` LEFT JOIN `authors` `a` ON `p`.`unique` = `a`.`unique` WHERE MATCH (`p`.`title`) AGAINST ('"heat"' IN BOOLEAN MODE) ORDER BY `p`.`date` DESC LIMIT 0,20) AS `subQueryE` UNION ALL SELECT `id`, `title`, `title_fa`, `unique`, `date` FROM (SELECT `f`.`id` AS `id`, `f`.`title` AS `title`, `f`.`title_fa` AS `title_fa`, `f`.`date` AS `date`, `f`.`unique` AS `unique` FROM `articles` `f` LEFT JOIN `authors` `a` ON `f`.`unique` = `a`.`unique` WHERE MATCH (`f`.`title_fa`) AGAINST ('"گرما"' IN BOOLEAN MODE) ORDER BY `f`.`date` DESC LIMIT 0,20) AS `subQueryF` GROUP BY `unique` ORDER BY `date` DESC LIMIT 0,10
Производительность была лучше, но не удовлетворительной, так как потребовалось около 7 секунд. Это вызвало еще одну проблему: даже если в результатах по-прежнему присутствовали GROUP BY unique повторяющиеся строки GROUP BY unique .
Третий шаг:
Я сделал еще один тест, выполнив следующий запрос, надеясь получить лучшие результаты:
SELECT `p`.`id` AS `id`, `p`.`title` AS `title`, `p`.`title_fa` AS `title_fa`, `p`.`date` AS `date`, `p`.`unique` AS `unique` FROM `articles` `p` LEFT JOIN `authors` `a` ON `p`.`unique` = `a`.`unique` WHERE MATCH (`p`.`title`) AGAINST ('"heat"' IN BOOLEAN MODE) OR MATCH (`p`.`title_fa`) AGAINST ('"گرما"' IN BOOLEAN MODE) GROUP BY `unique` ORDER BY `date` DESC LIMIT 0,10
Но время исполнения было ужасным и достигло более 100 секунд.
Любая помощь более чем приветствуется и благодарит заранее.