Fulltext search in posts sorted by relevancy

I’m building a custom search page and what I need is to sort results by relevancy (eg. occurences of a keyword)

Using MySQL docs I’ve built this query:

SELECT ID
FROM $wpdb->posts
WHERE MATCH (post_title, post_content) AGAINST ('$keyword_list')
AND post_status="publish"
AND post_type="issue"

Problem A) If I test it using a keyword(s) that appears multiple times in my test posts, it
does find those posts, but it’s not sorted correctly by relevancy. For example the first found posts has around 3 occurences of the keyword while the posts returned on 3rd place has roughly 15 occurences.

Problem B) There are two posts that contain keyword “medicare”, if I search for “medicare” it does return those posts, however when I search for “medi”, “medic” etc. it doesn’t return anything.

Is there a way to query it using MATCH/AGAINST or do I have to use LIKE % – in that case, how to sort by relevancy.

Thanks

You may want to check the Sphinx search engine
http://sphinxsearch.com/

It took me about one day to set it up and learn how to use, but it does really good job with sorting by relevancy and grammar stemming. Also it is faster than fulltext search for big data sets thanks to indexing.