how to order mysql results by relevancy?

AL-Kateb

Hello everybody
if i have topic_subject field and topic_body field in the table and i want to make a query if the subject or the topic contains specific keyword

now what i want is to put results in specific order

like the results when the match is in the topic_subject comes first then the results when the match is in the topic_body all in one query

i could do it by making two queries but then i would have problem with pagination!

there is a way to solve this .. like to create a table for searches and store found results IDs in that table along with the search ID and this will solve the pagination problem! but for some reason regarding SEO issues i cant do it this way

so is there another way to do this?
thanks in advance

Ashley_Sheridan

You could use a JOIN to join together the results of a search in first the subject and then the body. The subject and body searches will create results in a temporary table, and then your JOIN will concatenate the results of each.

AL-Kateb

You could use a JOIN to join together the results of a search in first the subject and then the body

i thought join is to join records from different tables and outputs them in the same table?

or maybe u meant to say (union)?

i could do this by using union to merge results from the first query which searches in the subject with results from query which searches in the body and that's all i need

thanks a lot for your help

johanafm

You can match both title and body in one query if that's what you're after

SELECT title, body, MATCH(title) AGAINST('...') as tmatch, MATCH(body) AGAINST('...') as bmatch
FROM tbl
HAVING tmatch OR bmatch
ORDER BY tmatch DESC, bmatch DESC

Since no match gets the value 0, you include anything where either tmatch or bmatch is not 0. Then you order first by title match relevancy and then by body. I.e. no matter how good the body match is if the title doesn't match, it will still come after a lousy title match where the body doesn't match at all.

To cache the results, you can't use a temporary table, since it only exists for the connection creating it, and as soon as the connection goes, so does the table. You could however create a view or table containing the above together with user_id, index on user_id. When a user performs a new search, you remove existing rows for that user_id.

djjjozsi

You can find union examples in the mysql.com manual site, like:

SELECT * FROM(
SELECT 2 AS ord , a, b FROM `t` WHERE `a` LIKE '%asd%' 
UNION
SELECT 1 AS ord,a, b FROM `t` WHERE `b` LIKE '%asd%'
        ) AS ttt
GROUP BY ttt.a,ttt.b

AL-Kateb

thanks for the examples guys i tried to use UNION and it works just as i wanted

thanks again for your help
good luck

djjjozsi

Then you should read more about johanafm's suggestion, about the fulltext search.