Fundamentals of Full-Text Operators and Basic Search

In this tutorial, we will explore full-text search operators available in Manticore Search. All search operations in Manticore Search are based on standard boolean operators (AND, OR, NOT) possible to be used in combination and free order to combine or exclude keywords in a search for more relevant results. The default and the simplest full-text operator is AND which is assumed when you just enumerate few words in the search. is a default operator with which the query will return documents that have both terms: ‘fast’ and ‘slow’. If one term is in a document and the other is not, the document will be not included in the resulting list. By default, the words will be searched in all available full-text fields. AND ‘fast slow’ * testrt ( ); SELECT FROM WHERE MATCH 'fast slow' is used to match any term (or both). The terms are to be separated with a vertical line, e.g. . It will find documents with either ‘fast’ or ‘slow’. OR ‘fast | slow’ * testrt ( ); SELECT FROM WHERE MATCH 'fast | slow' he OR operator has a higher precedence than the AND, so the query can be interpreted as ‘find me (fast|slow)’: ‘find me fast|slow’ * testrt ( ); SELECT FROM WHERE MATCH 'find me fast | slow' makes sure the term marked with ‘-’ or ‘!’ is not in the results. Any documents containing such term will be excluded. E.g will find documents with ‘fast’ if only there’s no ‘slow’ in them. Be careful using it trying to reduce the search, as it may become too specific and can rule out good documents. NOT ‘fast !slow’ * testrt ( ); * testrt ( ); SELECT FROM WHERE MATCH 'find !slow' SELECT FROM WHERE MATCH 'find -slow' is a special operator that works like the ‘OR’, but requires the left term to always be in the results while the right term is optional. But when both are met the document will get a higher search rank. E.g. will find documents with either ‘fast’ or ‘slow’, but the documents including both terms will have a higher score. MAYBE ‘fast MAYBE slow’ * testrt ( ) SELECT FROM WHERE MATCH 'find MAYBE slow' Usage examples Let’s connect to Manticore using mysql client: mysql -P9306 -h0 For boolean searches the OR ( ‘|’) can be used: MySQL [(none)]> * testrt ( ); + | id | gid | title | content | + | 1 | 1 | find me | fast and quick| | 2 | 1 | find me fast | quick | | 6 | 1 | find me fast now | quick | | 5 | 1 | find me quick and fast | quick | + 4 rows in ( sec) select from where match 'find | me fast' ------+------+------------------------+----------------+ ------+------+------------------------+----------------+ ------+------+------------------------+----------------+ set 0.00 The OR operator has higher precedence than AND, the query ‘find me fast|slow’ is interpreted as ‘find me (fast|slow)’: MySQL [(none)]> * testrt ( ); + | id | gid | title | content | + | 1 | 1 | find me | fast and quick| | 2 | 1 | find me fast | quick | | 6 | 1 | find me fast now | quick | | 3 | 1 | find me slow | quick | | 5 | 1 | find me quick and fast | quick | + 5 rows in ( sec) SELECT FROM WHERE MATCH 'find me fast|slow' ------+------+------------------------+----------------+ ------+------+------------------------+----------------+ ------+------+------------------------+----------------+ set 0.00 For negations, the operator NOT can be specified as ‘-‘ or ‘!’ : MySQL [(none)]> * testrt ( ); + | id | gid | title | content | + | 3 | 1 | find me slow | quick | + 1 row in ( sec) select from where match 'find me -fast' ------+------+--------------+---------+ ------+------+--------------+---------+ ------+------+--------------+---------+ set 0.00 It must be noted that full negation queries are not supported in Manticore by default and it’s not possible to run just ‘-fast’ (will be possible since v3.5.2). Another basic operator is MAYBE. The term defined by the MAYBE can be present or not in the documents. If it’s present, it will influence the ranking and documents having it will be ranked higher. MySQL [(none)]> * testrt ( ); + | id | gid | title | content | + | 3 | 1 | find me slow | quick | | 1 | 1 | find me | fast and quick| | 2 | 1 | find me fast | quick | | 5 | 1 | find me quick and fast | quick | | 6 | 1 | find me fast now | quick | + 5 rows in ( sec) select from where match 'find me MAYBE slow' ------+------+------------------------+----------------+ ------+------+------------------------+----------------+ ------+------+------------------------+----------------+ set 0.00 Field operator If we want to limit the search to only a specific field operator ‘@’ can be used: mysql> * testrt ( ); + | id | gid | title | content | + | 2 | 1 | find me fast | quick | | 6 | 1 | find me fast now | quick | | 5 | 1 | find me quick and fast | quick | + 3 rows in ( sec) select from where match '@title find me fast' ------+------+------------------------+---------+ ------+------+------------------------+---------+ ------+------+------------------------+---------+ set 0.00 We can also specify multiple fields to limit the search: mysql> * testrt ( ); + | id | gid | title | content | + | 1 | 1 | find me | fast and quick | | 2 | 1 | find me fast | quick | | 6 | 1 | find me fast now | quick | | 5 | 1 | find me quick and fast | quick | + 4 rows in ( sec) select from where match '@(title,content) find me fast' ------+------+------------------------+----------------+ ------+------+------------------------+----------------+ ------+------+------------------------+----------------+ set 0.00 The field operator can also be used to perform a restriction for the search to be made only in the first x words. For example: mysql> * testrt ( ); + | id | gid | title | content | + | 4 | 1 | The quick brown fox jumps over the lazy dog | The five boxing wizards jump quickly | | 7 | 1 | The quick brown fox take a step back and jumps over the lazy dog | The five boxing wizards jump quickly | | 8 | 1 | The brown and beautiful fox take a step back and jumps over the lazy dog | The five boxing wizards jump quickly | + 4 rows in ( sec) select from where match '@title lazy dog' ------+------+----------------------------------------------------------------------------+---------------------------------------+ ------+------+----------------------------------------------------------------------------+---------------------------------------+ ------+------+----------------------------------------------------------------------------+---------------------------------------+ set 0.00 However, if we search in 5 first words only we get nothing: mysql> * testrt ( ); Empty ( sec) select from where match '@title[5] lazy dog' set 0.00 In some situations, the search could be performed over multiple indexes that may not have the same full-text fields. By default specifying a field that doesn’t exist in the index will result in a query error. To overcome this, the special operator ‘@@relaxed’ can be used: mysql> * testrt (‘@(title,keywords) lazy dog’); ERROR 1064 (42000): index testrt: query error: no field ‘keywords’ found in schema select from where match mysql> * testrt ( ); + | id | gid | title | content | + | 4 | 1 | The quick brown fox jumps over the lazy dog | The five boxing wizards jump quickly | | 7 | 1 | The quick brown fox take a step back and jumps over the lazy dog | The five boxing wizards jump quickly | | 8 | 1 | The brown and beautiful fox take a step back and jumps over the lazy dog | The five boxing wizards jump quickly | + 3 rows in , ( sec select from where match '@@relaxed @(title,keywords) lazy dog' ------+------+----------------------------------------------------------------------------+---------------------------------------+ ------+------+----------------------------------------------------------------------------+---------------------------------------+ ------+------+----------------------------------------------------------------------------+---------------------------------------+ set 1 warning 0.01 Fuzzy search Fuzzy matching allows to match only some of the words from a query string, for example: mysql> * testrt ( ); + | id | gid | title | content | + | 4 | 1 | The quick brown fox jumps over the lazy dog | The five boxing wizards jump quickly | | 7 | 1 | The quick brown fox take a step back and jumps over the lazy dog | The five boxing wizards jump quickly | | 8 | 1 | The brown and beautiful fox take a step back and jumps over the lazy dog | The five boxing wizards jump quickly | + 3 rows in ( sec) select from where match '"fox bird lazy dog"/3' ------+------+----------------------------------------------------------------------------+---------------------------------------+ ------+------+----------------------------------------------------------------------------+---------------------------------------+ ------+------+----------------------------------------------------------------------------+---------------------------------------+ set 0.00 In this case we use the QUORUM operator and specify that it is fine to match only 3 of the words. The search with ‘/1’ is equivalent of the OR boolean search while the search with ‘/N’ where the N is the number of the input words is equivalent of the AND search. Instead of an absolute number, you can also specify a number between 0.0 and 1.0 (standing for 0% and 100%), and Manticore will match only the documents with at least the specified percentage of the given words. The same example above could also have been written as “fox bird lazy dog”/0.3 and it would match documents with at least 30% of the 4 words. mysql> * testrt ( ); + | id | gid | title | content | + | 4 | 1 | The quick brown fox jumps over the lazy dog | The five boxing wizards jump quickly | | 7 | 1 | The quick brown fox take a step back and jumps over the lazy dog | The five boxing wizards jump quickly | | 8 | 1 | The brown and beautiful fox take a step back and jumps over the lazy dog | The five boxing wizards jump quickly | + 3 rows in ( sec) select from where match '"fox bird lazy dog"/0.3' ------+------+----------------------------------------------------------------------------+---------------------------------------+ ------+------+----------------------------------------------------------------------------+---------------------------------------+ ------+------+----------------------------------------------------------------------------+---------------------------------------+ set 0.00 Advanced operators Besides the simpler operators there are many advanced operators that are used more seldom, but in some cases can be absolutely necessary. One of the most used advanced operators is a phrase operator. The phrase operator will match only if the given words are found in the verbatim specified order. This will also restrict the words to be found in the same field: mysql> * testrt ( ); + | id | gid | title | content | + | 4 | 1 | The quick brown fox jumps over the lazy dog | The five boxing wizards jump quickly | | 7 | 1 | The quick brown fox take a step back and jumps over the lazy dog | The five boxing wizards jump quickly | + 2 rows in ( sec) SELECT FROM WHERE MATCH '"quick brown fox"' ------+------+-------------------------------------------------------------------+---------------------------------------+ ------+------+-------------------------------------------------------------------+---------------------------------------+ ------+------+-------------------------------------------------------------------+---------------------------------------+ set 0.00 A more relaxed version of the phrase operator is the strict order operator. The order operator requires the words to be found in the exact same order as specified, but other words are accepted between: mysql> * testrt ( ); + | id | gid | title | content | + | 2 | 1 | find me fast | quick | | 6 | 1 | find me fast now | quick | | 5 | 1 | find me quick and fast | quick | + 3 rows in ( sec) SELECT FROM WHERE MATCH 'find * testrt ( ); + | id | gid | title | content | + | 2 | 1 | find me fast | quick | | 5 | 1 | find me quick and fast | quick | + 2 rows in ( sec) SELECT FROM WHERE MATCH '^find me fast$' ------+------+------------------------+---------+ ------+------+------------------------+---------+ ------+------+------------------------+---------+ set 0.00 The proximity operator is similar to the AND operator but adds the maximum distance between the words so they can still be considered a match. Let’s take this example with just the AND operator: mysql> * testrt ( ); + | id | gid | title | content | + | 4 | 1 | The quick brown fox jumps over the lazy dog | The five boxing wizards jump quickly | | 7 | 1 | The quick brown fox take a step back and jumps over the lazy dog | The five boxing wizards jump quickly | | 8 | 1 | The brown and beautiful fox take a step back and jumps over the lazy dog | The five boxing wizards jump quickly | + 3 rows in ( sec) SELECT FROM WHERE MATCH 'brown fox jumps' ------+------+----------------------------------------------------------------------------+---------------------------------------+ ------+------+----------------------------------------------------------------------------+---------------------------------------+ ------+------+----------------------------------------------------------------------------+---------------------------------------+ set 0.00 Our query returns 2 results: one in which all the words are close to each other and the second where one of the words is more distant. If we want to match only if the words are within a certain distance, we can restrict that with the proximity operator: mysql> * testrt ( ); + | id | gid | title | content | + | 4 | 1 | The quick brown fox jumps over the lazy dog | The five boxing wizards jump quickly | + 1 row in ( sec) SELECT FROM WHERE MATCH '"brown fox jumps"~5' ------+------+---------------------------------------------+---------------------------------------+ ------+------+---------------------------------------------+---------------------------------------+ ------+------+---------------------------------------------+---------------------------------------+ set 0.00 A more generalized version of the proximity operator is the NEAR operator. In the case of the proximity a single distance is specified over a bag of words, while the NEAR operator works with 2 operands, that can be either single words or expressions. In the following example, ‘brown’ and ‘fox’ must be within a distance of 2 and ‘fox’ and ‘jumps’ within a distance of 6: mysql> * testrt ( ); + | id | gid | title | content | + | 4 | 1 | The quick brown fox jumps over the lazy dog | The five boxing wizards jump quickly | | 7 | 1 | The quick brown fox take a step back and jumps over the lazy dog | The five boxing wizards jump quickly | + 2 rows in ( sec) SELECT FROM WHERE MATCH 'brown NEAR/2 fox NEAR/6 jumps' ------+------+-------------------------------------------------------------------+---------------------------------------+ ------+------+-------------------------------------------------------------------+---------------------------------------+ ------+------+-------------------------------------------------------------------+---------------------------------------+ set 0.00 The query leaves out one document which doesn’t match the first NEAR condition (the last one here): mysql> * testrt ( ); + | id | gid | title | content | + | 4 | 1 | The quick brown fox jumps over the lazy dog | The five boxing wizards jump quickly | | 7 | 1 | The quick brown fox take a step back and jumps over the lazy dog | The five boxing wizards jump quickly | | 8 | 1 | The brown and beautiful fox take a step back and jumps over the lazy dog | The five boxing wizards jump quickly | + 3 rows in ( sec) SELECT FROM WHERE MATCH 'brown NEAR/3 fox NEAR/6 jumps' ------+------+----------------------------------------------------------------------------+---------------------------------------+ ------+------+----------------------------------------------------------------------------+---------------------------------------+ ------+------+----------------------------------------------------------------------------+---------------------------------------+ set 0.09 A variation of the NEAR operator is NOTNEAR, which matches only if the operands have a minimum distance between them. mysql> * testrt ( ); + | id | gid | title | content | + | 7 | 1 | The quick brown fox take a step back and jumps over the lazy dog | The five boxing wizards jump quickly | + 1 row in ( sec) SELECT FROM WHERE MATCH '"brown fox" NOTNEAR/5 jumps' ------+------+-------------------------------------------------------------------+---------------------------------------+ ------+------+-------------------------------------------------------------------+---------------------------------------+ ------+------+-------------------------------------------------------------------+---------------------------------------+ set 0.00 Manticore can also detect sentences in plain texts and paragraphs in HTML content. For indexing sentences, the option needs to be enabled, while paragraphs also require index_sp html_strip =1 Let’s take the following example: mysql> * testrt ( )\G *************************** *************************** : gid: title: The brown fox takes a step back. it jumps the lazydog : ( sec) select from where match '"the brown fox" jumps' 1. row id 15 2 Then over content 1 row in set 0.00 The document includes 2 sentences, while the phrase is found in the first one ‘jumps’ is only in the second sentence. With the SENTENCE operator we can restrict the search to match only if the operands are in the same sentence: mysql> * testrt ( )\G ( sec) select from where match '"the brown fox" SENTENCE jumps' Empty set 0.00 We can see that the document is not a match anymore. If we correct the search query so all the words are from the same sentence we’ll see a match: mysql> * testrt ( )\G *************************** *************************** : gid: title: The brown fox takes a step back. it jumps the lazydog : ( sec) select from where match '"the brown fox" SENTENCE back' 1. row id 15 2 Then over content 1 row in set 0.00 To demonstrate the PARAGRAPH let’s use the following search: mysql> * testrt ( ); + | id | gid | title | content | + | 9 | 2 | Samsung Galaxy S10 Is a smartphone introduced by Samsung in 2019 | | | 10 | 2 | Samsung Galaxy,Note,A,J | | + 2 rows in ( sec) select from where match 'Samsung Galaxy' ------+------+-------------------------------------------------------------------------------------+---------+ ------+------+-------------------------------------------------------------------------------------+---------+ ------+------+-------------------------------------------------------------------------------------+---------+ set 0.00 These 2 documents have different HTML tags If we add the PARAGRAPH only the document with the search terms found in the single tag will remain. The more general operators are ZONE and it’s variant ZONESPAN. “zone” is text inside an HTML or XML tag. The tags to be considered for the zones need to be declared in the ‘index_zones’ setting, like ‘index_zones = h*, th, title’. For example: mysql> * testrt ( ); + | id | gid | title | content | + | 12 | 2 | Hello world | | | 14 | 2 | Hello world | | | 13 | 2 | Hello world | | + 3 rows in ( sec) select from where match 'hello world' ------+------+-------------------------------+---------+ ------+------+-------------------------------+---------+ ------+------+-------------------------------+---------+ set 0.00 We have 3 documents, where ‘hello’ and ‘world’ are found in plain text, in different zones of the same type or in a single zone. mysql> * testrt ( ); + | id | gid | title | content | + | 14 | 2 | Hello world | | | 13 | 2 | Hello world | | + 2 rows in ( sec) select from where match 'ZONE:h1 hello world' ------+------+-------------------------------+---------+ ------+------+-------------------------------+---------+ ------+------+-------------------------------+---------+ set 0.00 In this case, the words are present in H1 zones, but they are not required to be in the same zone. If we want to limit the match to a single zone, we can use ZONESPAN: mysql> * testrt ( ); + | id | gid | title | content | + | 14 | 2 | Hello world | | + 1 row in ( sec) select from where match 'ZONESPAN:h1 hello world' ------+------+----------------------+---------+ ------+------+----------------------+---------+ ------+------+----------------------+---------+ set 0.00 Hopefully, from this article, you’ve learned how full-text search operators work in Manticore. If you are looking for a hands-on experience to learn it even better you can try our interactive course right now in your browser. Previously published at https://manticoresearch.com/2020/09/16/manticore-full-text-operators-definitive-guide/