Question 262537 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL MATCH in column select or in where clause. Is there a difference?

What is the difference, if any, in these two MySQL statements? Is there a performance difference if I use MATCH in the WHERE clause, or is it unnecessary?

SELECT `column`, MATCH(`column`) AGAINST ("searchterm") AS `rank` 
FROM `table` 
ORDER BY `rank` DESC;

AND

SELECT `column`, MATCH(`column`) AGAINST ("searchterm") AS `rank` 
FROM `table` 
WHERE MATCH(`column`) AGAINST ('searchterm') 
ORDER BY `rank` DESC;
Tags:

2

Answers


  1. 0

    The first example runs a table-scan. This must examine every row in your table, even those that don’t match the pattern in your AGAINST() predicate.

    The second example examines only matching rows (I assume you have defined a fulltext index).

    Performance is mostly proportional to examined rows, so the second example should be much better.

    Or course there are edge cases, e.g. if "searchterm" occurs on virtually every row. But I’m assuming a typical case, where your search matches a small subset of rows.

    Login or Signup to reply.
  2. 0

    From a performance standpoint, there might be a slight difference, but it’s typically minimal, and the choice between the two depends on your specific use case:

    • If you want to retrieve search results with their relevance scores, the first statement is more suitable.
    • If you only want rows that match the search term and don’t need to retrieve the relevance scores, the second statement is more appropriate.

    Both statements will return the same search results, but the second one may be slightly more efficient in filtering out non-matching rows.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search