mmm8955405
commented
3 years ago SELECT discussions., posts_ft.most_relevant_post_id
FROM discussions
INNER JOIN ((
SELECT posts.discussion_id, SUM(MATCH(posts.content) AGAINST ()) AS score, SUBSTRING_INDEX(GROUP_CONCAT(posts.id
ORDER BY MATCH(posts.content) AGAINST () DESC, posts.number), ',', 1) AS most_relevant_post_id
FROM posts
WHERE EXISTS (
SELECT 1
FROM discussions
WHERE discussions.id = posts.discussion_id AND (discussions.id NOT in (
SELECT discussion_id
FROM discussion_tag
WHERE tag_id NOT in (
SELECT tags.id
FROM tags
WHERE (tags.id in (
SELECT perm_tags.id
FROM tags AS perm_tags
WHERE (perm_tags.is_restricted = 1 AND 0 = 1) OR perm_tags.is_restricted = 0) AND (tags.parent_id in (
SELECT perm_tags.id
FROM tags AS perm_tags
WHERE (perm_tags.is_restricted = 1 AND 0 = 1) OR perm_tags.is_restricted = 0) OR tags.parent_id IS NULL))))) AND (discussions.is_private = 0 OR (((discussions.is_approved = 0 AND (discussions.user_id = 0 OR ((discussions.id NOT in (
SELECT discussion_id
FROM discussion_tag
WHERE tag_id NOT in (
SELECT tags.id
FROM tags
WHERE (tags.id in (
SELECT perm_tags.id
FROM tags AS perm_tags
WHERE (perm_tags.is_restricted = 1 AND 0 = 1)) AND (tags.parent_id in (
SELECT perm_tags.id
FROM tags AS perm_tags
WHERE (perm_tags.is_restricted = 1 AND 0 = 1)) OR tags.parent_id IS NULL))))) AND EXISTS (
SELECT
FROM tags
INNER JOIN discussion_tag ON tags.id = discussion_tag.tag_id
WHERE discussions.id = discussion_tag.discussion_id))))))) AND (discussions.hidden_at IS NULL OR discussions.user_id = 0 OR ((discussions.id NOT in (
SELECT discussion_id
FROM discussion_tag
WHERE tag_id NOT in (
SELECT tags.id
FROM tags
WHERE (tags.id in (
SELECT perm_tags.id
FROM tags AS perm_tags
WHERE (perm_tags.is_restricted = 1 AND 0 = 1)) AND (tags.parent_id in (
SELECT perm_tags.id
FROM tags AS perm_tags
WHERE (perm_tags.is_restricted = 1 AND 0 = 1)) OR tags.parent_id IS NULL))))) AND EXISTS (
SELECT
FROM tags
INNER JOIN discussion_tag ON tags.id = discussion_tag.tag_id
WHERE discussions.id = discussion_tag.discussion_id))) AND (discussions.comment_count > 0 OR discussions.user_id = 0 OR ((discussions.id NOT in (
SELECT discussion_id
FROM discussion_tag
WHERE tag_id NOT in (
SELECT tags.id
FROM tags
WHERE (tags.id in (
SELECT perm_tags.id
FROM tags AS perm_tags
WHERE (perm_tags.is_restricted = 1 AND 0 = 1)) AND (tags.parent_id in (
SELECT perm_tags.id
FROM tags AS perm_tags
WHERE (perm_tags.is_restricted = 1 AND 0 = 1)) OR tags.parent_id IS NULL))))) AND EXISTS (
SELECT
FROM tags
INNER JOIN discussion_tag ON tags.id = discussion_tag.tag_id
WHERE discussions.id = discussion_tag.discussion_id)))) AND (posts.is_private = 0 OR (((posts.is_approved = 0 AND (posts.user_id = 0 OR EXISTS (
SELECT 1
FROM discussions
WHERE discussions.id = posts.discussion_id AND ((discussions.id NOT in (
SELECT discussion_id
FROM discussion_tag
WHERE tag_id NOT in (
SELECT tags.id
FROM tags
WHERE (tags.id in (
SELECT perm_tags.id
FROM tags AS perm_tags
WHERE (perm_tags.is_restricted = 1 AND 0 = 1)) AND (tags.parent_id in (
SELECT perm_tags.id
FROM tags AS perm_tags
WHERE (perm_tags.is_restricted = 1 AND 0 = 1)) OR tags.parent_id IS NULL))))) AND EXISTS (
SELECT
FROM tags
INNER JOIN discussion_tag ON tags.id = discussion_tag.tag_id
WHERE discussions.id = discussion_tag.discussion_id)))))))) AND (posts.hidden_at IS NULL OR posts.user_id = 0 OR EXISTS (
SELECT 1
FROM discussions
WHERE discussions.id = posts.discussion_id AND (1=0 OR ((discussions.id NOT in (
SELECT discussion_id
FROM discussion_tag
WHERE tag_id NOT in (
SELECT tags.id
FROM tags
WHERE (tags.id in (
SELECT perm_tags.id
FROM tags AS perm_tags
WHERE (perm_tags.is_restricted = 1 AND 0 = 1)) AND (tags.parent_id in (
SELECT perm_tags.id
FROM tags AS perm_tags
WHERE (perm_tags.is_restricted = 1 AND 0 = 1)) OR tags.parent_id IS NULL))))) AND EXISTS (
SELECT
FROM tags
INNER JOIN discussion_tag ON tags.id = discussion_tag.tag_id
WHERE discussions.id = discussion_tag.discussion_id))))) AND posts.type = COMMENT AND MATCH(posts.content) AGAINST (IN BOOLEAN MODE) AND type in (discussionLocked, discussionStickied, discussionTagged, COMMENT, discussionRenamed)
GROUP BY posts.discussion_id) UNION (
SELECT id, NULL AS score, first_post_id AS most_relevant_post_id
FROM discussions
WHERE MATCH(discussions.title) AGAINST (IN BOOLEAN MODE))) posts_ft ON posts_ft.discussion_id = discussions.id
WHERE (discussions.id NOT in (
SELECT discussion_id
FROM discussion_tag
WHERE tag_id NOT in (
SELECT tags.id
FROM tags
WHERE (tags.id in (
SELECT perm_tags.id
FROM tags AS perm_tags
WHERE (perm_tags.is_restricted = 1 AND 0 = 1) OR perm_tags.is_restricted = 0) AND (tags.parent_id in (
SELECT perm_tags.id
FROM tags AS perm_tags
WHERE (perm_tags.is_restricted = 1 AND 0 = 1) OR perm_tags.is_restricted = 0) OR tags.parent_id IS NULL))))) AND (discussions.is_private = 0 OR (((discussions.is_approved = 0 AND (discussions.user_id = 0 OR ((discussions.id NOT in (
SELECT discussion_id
FROM discussion_tag
WHERE tag_id NOT in (
SELECT tags.id
FROM tags
WHERE (tags.id in (
SELECT perm_tags.id
FROM tags AS perm_tags
WHERE (perm_tags.is_restricted = 1 AND 0 = 1)) AND (tags.parent_id in (
SELECT perm_tags.id
FROM tags AS perm_tags
WHERE (perm_tags.is_restricted = 1 AND 0 = 1)) OR tags.parent_id IS NULL))))) AND EXISTS (
SELECT
FROM tags
INNER JOIN discussion_tag ON tags.id = discussion_tag.tag_id
WHERE discussions.id = discussion_tag.discussion_id))))))) AND (discussions.hidden_at IS NULL OR discussions.user_id = 0 OR ((discussions.id NOT in (
SELECT discussion_id
FROM discussion_tag
WHERE tag_id NOT in (
SELECT tags.id
FROM tags
WHERE (tags.id in (
SELECT perm_tags.id
FROM tags AS perm_tags
WHERE (perm_tags.is_restricted = 1 AND 0 = 1)) AND (tags.parent_id in (
SELECT perm_tags.id
FROM tags AS perm_tags
WHERE (perm_tags.is_restricted = 1 AND 0 = 1)) OR tags.parent_id IS NULL))))) AND EXISTS (
SELECT
FROM tags
INNER JOIN discussion_tag ON tags.id = discussion_tag.tag_id
WHERE discussions.id = discussion_tag.discussion_id))) AND (discussions.comment_count > 0 OR discussions.user_id = 0 OR ((discussions.id NOT in (
SELECT discussion_id
FROM discussion_tag
WHERE tag_id NOT in (
SELECT tags.id
FROM tags
WHERE (tags.id in (
SELECT perm_tags.id
FROM tags AS perm_tags
WHERE (perm_tags.is_restricted = 1 AND 0 = 1)) AND (tags.parent_id in (
SELECT perm_tags.id
FROM tags AS perm_tags
WHERE (perm_tags.is_restricted = 1 AND 0 = 1)) OR tags.parent_id IS NULL))))) AND EXISTS (
SELECT *
FROM tags
INNER JOIN discussion_tag ON tags.id = discussion_tag.tag_id
WHERE discussions.id = discussion_tag.discussion_id)))
GROUP BY discussions.id
ORDER BY MATCH(discussions.title) AGAINST () DESC, posts_ft.score DESC
LIMIT 21
luceos
How long has your team been doing, but it still doesn't support full-text search in other languages! Think about a forum that does not support searching posts in other languages. Who will use it. Sometimes it's even worse than like, but that's what you should consider~~~
You use MySQL "NGram", that painful participle! In my opinion, apart from being friendly to English, NGram is nothing but chicken ribs. So I try to use mroonga. Mroonga's packaging mode supports InnoDB. Everything seems to be going well; But when I enter a special character in the search box, (like@# ￥%... & *), MySQL full text parsing error!!!! I tried different versions of mroonga and different grammatical word segmentation, and the results were the same. I don't have time to look at your PHP code, but I think it's the reason for your code error.
Your table design uses foreign keys in many places. That kind of old thing has no help ~ constraints except to increase the difficulty of readability and operation. It should be done at the business level. In addition, your SQL statement has multiple table links. At the data million level, MySQL will have a painful performance bottleneck when more than three tables are linked
At your level, I don't know when to finish full-text search!! After all, this problem was a problem several years ago, but you still haven't solved it. Adding mroonga search plug-in is a way, but there is a bug with special characters! I believe that solving the problem of special characters in your code (it seems to me that it is a code problem, not a problem of MySQL database full-text search engine) is the fastest way to solve the search problem at present! Unfortunately, I specialize in Java, not PHP
