CODEWITHSUNDEEP
mysqlsqlfull-text-searchquery-optimization
Shaharyar
Shaharyar

Reputation: 12449

Full text search sort by relevancy score

Table: users with full text search index on column username.

Query:

SELECT 
    MATCH (username) AGAINST ('shaharyar' IN NATURAL LANGUAGE MODE) AS `score`, 
    uid, 
    first_name, 
    username, 
    `status`, 
    created
FROM users
WHERE 
    MATCH (username) AGAINST ('shaharyar' IN NATURAL LANGUAGE MODE) AND 
    uid <> 164125 -- to prevent self profile search
ORDER BY 
    score DESC, 
    created DESC

Question: Will the Match clause execute 2 times?

I know MySQL sorts by score descending by default, but here I need double sorting condition that's why I am selecting the column.

Explain Extended:

+----+-------------+-------+----------+------------------+----------+---------+-----+------+----------+-----------------------------+
| id | select_type | table |   type   |  possible_keys   |   key    | key_len | ref | rows | filtered |            Extra            |
+----+-------------+-------+----------+------------------+----------+---------+-----+------+----------+-----------------------------+
|  1 | SIMPLE      | users | fulltext | PRIMARY,username | username |       0 |     |    1 |      100 | Using where; Using filesort |
+----+-------------+-------+----------+------------------+----------+---------+-----+------+----------+-----------------------------+

About 150K+ records are in the table.

Upvotes: 1

Views: 2238

Answers (2)

Rick James
Rick James

Reputation: 142366

Yes and no.

WHERE MATCH... will be executed some number of times, thereby "filtering" out the rows that don't "match". Only the remaining rows would need to execute the MATCH a second time.

HAVING, on the other hand, would have to perform the MATCH for all the rows (except for the one with that uid), build a temp table with all those rows. Only then could the HAVING do the filtering.

So, even if MATCH is executed fewer times in the HAVING version, the query would probably run faster.

You do have FULLTEXT(username), don't you?

Why, why, use a fulltext query against one word??

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270443

I think so. This is often written using a having clause:

SELECT MATCH (username) AGAINST ('shaharyar' IN NATURAL LANGUAGE MODE) AS `score`, 
       uid, first_name, username, `status`, created
FROM users
WHERE uid <> 164125 -- to prevent self profile search
HAVING score > 0
ORDER BY score DESC, created DESC;

Upvotes: 1

Related Questions