Блог

Требование

• Порядок результатов запроса должен быть таким: результат по заголовку> результат по содержанию> результат по тегу
• Каждый результат должен быть в последнем порядке.

Я хочу сделать это в одном запросе. Как сделать эффективный запрос?

Ниже приведены запросы, которые я сделал с помощью регулярных выражений и ПОЛНОГО ТЕКСТА.

РЕГУЛЯРНОЕ ВЫРАЖЕНИЕ

 (SELECT * FROM board
    WHERE
        title rlike 'first' AND title rlike 'second'
    ORDER BY board_id DESC LIMIT 1000)
UNION
(SELECT * FROM board
    WHERE
        content rlike 'first' AND content rlike 'second'
    ORDER BY board_id DESC LIMIT 1000)
UNION
(SELECT * FROM board
    WHERE
        tag rlike 'first' AND tag rlike 'second'
    ORDER BY board_id DESC LIMIT 1000)
LIMIT 1000;
 

ПОЛНОТЕКСТОВЫЙ

 (SELECT * FROM board
    WHERE
        match(title) AGAINST(' "first"  "second"' in boolean mode)
    ORDER BY board_id DESC LIMIT 1000)
UNION
(SELECT * FROM board
    WHERE
        match(content) AGAINST(' "first"  "second"' in boolean mode)
    ORDER BY board_id DESC LIMIT 1000)
UNION
(SELECT * FROM board
    WHERE
        match(tag) AGAINST(' "first"  "second"' in boolean mode)
    ORDER BY board_id DESC LIMIT 1000)
LIMIT 1000;
 

Как я сейчас, РЕГУЛЯРНОЕ ВЫРАЖЕНИЕ не использует индекс, но оно быстрее, чем полнотекстовое.
Я тоже не понимаю, почему это происходит.

СОЗДАТЬ ИНСТРУКЦИЮ

 CREATE TABLE `board` (
  `board_id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` bigint NOT NULL,
  `nickname` varchar(255) NOT NULL,
  `category` int NOT NULL,
  `title` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `likes` int NOT NULL DEFAULT '0',
  `hits` int NOT NULL DEFAULT '0',
  `tag` varchar(255) DEFAULT NULL,
  `create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modify_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`board_id`),
  KEY `popular` (`create_date`,`likes`),
  FULLTEXT KEY `fttitle` (`title`),
  FULLTEXT KEY `ftcontent` (`content`),
  FULLTEXT KEY `fttag` (`tag`)
) ENGINE=InnoDB AUTO_INCREMENT=60027 DEFAULT CHARSET=utf8;

 

Регулярное выражение и объединение не будут в каком-либо определенном порядке.

В большинстве формулировок было бы лучше сначала получить идентификаторы в объединениях, а затем ОБЪЕДИНИТЬ, чтобы получить остальные столбцы из нескольких строк, которые получаются в результате.

Имейте в виду, что UNION это означает UNION DISTINCT и имеет дедуплицирующий проход. (Вероятно, это то, что вы хотите, хотя это медленнее, чем UNION ALL .)

Большинство из ORDER BY показанных вами бесполезны — они ничего не сделают, кроме (возможно) пустой траты времени. ORDER BY и LIMIT должны быть сопряжены для каждого подзапроса. А затем снова после выполнения UNION . Связанная тема: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#or

ответ nnichols не дедуплицируется. Это можно исправить с помощью дополнительного (внешнего) выбора, который группирует ответы и выбирает MIN(col_sort) с GROUP BY id , прежде чем выполнять порядок.

Еще

 SELECT c.*
    FROM (
        SELECT id, MIN(col_sort) AS col_sort2
            FROM (
                SELECT id, 1 AS col_sort
                    FROM ... 
                    WHERE MATCH ...
                    LIMIT 100
                UNION ALL
                SELECT id, 2 AS col_sort
                    FROM ... 
                    WHERE MATCH ...
                    LIMIT 100
                UNION ALL
                SELECT id, 3 AS col_sort
                    FROM ... 
                    WHERE MATCH ...
                    LIMIT 100
                 ) AS a
            GROUP BY id 
         ) AS b
    JOIN board AS c  USING(id)
    ORDER BY b.col_sort2
    LIMIT 100;
 

Не используйте огромное число, например 1000, для ОГРАНИЧЕНИЯ. Довольно сложно поддерживать «релевантность» с помощью запросов.

Ваш существующий ПОЛНОТЕКСТОВЫЙ запрос предлагает отдельные полнотекстовые индексы для трех текстовых столбцов. Исходя из этого, я бы, вероятно, начал с —

 (
    SELECT *, 1 AS col_sort
    FROM board
    WHERE match(title) AGAINST(' "first"  "second"' in boolean mode)
) UNION (
    SELECT *, 2 AS col_sort
    FROM board
    WHERE match(content) AGAINST(' "first"  "second"' in boolean mode)
) UNION (
    SELECT *, 3 AS col_sort
    FROM board
    WHERE match(tag) AGAINST(' "first"  "second"' in boolean mode)
)
ORDER BY col_sort ASC;
 

Разница в производительности между использованием RLIKE и ПОЛНОТЕКСТОВЫМ поиском будет зависеть от размера набора данных, как размеров столбцов, так и количества строк.