MySQL/MariaDB Optimizing a Two Table Full Text Search

Question

I'm trying to figure out how to optimize a full text search query which seems rather slow against two tables with around 2k entries, each. I'm running MariaDB 10.3.x. The two table query is orders of magnitude slower than one performed against a single table (e.g. 0.255 seconds vs. 0.03 seconds).

The goal is to search for a term in the relevant uninet_articles fields (body, abstract, title, subtitle) and also see if there are any occurrences of the term in the uninet_tags table, joining it as appropriate. Both tables have full text indexes. Here's the query:

    SELECT a.approve
    ,a.aid
    ,a.sid
    ,a.articleFormat
    ,title
    ,cachedTitle
    ,subtitle
    ,body
    ,abstract
    ,a.linkUrl
    ,a.byline
    ,a.poster
    ,a.allowComments
    ,a.allowRatings
    ,a.gmt
    ,a.lastModified
    ,a.modifier
    ,a.type
    ,UNIX_TIMESTAMP(a.gmt) AS DATETIME
    ,a.commentCount
    ,a.ratingCount
    ,a.ratingDetails
    ,(MATCH(a.body, a.title, a.subtitle, a.abstract) AGAINST('OS X' IN NATURAL LANGUAGE MODE) + MATCH(tags.name) AGAINST('OS X' IN NATURAL LANGUAGE MODE)) AS relevanceScore
    ,a.readCount
FROM uninet_articles a
LEFT JOIN uninet_tags AS tags ON a.aid = tags.paid
    AND MATCH(tags.name) AGAINST('OS X' IN NATURAL LANGUAGE MODE)
WHERE MATCH(a.body, a.title, a.subtitle, a.abstract) AGAINST('OS X' IN NATURAL LANGUAGE MODE)
    OR tags.tid
    AND a.type = 'article'
    AND `approve` != '0'
ORDER BY `approve` DESC
    ,`gmt` DESC LIMIT 0
    ,10

I was able to cut the query time by 25% when I upgraded to the current version of MariaDB. I've tried MyISAM and InnoDB -- InnoDB seems to perform about 50% worse than MyISAM. Aria seems to be ever slightly faster than MyISAM, but not significantly so.

This leads me to two questions: first, is there a way to optimize the query to make it faster so that when I scale up the data in the table it is semi-acceptable? Second, is there a way to optimize the query for InnoDB, so I can go ahead and make the leap to a safer database system?

Answer 1

Multiple FT tests

MATCH likes to be first. But another MATCH cannot also be "first".

OR also hurts performance terribly.

So, let's separate them:

SELECT ...
    WHERE MATCH(tags.name) AGAINST('OS X' IN NATURAL LANGUAGE MODE)
UNION DISTINCT
SELECT ...
    WHERE MATCH(a. ...) AGAINST('OS X' IN NATURAL LANGUAGE MODE)
UNION DISTINCT
SELECT ...
    WHERE tags.tid

Then use that as a 'derived' table to do the rest of the work:

SELECT ...
    FROM ( the-above-union ) AS u
    ....

Definition of "word"

Also, the space will not work inside a "word" in a FULLTEXT search.

Other issues

Hmmm... Is tags.tid a boolean? Perhaps you need to test that against something?