Full text search for WordPress with MySQL

search.sql

SELECT
    ID,
    post_title,
    post_status,
    CONCAT('http://www.yourwebsite.com/wp-content/uploads/',
        thumbnail.meta_value) AS picture,
    MATCH (post_title) AGAINST ('term') AS title_score,
    MATCH (post_excerpt) AGAINST ('term') AS excerpt_score,
    MATCH (post_content) AGAINST ('term') AS content_score
FROM wp_posts
RIGHT JOIN
    (
        SELECT
            m.post_id,m.meta_id,t.meta_key,t.meta_value
        FROM wp_postmeta AS t
            LEFT JOIN wp_postmeta AS m
            ON m.meta_value = t.post_id
        WHERE t.meta_key = "_wp_attached_file"
            AND m.meta_key = "_thumbnail_id"
    ) AS thumbnail
    ON thumbnail.post_id = wp_posts.id
WHERE wp_posts.post_type = "post"
    AND wp_posts.post_status = %(post_status)s
    AND (
        wp_posts.post_title REGEXP('term')
        OR MATCH (wp_posts.post_excerpt) AGAINST('term')
        OR MATCH (wp_posts.post_content) AGAINST('term')
    )
ORDER BY
    title_score DESC,
    content_score DESC,
    excerpt_score DESC,
    wp_posts.post_date_gmt DESC
LIMIT 20
OFFSET 0