Manticore/Sphinx equivalent of MySQL `LIKE '%term%'`?

General Discussions - English
1

Is there any way to achieve it? Even it will cost more indexing?

Mysql:

mysql> select count(*) from search where email like '%johndoe%';
+----------+
| count(*) |
+----------+
|      102 |
+----------+
1 row in set (14.99 sec)

Manticore:

mysql> SELECT count(*) FROM Search1 WHERE MATCH('@email johndoe') LIMIT 1000 OPTION max_matches=1000;
+----------+
| count(*) |
+----------+
|       16 |
+----------+
1 row in set (0.00 sec)
2

You need to set min_infix_len option in Index configuration, and then you can use *word* to match middle of the word.

SELECT count(*) FROM Search1 WHERE MATCH('@email *johndoe*') LIMIT 1000 OPTION max_matches=1000;
1 Like
3

When min_infix_len is enabled, you can use following special characters in words:

? match one letter
% match zero or one letter
* match zero or more letters

(I don’t remember where exactly this is documented, this was a bit difficult to find as this is NOT mentioned in “Extended query syntax” as one might expect.)

1 Like
4

Tried with min_prefix_len = 1 and min_word_len = 1, got more results (85 out of mysql’s 102), even so it doesn’t match words like lil_johndoe_24 mr_johndoe23 or johndoe_i_am

5

you have to use ‘min_inxif_len’ and reindex your data

1 Like
6

Perfect! Seems min_infix_len = 2 is doing it’s job!

mysql> SELECT count(*) FROM Search1 WHERE MATCH('@email johndoe') LIMIT 1000 OPTION max_matches=1000;
+----------+
| count(*) |
+----------+
|      102 |
+----------+
1 row in set (0.72 sec)