Why full-text-search returns less rows than LIKE

I don't get full-text-search working as I want it to, and I don't understand the differences in the resultlists.

Example statements:

SELECT `meldungstext`
FROM `artikel`
WHERE `meldungstext` LIKE '%punkt%'

returns 92 rows. I receive rows which have matches, for example, like "Punkten", "Zwei-Punkte-Vorsprung" and "Treffpunkt" in column meldungstext.

I set a fulltext-index on the column "meldungstext" and tried this:

SELECT `meldungstext`
FROM `artikel`
WHERE MATCH (`meldungstext`)
AGAINST ('*punkt*')

this returns only 8 rows. I receive only rows which have matches to "Punkt" itself or words which I think are taken as "Punkt" as in "i-Punkt".

I then tried boolean mode:

SELECT `meldungstext`
FROM `artikel`
WHERE MATCH (`meldungstext`)
AGAINST ('*punkt*' IN BOOLEAN MODE)

returns 44 rows. I receive rows which have "Zwei-Punkte-Vorsprung" or "Treffpunkt" in column meldungstext, but not those with "Punkten".

Why does this happen and how can I set a "fully" working full-text-search to prevent using LIKE '%%' in the where-clause?

I took the three strings in your question and added it to a table plus three more string with pankt instead of punkt.

The following was executed using MySQL 5.5.12 for Windows

mysql> CREATE TABLE artikel
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     meldungstext MEDIUMTEXT,
    ->     PRIMARY KEY (id),
    ->     FULLTEXT (meldungstext)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO artikel (meldungstext) VALUES
    -> ('Punkten'),('Zwei-Punkte-Vorsprung'),('Treffpunkt'),
    -> ('Pankten'),('Zwei-Pankte-Vorsprung'),('Treffpankt');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>

I ran these queries against the table using 3 different approaches

• MATCH ... AGAINST

• LOCATE as in the LOCATE function

• LIKE

Please note the differences

mysql> SELECT id,meldungstext,
    -> COUNT(IF(MATCH (`meldungstext`) AGAINST ('*punkt*' IN BOOLEAN MODE),1,0)) PunktMatch,
    -> IF(LOCATE('punkt',meldungstext)>0,1,0) PunktLocate,
    -> meldungstext  LIKE '%punkt%' PunktLike
    -> FROM `artikel` GROUP BY id,meldungstext;
+----+-----------------------+------------+-------------+-----------+
| id | meldungstext          | PunktMatch | PunktLocate | PunktLike |
+----+-----------------------+------------+-------------+-----------+
|  1 | Punkten               |          1 |           1 |         1 |
|  2 | Zwei-Punkte-Vorsprung |          1 |           1 |         1 |
|  3 | Treffpunkt            |          1 |           1 |         1 |
|  4 | Pankten               |          1 |           0 |         0 |
|  5 | Zwei-Pankte-Vorsprung |          1 |           0 |         0 |
|  6 | Treffpankt            |          1 |           0 |         0 |
+----+-----------------------+------------+-------------+-----------+
6 rows in set (0.01 sec)

mysql>

All the PunktMatch values should bee 3 1's and 3 0's.

Now watch me query them as normal

mysql> SELECT `meldungstext` FROM `artikel`
    -> WHERE MATCH (`meldungstext`) AGAINST ('*punkt*' IN BOOLEAN MODE);
+-----------------------+
| meldungstext          |
+-----------------------+
| Zwei-Punkte-Vorsprung |
| Punkten               |
+-----------------------+
2 rows in set (0.01 sec)

mysql> SELECT `meldungstext` FROM `artikel`
    -> WHERE LOCATE('punkt',meldungstext)>0;
+-----------------------+
| meldungstext          |
+-----------------------+
| Punkten               |
| Zwei-Punkte-Vorsprung |
| Treffpunkt            |
+-----------------------+
3 rows in set (0.00 sec)

mysql> SELECT `meldungstext` FROM `artikel`
    -> WHERE `meldungstext` LIKE '%punk%';
+-----------------------+
| meldungstext          |
+-----------------------+
| Punkten               |
| Zwei-Punkte-Vorsprung |
| Treffpunkt            |
+-----------------------+
3 rows in set (0.00 sec)

mysql>

OK using MATCH .. AGAINST with punkt does not work. What about pankt ???

mysql> SELECT `meldungstext` FROM `artikel` WHERE `meldungstext` LIKE '%pankt%';
+-----------------------+
| meldungstext          |
+-----------------------+
| Pankten               |
| Zwei-Pankte-Vorsprung |
| Treffpankt            |
+-----------------------+
3 rows in set (0.00 sec)

mysql>

Let's run my big GROUP BY query against pankt

mysql> SELECT id,meldungstext,
    -> COUNT(IF(MATCH (`meldungstext`) AGAINST ('*pankt*' IN BOOLEAN MODE),1,0)) PanktMatch,
    -> IF(LOCATE('pankt',meldungstext)>0,1,0) PanktLocate,
    -> meldungstext  LIKE '%pankt%' PanktLike
    -> FROM `artikel` GROUP BY id,meldungstext;
+----+-----------------------+------------+-------------+-----------+
| id | meldungstext          | PanktMatch | PanktLocate | PanktLike |
+----+-----------------------+------------+-------------+-----------+
|  1 | Punkten               |          1 |           0 |         0 |
|  2 | Zwei-Punkte-Vorsprung |          1 |           0 |         0 |
|  3 | Treffpunkt            |          1 |           0 |         0 |
|  4 | Pankten               |          1 |           1 |         1 |
|  5 | Zwei-Pankte-Vorsprung |          1 |           1 |         1 |
|  6 | Treffpankt            |          1 |           1 |         1 |
+----+-----------------------+------------+-------------+-----------+
6 rows in set (0.01 sec)

mysql>

This is wrong also because I should see 3 0's and 3 1's for PanktMatch.

I tried something else

``
mysql> SELECT id,meldungstext, MATCH (meldungstext) AGAINST ('+pankt' IN BOOLEAN MODE) PanktMatch, IF(LOCATE('pankt',meldungstext)>0,1,0) PanktLocate, meldungstext LIKE '%pankt%' PanktLike FROM artikel` GROUP BY id,meldungstext;
+----+-----------------------+------------+-------------+-----------+
| id | meldungstext | PanktMatch | PanktLocate | PanktLike |
+----+-----------------------+------------+-------------+-----------+
| 1 | Punkten | 0 | 0 | 0 |
| 2 | Zwei-Punkte-Vorsprung | 0 | 0 |

StackExchange Database Administrators Q#21118, answer score: 14