Mysql query to search posts with title, content and tag-CodePudding

Requirement

The query result's order should be like this: result by title > result by content > result by tag
Each result should be in the latest order.

I want to make it in one query. How to make an efficient query?

The followings are querys that i made with REGEXP and FULLTEXT.

REGEXP

SELECT t.*
        FROM
            (SELECT * FROM
                ( SELECT * FROM moc_db_test.board
                WHERE
                    title rlike 'first' AND title rlike 'second'
                ) AS t2
            GROUP BY board_id ORDER BY t2.board_id DESC) AS t
        UNION
        SELECT c.*
        FROM
            (SELECT * FROM
                ( SELECT * FROM moc_db_test.board
                WHERE
                    content rlike 'first' AND content rlike 'second'
                ) AS c2
            GROUP BY board_id ORDER BY c2.board_id DESC) AS c
        UNION
        SELECT k.*
        FROM
            (SELECT * FROM
                ( SELECT * FROM moc_db_test.board
                WHERE
                    tag rlike 'first' AND tag rlike 'second'
                ) AS k2
            GROUP BY board_id ORDER BY k2.board_id DESC) AS k LIMIT 1000;

FULLTEXT

SELECT t.*
    FROM
        (SELECT * FROM
            ( SELECT * FROM moc_db_test.board
                WHERE
                    match(title) AGAINST(' "first"  "second"' in boolean mode)
            ) AS t2
        GROUP BY board_id ORDER BY t2.board_id DESC) AS t
UNION
SELECT c.*
    FROM
        (SELECT * FROM
            ( SELECT * FROM moc_db_test.board
                WHERE
                    match(content) AGAINST(' "first"  "second"' in boolean mode)
            ) AS c2
        GROUP BY board_id ORDER BY c2.board_id DESC) AS c
UNION
SELECT k.*
    FROM
        (SELECT * FROM
            ( SELECT * FROM moc_db_test.board
                WHERE
                    match(tag) AGAINST(' "first"  "second"' in boolean mode)
            ) AS k2
        GROUP BY board_id ORDER BY k2.board_id DESC) AS k 
LIMIT 1000;

As i now, REGEXP doesn't use index but it is faster than fulltext. I don't understand why it happens too.

CodePudding user response：

Your existing FULLTEXT query suggests individual fulltext indices on the three text based columns. On this basis I would probably start with -

(
    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;

The performance difference between using RLIKE and FULLTEXT search will depend on the size of the dataset, both column sizes and number of rows.

CodePudding user response：

The regexp and Union won't be in any particular order.

title rlike 'first' AND title rlike 'second' can be sped up to title rlike 'first|second'

In most formulations, it would be better to fetch the ids in the unions first, then JOIN to get the rest of the columns from the few rows that result.

Be aware that UNION means UNION DISTINCT and has a dedupping pass. (This is probably what you want, even though it is slower than UNION ALL.)

Most of the ORDER BY you have shown are useless -- they will do nothing, except (maybe) waste time. ORDER BY and LIMIT need to be paired up for each subquery. And then again after doing the UNION. A related topic: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#or

nnichols's answer fails to dedup. That can be remedied by an extra (outer) Select that Groups the answers and picks MIN(col_sort) with GROUP BY id before doing the order by.