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 )

• Gerard H. Pille: Perhaps you could share the query?
• Gerard H. Pille: One "optimize table" does 2000 words, the next the following 2000, ...
• Gregory Bologna: Here's the sql. I don't see any issue with it because it works for test db. 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)
• Rick James: MySQL version of both machines?
• Rick James: And please provide SHOW CREATE TABLE SEARCHTABLE.

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.

enter image description here

• Gerard H. Pille: To give credit where credit's due: it was in the manual. ;-)