Can't find FULLTEXT index while using IN BOOLEAN MODE

Question

I'm trying to filter some data with an MATCH ... AGAINST WHERE clause. But I get the following error:

Can't find FULLTEXT index matching the column list

This is an query I execute:

SELECT PAR.idPartij,
       PAR.Partijnaam,
       PRO.Productnaam,
       PER.Perceel,
       R.Ras,
       PAR.TagNR
FROM Partij AS PAR
LEFT OUTER JOIN Product AS PRO ON PAR.idProduct = PRO.idProduct
LEFT OUTER JOIN Ras AS R ON PAR.idRas = R.idRas
LEFT OUTER JOIN Klasse AS K ON PAR.idKlasse = K.idKlasse
LEFT OUTER JOIN Perceel AS PER ON PAR.idPerceel = PER.idPerceel
LEFT OUTER JOIN Maat AS M ON PAR.idMaat = M.idMaat
LEFT OUTER JOIN Teler AS T ON PAR.idTeler = T.idTeler
LEFT OUTER JOIN Handelshuis AS H ON PAR.idHandelshuis = H.idHandelshuis
LEFT OUTER JOIN Behandeling AS BH ON PAR.idBehandeling = BH.idBehandeling
WHERE PAR.Actief <> 0
  AND (
          MATCH(PAR.idPartij) AGAINST ('%1423%' IN BOOLEAN MODE)
       OR MATCH(PAR.Partijnaam) AGAINST ('%1423%' IN BOOLEAN MODE)
       OR MATCH(PRO.Productnaam) AGAINST ('%1423%' IN BOOLEAN MODE)
       OR MATCH(PER.Perceel) AGAINST ('%1423%' IN BOOLEAN MODE)
       OR MATCH(R.Ras) AGAINST ('%1423%' IN BOOLEAN MODE)
       OR MATCH(PAR.TagNR) AGAINST ('%1423%' IN BOOLEAN MODE)
  )
ORDER BY PAR.idPartij

As you can see, am I using the IN BOOLEAN MODE inside the AGAINST to overcome this problem as stated by Dan Grossman but it isn't working.

Am I missing something or am I doing it totally wrong?

an little side note, the select (and thus the MATCH part) is created dynamically in PHP

Answer 1

There could be many several factors why in boolean mode not working

• like mysql version you are having,

• storage engine etc.

However its highly recommended to use fulltext index when you are doing match .. against since this is what it is meant for.

Now coming to the point will it mess up other index, no it will not your id is primary key and its always index. You can use regular indexes for other columns as well.