Sort by Most Found MY_SQL Full Text
I am working on a web application and need help requesting. I am using the full text, this is how it looks:
SELECT id, name, city, state, country, date
FROM events_search
WHERE MATCH(name, city, state, country)
AGAINST ('$str')
As you can see, I am using an Index that contains the name, city, state, and country and maps it to the string the user belongs to. This string can contain any combination of the fields I just mentioned. So I need to query the db, find the most relevant results and then show them to the user. It works well so far, but not perfect.
For example: if the user logs into Miami Florida, the first 5 results are in Miami and other Florida cities. The next 5 are in Miami Arizona (I don't want to show this one) and after I get more results in Florida and Miami (FL). So I want to define a field that has been found more times and show the results that have that field on top. In this case, it will be Florida, as 70% of the results are in Florida in the state field.
source to share
Include full-text search in your sentence SELECT
and you get the estimate as MySQL thinks.
SELECT
id,
name,
city,
state,
country,
date,
MATCH(name, city, state, country) AGAINST ('$str') AS relevancy
FROM
events_search
WHERE
MATCH(name, city, state, country) AGAINST ('$str')
ORDER BY relevancy DESC
This does not make the query slower; the optimizer checks that it is twice the same search criteria.
With the proposal, ORDER BY
your Miami Arizona should appear later than Miami Florida.
source to share