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.