Order keyword(s) result relevancy

Question

I've created a query that grabs fairly short strings from the database based on keywords created by the user (credit to zhikharev for his answer):

$searchArray = explode(' ', $search);
$searchNumber = count($searchArray);

$query = "SELECT * FROM tags WHERE tags.tag LIKE CONCAT('%',?,'%')" . 
    str_repeat(" OR tags.tag LIKE CONCAT('%',?,'%')", $searchNumber - 1);

$stmt = $mysqli -> prepare($query);
$bind_names[] = str_repeat('s', $searchNumber);

for ($i = 0; $i < count($searchArray); $i++){
    $bind_name = 'bind'.$i; /*generate a name for variable bind1, bind2, bind3...*/
    $$bind_name = $searchArray[$i]; /*create a variable with this name and put value in it*/
    $bind_names[] = & $$bind_name; /*put a link to this variable in array*/
}

call_user_func_array(array($stmt, 'bind_param'), &$bind_names);
$stmt -> execute();

My problem is that I don't know how to sort this result by relevancy. Note that one tag in the database can contain multiple words.

Let's say that the user searches for "New York City". Then in the database I have:

    New York
    York
    New York City
    New Zealand
    Kansas City

I would like the result to look like this:

    New York City
    New York
    New Zealand
    York
    Kansas City

It should look for tags with most matches with the words in the user input and tags that have approximately the same word order as the order of the words in the search.

Have you considered [fulltext search](http://dev.mysql.com/doc/en/fulltext-search.html)? — eggyal, Jan 11 '13 at 21:48
@eggyal Yeah I tried but it doesn't seem to be working to search for part of words. For example, searching for 'ork' doesn't return 'York' — Oskar Persson, Jan 11 '13 at 22:10
No, that's true. It won't. In that case, you will have to calculate a relevance score yourself... — eggyal, Jan 11 '13 at 22:11
Tried it by couldn't figure out any useful solution. That's why I asked this question ;) — Oskar Persson, Jan 11 '13 at 22:23
@Arnaud Hmm, should look into that. Though, will that work good when the user can search with multiple words? — Oskar Persson, Jan 11 '13 at 22:38
I am at work so I don't have time to investigate right now, regex usually have OR operators so you can make it work with multiple words. An idea would be to calculate a match percentage from you result set (percentage of words in the tag that match the query). You could then make a ranking among the tags from the 'most matching' to the 'least matching'. — Arnaud, Jan 11 '13 at 22:40
I got it working with multiple words though I don't know how to make the sorting. — Oskar Persson, Jan 12 '13 at 12:41

score 0 · Answer 1 · answered Jan 20 '13 at 02:23

This is the perfect use-case for a standalone full-text search engine such as Sphinx. I recently began using Sphinx in a project and so far I'm very happy with it. It indexes very quickly and I saw 10X speed improvements over a direct MySQL query.

Enabling star mode will allow partial matching: http://sphinxsearch.com/docs/2.0.6/conf-enable-star.html

It also allows various methods for sorting: http://sphinxsearch.com/docs/2.0.6/sorting-modes.html

Some more links: http://sphinxsearch.com/docs/2.0.6/ http://php.net/manual/en/sphinx.examples.php

Order keyword(s) result relevancy

1 Answers1