Exact search by substring in field

[d47081]

Not first time, my attempt to use Manticore as MySQL replacement getting fail.

For example, I have crawler, that collects URL by some configured conditions using PHP strpos method.
After some time, configuration of URL rules get updates and I want to cleanup the index, but can't select exact records by substring using Manticore search query.

Here is my implementation:
https://github.com/YGGverse/Yo/blob/main/src/cli/document/clean.php#L82

    foreach(
        $index->search(
            sprintf(
                '@url "*%s*"',
                @\Manticoresearch\Utils::escape(
                    $condition
                )
            )
        )->get() as $document)
    {
        // Make sure document contain exact substring in URL
        if (false === mb_strpos($document->get('url'), $condition))
        {
            continue;
        }

        // Delete found document by it ID
        $result = $index->deleteDocument(
            $document->getId()
        );
        ...

In this case, I can't directly delete records by $condition using just deleteDocuments method,
because it mismatch strpos method in search results and drops another records, that for example contain just one char # or special char like / postfix.

So I do this shitcode by selecting estimated results, then double check it with PHP strpos. I don't understand how to make it simpler, because with MySQL I can select exact results like by using PHP.

Maybe deleteDocuments works with few test records well, but I have about 10M ones, and that not returns substring exactly wanted.

[sanikolaev]

If you think it's a bug please provide a reproducible example, so we can look into it. There is one important thing to take into account - full-text tokenization. E.g. here you can see that *ath/t* finds both https://domain.com/path/to/page#anchor and https://math.tld/smth/else. The reason is that it doesn't care about / since it's a delimiter, so math.tld also matches. To change the behavioiur, you'd have to add / to the charset_table. If you do that, pls provide an example of how it still is not working. Otherwise, consider updating your charset_table or using a string attribute instead. You can control yourself using the CALL KEYWORDS sql statement as shown below. If you don't see some character in the output - there may be a problem with it.

mysql> drop table if exists t; create table t(url text) min_infix_len='2'; insert into t(url) values('https://domain.com/path/to/page#anchor'),('https://math.tld/smth/else'); call keywords('https://domain.com/path/to/page#anchor', 't'); select * from t where match('@url *om*'); select * from t where match('*ath\\/t*');
--------------
drop table if exists t
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
create table t(url text) min_infix_len='2'
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
insert into t(url) values('https://domain.com/path/to/page#anchor'),('https://math.tld/smth/else')
--------------

Query OK, 2 rows affected (0.00 sec)

--------------
call keywords('https://domain.com/path/to/page#anchor', 't')
--------------

+------+-----------+------------+
| qpos | tokenized | normalized |
+------+-----------+------------+
| 1    | https     | https      |
| 2    | domain    | domain     |
| 3    | com       | com        |
| 4    | path      | path       |
| 5    | to        | to         |
| 6    | page      | page       |
| 7    | anchor    | anchor     |
+------+-----------+------------+
7 rows in set (0.00 sec)

--------------
select * from t where match('@url *om*')
--------------

+---------------------+----------------------------------------+
| id                  | url                                    |
+---------------------+----------------------------------------+
| 1515799027835207691 | https://domain.com/path/to/page#anchor |
+---------------------+----------------------------------------+
1 row in set (0.00 sec)
--- 1 out of 1 results in 0ms ---

--------------
select * from t where match('*ath\\/t*')
--------------

+---------------------+----------------------------------------+
| id                  | url                                    |
+---------------------+----------------------------------------+
| 1515799027835207691 | https://domain.com/path/to/page#anchor |
| 1515799027835207692 | https://math.tld/smth/else             |
+---------------------+----------------------------------------+
2 rows in set (0.00 sec)
--- 2 out of 2 results in 0ms ---