Improve search query
Budget: €8 – €30 EUR
I have a query to get the best match of games but is not working as intended.
Its based on 2 values, the name and the popularity points (an already calculated value)
Name field has a FULLTEXT key
Added games.sql with those columns and indexes.
This is the query I am using at the moment.
@set string = ''
SELECT
name,
popularityPoints,
ROUND((MATCH(name) AGAINST('${string}' IN BOOLEAN MODE)* 50), 2) AS matchPoints,
(CASE WHEN popularityPoints > 0 THEN popularityPoints + ROUND((MATCH(name) AGAINST('${string}' IN BOOLEAN MODE)* 50), 2) ELSE 0 END) AS searchPoints
FROM
game AS g
WHERE
MATCH(name) AGAINST('${string}' IN BOOLEAN MODE)
ORDER BY
searchPoints DESC
LIMIT 100
Note: the number 50 is the number I gave to make it balanced with popularityPoints but can be changed
Requisites:
Optimal, no slow queries
Logical results
Some strings that should find correctly:
- input: "stalker 2" should get the game named "S.T.A.L.K.E.R. 2" first
- input: "final xv" should get the game named "Final fantasy XV" first
- input: "orcs 3" should get the game named "Orcs must die! 3" first
Its based on 2 values, the name and the popularity points (an already calculated value)
Name field has a FULLTEXT key
Added games.sql with those columns and indexes.
This is the query I am using at the moment.
@set string = ''
SELECT
name,
popularityPoints,
ROUND((MATCH(name) AGAINST('${string}' IN BOOLEAN MODE)* 50), 2) AS matchPoints,
(CASE WHEN popularityPoints > 0 THEN popularityPoints + ROUND((MATCH(name) AGAINST('${string}' IN BOOLEAN MODE)* 50), 2) ELSE 0 END) AS searchPoints
FROM
game AS g
WHERE
MATCH(name) AGAINST('${string}' IN BOOLEAN MODE)
ORDER BY
searchPoints DESC
LIMIT 100
Note: the number 50 is the number I gave to make it balanced with popularityPoints but can be changed
Requisites:
Optimal, no slow queries
Logical results
Some strings that should find correctly:
- input: "stalker 2" should get the game named "S.T.A.L.K.E.R. 2" first
- input: "final xv" should get the game named "Final fantasy XV" first
- input: "orcs 3" should get the game named "Orcs must die! 3" first