Complication Name Search

Good Day Coders,

I’m hoping someone can help me.

I’m searching a very large database based on my search string. Here’s my code below. John Jackson comes from a text box and this code has been pulled from previous costs on this forum.

“`
SELECT *, MATCH (`lastname`,`firstname`,`middlenames`,`knownas`,`nickname`) AGAINST (‘John Jackson’ IN NATURAL LANGUAGE MODE) As Score FROM `staff`
WHERE
HAVING Score > 0
ORDER BY Score DESC
“`

I get a score – does anyone know what this score is made from, like how does it get calculated? a lost of scores are in single digits so it’s not a percentage I don’t believe.

I get hundreds of results from my thousands of records. It includes any John or any Jackson even if their other names are unrelated.

I really want to only get strong / close matches.

I can’t just search first and last names as some people go by nickname, of John is a ”know as’ name because he real name is Johnathon.

Other people are known only by their nickname like a staff member known as TinTin, a lot of staff don’t even know his real name.

How can I improve this code?

I had thought about joining names into runtime columns and matching against this?

`
CONCAT_WS(‘ ‘, `lastname`, `firstname`)
.`

But I’m not sure how efficient that would be as I’d need a large number of combinations and I’m not sure if I can even match there on a WHERE clause??

Also in #PHP _↴