Server: MySQL 5.1.66-0+squeeze1

My problem:

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:

  1. So I though it might be the table definition. I truncated the prod table and than inserted the data from test (INSERT INTO test SELECT * FROM prod). The same problem.
  2. I repaired the table (even with USE_FRM), same problem
  3. 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?


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

FROM    table
WHERE   MATCH(col1, col2, col3, col4, col5, col6) AGAINST ("word1, word2") > 1

But is this really the right solution for the problem?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.