how to order mysql results by relevancy?

[ALKateb]

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

[coopster]

what about using something like sphinx search to index and weigh the results and then use the API in your scripting language to display and paginate? It will likely do a better job of indexing by relevancy too.

[ALKateb]

you are right i considered using search engine but that is gonna take a while to implement and what i needed in this website is much simpler

btw i figured out that 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