MySQL FULLTEXT BOOLEAN Query result differs between identical DBs

Question

I have two Windows MySQL 5.7 InnoDB databases. One is a production db, and the other is a test db that was created from an image of the prod db. Both have equal data loaded from an Oracle merge script. The issue I discovered is that a FULLTEXT BOOLEAN MODE query returns the wrong result for a single query on the prod db.

Consider these two address records: “1234 12TH ST” & “1234 12TH STREET”

If I execute my FULLTEXT query for 1234 12TH on the test db, both rows are returned, But with the same query on the prod db only the first row is returned.

I’ve run out of things to check between the prod and test db. What else can I check?

• Compared my.ini
• Compared variables
• Compared FULLTEXT indexes
• Checked stopwords
• Checked word length (I have it set to 1)
• ran set GLOBAL innodb_optimize_fulltext_only=ON;
• ran OPTIMIZE TABLE

(from Comment)

SELECT  searchtable.ADDRPART1
    FROM  SEARCHTABLE
    WHERE  SEARCHTABLE.ROLLTYPE IN ('REAL PROPERTY')
      AND  MATCH (SEARCHTABLE.ADDRPART1,SEARCHTABLE.ADDRPART2,
                SEARCHTABLE.ADDRPART3, SEARCHTABLE.ADDRPART4,
                SEARCHTABLE.ADDRPART5,
                SEARCHTABLE.ADDRPART6,SEARCHTABLE.ADDRPART7)
           AGAINST (+'+;1234+12TH' IN BOOLEAN MODE )

Answer 1

It's working now. I have to give credit to Gerard for letting me know about the 2000 word limit. When I ran OPTIMIZE TABLE the first time a few days ago, the process completed in 9 seconds and the issue unresolved. When I ran OPTIMIZE TABLE a second time today, the process ran for nearly 2 minutes and when done both records were returned. Thank you!

The innodb_ft_num_word_optimize option defines the number of words that are optimized each time OPTIMIZE TABLE is run. The default setting is 2000, which means that 2000 words are optimized each time OPTIMIZE TABLE is run. Subsequent OPTIMIZE TABLE operations continue from where the preceding OPTIMIZE TABLE operation ended.