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
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 )