php - How-to: Ranking Search Results

Question

I have a webapp development problem that I've developed one solution for, but am trying to find other ideas that might get around some performance issues I'm seeing.

problem statement:

• a user enters several keywords/tokens
• the application searches for matches to the tokens
• need one result for each token
  • ie, if an entry has 3 tokens, i need the entry id 3 times
• rank the results
  • assign X points for token match
  • sort the entry ids based on points
  • if point values are the same, use date to sort results

What I want to be able to do, but have not figured out, is to send 1 query that returns something akin to the results of an in(), but returns a duplicate entry id for each token matches for each entry id checked.

Is there a better way to do this than what I'm doing, of using multiple, individual queries running one query per token? If so, what's the easiest way to implement those?

edit
I've already tokenized the entries, so, for example, "see spot run" has an entry id of 1, and three tokens, 'see', 'spot', 'run', and those are in a separate token table, with entry ids relevant to them so the table might look like this:

'see', 1 
'spot', 1 
'run', 1 
'run', 2 
'spot', 3 

Answer 1

you could achive this in one query using 'UNION ALL' in MySQL.

Just loop through the tokens in PHP creating a UNION ALL for each token:

e.g if the tokens are 'x', 'y' and 'z' your query may look something like this

SELECT * FROM `entries` 
WHERE token like "%x%" union all 
    SELECT * FROM `entries` 
    WHERE token like "%y%" union all 
        SELECT * FROM `entries` 
        WHERE token like "%z%" ORDER BY score ect...

The order clause should operate on the entire result set as one, which is what you need.

In terms of performance it won't be all that fast (I'm guessing), however with databases the main overhead in terms of speed is often sending the query to the database engine from PHP and receiving the results. With this technique this only happens once instead of once per token, so performance will increase, I just don't know if it'll be enough.