Server: MySQL 5.1.66-0+squeeze1
I have two databases with the same tables. One is for the testing environment and one for production. When I perform a MATCH AGAINST on the prodution table (let's call it
prod), I get the correct result. When I do the same on the test table (let's call it
test), I get thousand of results, that shouldn't match.
Things get even worse. I copied the table from the production database to the test database. Still everything fine in the table.
My steps trying to resolve the problem:
- So I though it might be the table definition. I truncated the
prodtable and than inserted the data from
testSELECT * FROM
prod). The same problem.
- I repaired the table (even with USE_FRM), same problem
- I dropped all indexes and added them again, same problem
Now it is getting really strange:
I copied the data from both tables into one table (giving the new rows new auto incremented IDs), problem solved, but duplicate rows, as expected. I than removed the rows from
prod -> same problem!
So it must have something to do with the data. Trying to find the "broken row" I deleted 100 rows in each step, trying to find the one causing the issue. After hundreds of tries, I though, that I have found the one row. But after filling all data into the table again and deleting only this single row, the problem still exists.
So I really need you help as I have absolutely no clue what is going wrong and how I can solve the problem. At the moment it's only in the
test table, which wouldn't be that dramatic, but it might also occur in the
prod table in the future.
OK, I think the real problem is, that the MATCH will return everything with a score larger than 0.2 using a query like this:
SELECT MATCH(col1, col2, col3, col4, col5, col6) AGAINST ("word1, word2") AS score FROM table WHERE MATCH(col1, col2, col3, col4, col5, col6) AGAINST ("word1, word2")
In my table, the
word2 might match exactly the value of col6 and is included in col1. But the query also returns rows that does not have
word1 in any cols. Those rows have a score from approx. 0.2 to 0.6 and all rows with both words in any cols have a score from 8 and more.
So filtering this score for values greater than 1 does fix the problem. But how is the MATCH AGAINST defined? Why does it return rows with a score lower then 1?
prod I only get a score for three rows (which are the ones, you should expect for the search) of approx. 7.3 and higher, all other rows get a score 0. In
test approx. 40% of rows get a score larger than 0 but only the three positive rows get a score larger than 1.
So a possible solution would be using a query like the following (filtering scores lower than 1 out):
SELECT * FROM table WHERE MATCH(col1, col2, col3, col4, col5, col6) AGAINST ("word1, word2") > 1
But is this really the right solution for the problem?