Manticore's data types can be split into two categories: full-text fields and attributes.
Field names in Manticore must follow these rules:
- Can contain letters (a-z, A-Z), numbers (0-9), and hyphens (-)
- Must start with a letter
- Numbers can only appear after letters
- Underscore (
_) is the only allowed special character - Field names are case-insensitive
For example:
- Valid field names:
title,product_id,user_name_2 - Invalid field names:
2title,-price,user@name
Full-text fields:
- can be indexed with natural language processing algorithms, therefore can be searched for keywords
- cannot be used for sorting or grouping
- original document's content can be retrieved
- original document's content can be used for highlighting
Full-text fields are represented by the data type text. All other data types are called "attributes".
Attributes are non-full-text values associated with each document that can be used to perform non-full-text filtering, sorting and grouping during a search.
It is often desired to process full-text search results based not only on matching document ID and its rank, but also on a number of other per-document values. For example, one might need to sort news search results by date and then relevance, or search through products within a specified price range, or limit a blog search to posts made by selected users, or group results by month. To do this efficiently, Manticore enables not only full-text fields, but also additional attributes to be added to each document. These attributes can be used to filter, sort, or group full-text matches, or to search only by attributes.
The attributes, unlike full-text fields, are not full-text indexed. They are stored in the table, but it is not possible to search them as full-text.
A good example for attributes would be a forum posts table. Assume that only the title and content fields need to be full-text searchable - but that sometimes it is also required to limit search to a certain author or a sub-forum (i.e., search only those rows that have some specific values of author_id or forum_id); or to sort matches by post_date column; or to group matching posts by month of the post_date and calculate per-group match counts.
- SQL
- JSON
- PHP
- Python
- Javascript
- Java
- C#
- config
CREATE TABLE forum(title text, content text, author_id int, forum_id int, post_date timestamp);POST /cli -d "CREATE TABLE forum(title text, content text, author_id int, forum_id int, post_date timestamp)"$index = new \Manticoresearch\Index($client);
$index->setName('forum');
$index->create([
'title'=>['type'=>'text'],
'content'=>['type'=>'text'],
'author_id'=>['type'=>'int'],
'forum_id'=>['type'=>'int'],
'post_date'=>['type'=>'timestamp']
]);utilsApi.sql('CREATE TABLE forum(title text, content text, author_id int, forum_id int, post_date timestamp)')res = await utilsApi.sql('CREATE TABLE forum(title text, content text, author_id int, forum_id int, post_date timestamp)');utilsApi.sql("CREATE TABLE forum(title text, content text, author_id int, forum_id int, post_date timestamp)");
utilsApi.Sql("CREATE TABLE forum(title text, content text, author_id int, forum_id int, post_date timestamp)");
table forum
{
type = rt
path = forum
# when configuring fields via config, they are indexed (and not stored) by default
rt_field = title
rt_field = content
# this option needs to be specified for the field to be stored
stored_fields = title, content
rt_attr_uint = author_id
rt_attr_uint = forum_id
rt_attr_timestamp = post_date
}This example shows running a full-text query filtered by author_id, forum_id and sorted by post_date.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
select * from forum where author_id=123 and forum_id in (1,3,7) order by post_date descPOST /search
{
"table": "forum",
"query":
{
"match_all": {},
"bool":
{
"must":
[
{ "equals": { "author_id": 123 } },
{ "in": { "forum_id": [1,3,7] } }
]
}
},
"sort": [ { "post_date": "desc" } ]
}$client->search([
'table' => 'forum',
'query' =>
[
'match_all' => [],
'bool' => [
'must' => [
'equals' => ['author_id' => 123],
'in' => [
'forum_id' => [
1,3,7
]
]
]
]
],
'sort' => [
['post_date' => 'desc']
]
]);searchApi.search({"table":"forum","query":{"match_all":{},"bool":{"must":[{"equals":{"author_id":123}},{"in":{"forum_id":[1,3,7]}}]}},"sort":[{"post_date":"desc"}]})res = await searchApi.search({"table":"forum","query":{"match_all":{},"bool":{"must":[{"equals":{"author_id":123}},{"in":{"forum_id":[1,3,7]}}]}},"sort":[{"post_date":"desc"}]});HashMap<String,Object> filters = new HashMap<String,Object>(){{
put("must", new HashMap<String,Object>(){{
put("equals",new HashMap<String,Integer>(){{
put("author_id",123);
}});
put("in",
new HashMap<String,Object>(){{
put("forum_id",new int[] {1,3,7});
}});
}});
}};
Map<String,Object> query = new HashMap<String,Object>();
query.put("match_all",null);
query.put("bool",filters);
SearchRequest searchRequest = new SearchRequest();
searchRequest.setIndex("forum");
searchRequest.setQuery(query);
searchRequest.setSort(new ArrayList<Object>(){{
add(new HashMap<String,String>(){{ put("post_date","desc");}});
}});
SearchResponse searchResponse = searchApi.search(searchRequest);object query = new { match_all=null };
var searchRequest = new SearchRequest("forum", query);
var boolFilter = new BoolFilter();
boolFilter.Must = new List<Object> {
new EqualsFilter("author_id", 123),
new InFilter("forum_id", new List<Object> {1,3,7})
};
searchRequest.AttrFilter = boolFilter;
searchRequest.Sort = new List<Object> { new SortOrder("post_date", SortOrder.OrderEnum.Desc) };
var searchResponse = searchApi.Search(searchRequest);Manticore supports two types of attribute storages:
- row-wise - traditional storage available in Manticore Search out of the box
- columnar - provided by Manticore Columnar Library
As can be understood from their names, they store data differently. The traditional row-wise storage:
- stores attributes uncompressed
- all attributes of the same document are stored in one row close to each other
- rows are stored one by one
- accessing attributes is basically done by just multiplying the row ID by the stride (length of a single vector) and getting the requested attribute from the calculated memory location. It gives very low random access latency.
- attributes have to be in memory to get acceptable performance, otherwise due to the row-wise nature of the storage Manticore may have to read from disk too much unneeded data which is in many cases suboptimal.
With the columnar storage:
- each attribute is stored independently of all other attributes in its separate "column"
- storage is split into blocks of 65536 entries
- the blocks are stored compressed. This often allows storing just a few distinct values instead of storing all of them like in the row-wise storage. High compression ratio allows reading from disk faster and makes the memory requirement much lower
- when data is indexed, storage scheme is selected for each block independently. For example, if all values in a block are the same, it gets "const" storage and only one value is stored for the whole block. If there are less than 256 unique values per block, it gets "table" storage and stores indexes to a table of values instead of the values themselves
- search in a block can be early rejected if it's clear the requested value is not present in the block.
The columnar storage was designed to handle large data volume that does not fit into RAM, so the recommendations are:
- if you have enough memory for all your attributes you will benefit from the row-wise storage
- otherwise, the columnar storage can still give you decent performance with a much lower memory footprint which will allow you to store much more documents in your table
The traditional row-wise storage is the default, so if you want everything to be stored in a row-wise fashion, you don't need to do anything when you create a table.
To enable the columnar storage you need to:
- specify
engine='columnar'in CREATE TABLE to make all attributes of the table columnar. Then, if you want to keep a specific attribute row-wise, you need to addengine='rowwise'when you declare it. For example:create table tbl(title text, type int, price float engine='rowwise') engine='columnar' - specify
engine='columnar'for a specific attribute inCREATE TABLEto make it columnar. For example:create table tbl(title text, type int, price float engine='columnar');or
create table tbl(title text, type int, price float engine='columnar') engine='rowwise'; - in the plain mode you need to list attributes you want to be columnar in columnar_attrs.
Below is the list of data types supported by Manticore Search:
The document identifier is a mandatory attribute that must be a unique 64-bit unsigned integer. Document IDs can be explicitly specified when creating a table, but they are always enabled even if not specified. Document IDs cannot be updated.
When you create a table, you can specify ID explicitly, but regardless of the data type you use, it will always behave as described above - stored as unsigned 64-bit but exposed as signed 64-bit integer.
mysql> CREATE TABLE tbl(id bigint, content text);
DESC tbl;
+---------+--------+----------------+
| Field | Type | Properties |
+---------+--------+----------------+
| id | bigint | |
| content | text | indexed stored |
+---------+--------+----------------+
2 rows in set (0.00 sec)You can also omit specifying ID at all, it will be enabled automatically.
mysql> CREATE TABLE tbl(content text);
DESC tbl;
+---------+--------+----------------+
| Field | Type | Properties |
+---------+--------+----------------+
| id | bigint | |
| content | text | indexed stored |
+---------+--------+----------------+
2 rows in set (0.00 sec) When working with document IDs, it's important to know that they are stored internally as unsigned 64-bit integers but are exposed as signed 64-bit integers in queries and results. This means:
- IDs greater than 2^63-1 will appear as negative numbers.
- When filtering by such large IDs, you must use their signed representation.
- Use the UINT64() function to view the actual unsigned value.
For example, let's create a table and insert some values around 2^63:
mysql> create table t(id_text string)
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t values(9223372036854775807, '2 ^ 63 - 1'),(9223372036854775808, '2 ^ 63')
Query OK, 2 rows affected (0.00 sec)Some IDs appear as negative numbers in the results because they exceed 2^63-1. However, using UINT64(id) can reveal their actual unsigned values:
mysql> select *, uint64(id) from t
+----------------------+------------+---------------------+
| id | id_text | uint64(id) |
+----------------------+------------+---------------------+
| 9223372036854775807 | 2 ^ 63 - 1 | 9223372036854775807 |
| -9223372036854775808 | 2 ^ 63 | 9223372036854775808 |
+----------------------+------------+---------------------+
2 rows in set (0.00 sec)
--- 2 out of 2 results in 0ms ---For querying documents with IDs less than 2^63, you can use the unsigned value directly:
mysql> select * from t where id = 9223372036854775807
+---------------------+------------+
| id | id_text |
+---------------------+------------+
| 9223372036854775807 | 2 ^ 63 - 1 |
+---------------------+------------+
1 row in set (0.00 sec)
--- 1 out of 1 results in 0ms ---However, for IDs starting from 2^63, you need to use the signed value:
mysql> select * from t where id = -9223372036854775808
+----------------------+---------+
| id | id_text |
+----------------------+---------+
| -9223372036854775808 | 2 ^ 63 |
+----------------------+---------+
1 row in set (0.00 sec)
--- 1 out of 1 results in 0ms ---If you use an unsigned value instead, you might get incorrect results:
mysql> select * from t where id = 9223372036854775808
+---------------------+------------+
| id | id_text |
+---------------------+------------+
| 9223372036854775807 | 2 ^ 63 - 1 |
+---------------------+------------+
1 row in set (0.00 sec)
--- 1 out of 1 results in 0ms ---General syntax:
string|text [stored|attribute] [indexed]Properties:
indexed- full-text indexed (can be used in full-text queries)stored- stored in a docstore (stored on disk, not in RAM, lazy read)attribute- makes it a string attribute (can sort/group by it)
Specifying at least one property overrides all the default ones (see below), i.e., if you decide to use a custom combination of properties, you need to list all the properties you want.
No properties specified:
string and text are aliases, but if you don't specify any properties, they by default mean different things:
- just
stringby default meansattribute(see details below). - just
textby default meansstored+indexed(see details below).
The text (just text or text/string indexed) data type forms the full-text part of the table. Text fields are indexed and can be searched for keywords.
Text is passed through an analyzer pipeline that converts the text to words, applies morphology transformations, etc. Eventually, a full-text table (a special data structure that enables quick searches for a keyword) gets built from that text.
Full-text fields can only be used in the MATCH() clause and cannot be used for sorting or aggregation. Words are stored in an inverted index along with references to the fields they belong to and positions in the field. This allows searching a word inside each field and using advanced operators like proximity. By default, the original text of the fields is both indexed and stored in document storage. It means that the original text can be returned with the query results and used in search result highlighting.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
- config
CREATE TABLE products(title text);POST /cli -d "CREATE TABLE products(title text)"$index = new \Manticoresearch\Index($client);
$index->setName('products');
$index->create([
'title'=>['type'=>'text']
]);utilsApi.sql('CREATE TABLE products(title text)')res = await utilsApi.sql('CREATE TABLE products(title text)');utilsApi.sql("CREATE TABLE products(title text)");utilsApi.Sql("CREATE TABLE products(title text)");table products
{
type = rt
path = products
# when configuring fields via config, they are indexed (and not stored) by default
rt_field = title
# this option needs to be specified for the field to be stored
stored_fields = title
}This behavior can be overridden by explicitly specifying that the text is only indexed.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
- config
CREATE TABLE products(title text indexed);POST /cli -d "CREATE TABLE products(title text indexed)"$index = new \Manticoresearch\Index($client);
$index->setName('products');
$index->create([
'title'=>['type'=>'text','options'=>['indexed']]
]);utilsApi.sql('CREATE TABLE products(title text indexed)')res = await utilsApi.sql('CREATE TABLE products(title text indexed)');utilsApi.sql("CREATE TABLE products(title text indexed)");utilsApi.Sql("CREATE TABLE products(title text indexed)");table products
{
type = rt
path = products
# when configuring fields via config, they are indexed (and not stored) by default
rt_field = title
}Fields are named, and you can limit your searches to a single field (e.g. search through "title" only) or a subset of fields (e.g. "title" and "abstract" only). You can have up to 256 full-text fields.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
select * from products where match('@title first');POST /search
{
"table": "products",
"query":
{
"match": { "title": "first" }
}
}$index->setName('products')->search('@title')->get();
searchApi.search({"table":"products","query":{"match":{"title":"first"}}})res = await searchApi.search({"table":"products","query":{"match":{"title":"first"}}});utilsApi.sql("CREATE TABLE products(title text indexed)");utilsApi.Sql("CREATE TABLE products(title text indexed)");Unlike full-text fields, string attributes (just string or string/text attribute) are stored as they are received and cannot be used in full-text searches. Instead, they are returned in results, can be used in the WHERE clause for comparison filtering or REGEX, and can be used for sorting and aggregation. In general, it's not recommended to store large texts in string attributes, but use string attributes for metadata like names, titles, tags, keys.
If you want to also index the string attribute, you can specify both as string attribute indexed. It will allow full-text searching and works as an attribute.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
- config
CREATE TABLE products(title text, keys string);POST /cli -d "CREATE TABLE products(title text, keys string)"$index = new \Manticoresearch\Index($client);
$index->setName('products');
$index->create([
'title'=>['type'=>'text'],
'keys'=>['type'=>'string']
]);utilsApi.sql('CREATE TABLE products(title text, keys string)')res = await utilsApi.sql('CREATE TABLE products(title text, keys string)');utilsApi.sql("CREATE TABLE products(title text, keys string)");utilsApi.Sql("CREATE TABLE products(title text, keys string)");table products
{
type = rt
path = products
rt_field = title
stored_fields = title
rt_attr_string = keys
}Manticore doesn't have a dedicated field type for binary data, but you can store it safely by using base64 encoding and the text stored or string stored field types (which are synonyms). If you don't encode the binary data, parts of it may get lost — for example, Manticore trims the end of a string if it encounters a null-byte.
Here is an example where we encode the ls command using base64, store it in Manticore, and then decode it to verify that the MD5 checksum remains unchanged:
- Example
# md5sum /bin/ls
43d1b8a7ccda411118e2caba685f4329 /bin/ls
# encoded_data=`base64 -i /bin/ls `
# mysql -P9306 -h0 -e "drop table if exists test; create table test(data text stored); insert into test(data) values('$encoded_data')"
# mysql -P9306 -h0 -NB -e "select data from test" | base64 -d > /tmp/ls | md5sum
43d1b8a7ccda411118e2caba685f4329 -Integer type allows storing 32 bit unsigned integer values.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
- config
CREATE TABLE products(title text, price int);POST /cli -d "CREATE TABLE products(title text, price int)"$index = new \Manticoresearch\Index($client);
$index->setName('products');
$index->create([
'title'=>['type'=>'text'],
'price'=>['type'=>'int']
]);utilsApi.sql('CREATE TABLE products(title text, price int)')res = await utilsApi.sql('CREATE TABLE products(title text, price int)');utilsApi.sql("CREATE TABLE products(title text, price int)");utilsApi.Sql("CREATE TABLE products(title text, price int)");table products
{
type = rt
path = products
rt_field = title
stored_fields = title
rt_attr_uint = type
}Integers can be stored in shorter sizes than 32-bit by specifying a bit count. For example, if we want to store a numeric value which we know is not going to be bigger than 8, the type can be defined as bit(3). Bitcount integers perform slower than the full-size ones, but they require less RAM. They are saved in 32-bit chunks, so in order to save space, they should be grouped at the end of attribute definitions (otherwise a bitcount integer between 2 full-size integers will occupy 32 bits as well).
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
- config
CREATE TABLE products(title text, flags bit(3), tags bit(2) );POST /cli -d "CREATE TABLE products(title text, flags bit(3), tags bit(2))"$index = new \Manticoresearch\Index($client);
$index->setName('products');
$index->create([
'title'=>['type'=>'text'],
'flags'=>['type'=>'bit(3)'],
'tags'=>['type'=>'bit(2)']
]);utilsApi.sql('CREATE TABLE products(title text, flags bit(3), tags bit(2) ')res = await utilsApi.sql('CREATE TABLE products(title text, flags bit(3), tags bit(2) ');utilsApi.sql("CREATE TABLE products(title text, flags bit(3), tags bit(2)");utilsApi.Sql("CREATE TABLE products(title text, flags bit(3), tags bit(2)");table products
{
type = rt
path = products
rt_field = title
stored_fields = title
rt_attr_uint = flags:3
rt_attr_uint = tags:2
}Big integers (bigint) are 64-bit wide signed integers.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
- config
CREATE TABLE products(title text, price bigint );POST /cli -d "CREATE TABLE products(title text, price bigint)"$index = new \Manticoresearch\Index($client);
$index->setName('products');
$index->create([
'title'=>['type'=>'text'],
'price'=>['type'=>'bigint']
]);utilsApi.sql('CREATE TABLE products(title text, price bigint )')res = await utilsApi.sql('CREATE TABLE products(title text, price bigint )');utilsApi.sql("CREATE TABLE products(title text, price bigint )");utilsApi.Sql("CREATE TABLE products(title text, price bigint )");table products
{
type = rt
path = products
rt_field = title
stored_fields = title
rt_attr_bigint = type
}Declares a boolean attribute. It's equivalent to an integer attribute with bit count of 1.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
- config
CREATE TABLE products(title text, sold bool );POST /cli -d "CREATE TABLE products(title text, sold bool)"$index = new \Manticoresearch\Index($client);
$index->setName('products');
$index->create([
'title'=>['type'=>'text'],
'sold'=>['type'=>'bool']
]);utilsApi.sql('CREATE TABLE products(title text, sold bool )')res = await utilsApi.sql('CREATE TABLE products(title text, sold bool )');utilsApi.sql("CREATE TABLE products(title text, sold bool )");utilsApi.Sql("CREATE TABLE products(title text, sold bool )");table products
{
type = rt
path = products
rt_field = title
stored_fields = title
rt_attr_bool = sold
}The timestamp type represents Unix timestamps, which are stored as 32-bit integers. Unlike basic integers, the timestamp type allows the use of time and date functions. Conversion from string values follows these rules:
- Numbers without delimiters, at least 10 characters long, are converted to timestamps as is.
%Y-%m-%dT%H:%M:%E*S%Z%Y-%m-%d'T'%H:%M:%S%Z%Y-%m-%dT%H:%M:%E*S%Y-%m-%dT%H:%M:%s%Y-%m-%dT%H:%M%Y-%m-%dT%H%Y-%m-%d%Y-%m%Y
The meanings of these conversion specifiers are detailed in the strptime manual, except for %E*S, which stands for milliseconds.
Note that auto-conversion of timestamps is not supported in plain tables.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
- config
CREATE TABLE products(title text, date timestamp);POST /cli -d "CREATE TABLE products(title text, date timestamp)"$index = new \Manticoresearch\Index($client);
$index->setName('products');
$index->create([
'title'=>['type'=>'text'],
'date'=>['type'=>'timestamp']
]);utilsApi.sql('CREATE TABLE products(title text, date timestamp)')res = await utilsApi.sql('CREATE TABLE products(title text, date timestamp)');utilsApi.sql("CREATE TABLE products(title text, date timestamp)");utilsApi.Sql("CREATE TABLE products(title text, date timestamp)");table products
{
type = rt
path = products
rt_field = title
stored_fields = title
rt_attr_timestamp = date
}Real numbers are stored as 32-bit IEEE 754 single precision floats.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
- config
CREATE TABLE products(title text, coeff float);POST /cli -d "CREATE TABLE products(title text, coeff float)"$index = new \Manticoresearch\Index($client);
$index->setName('products');
$index->create([
'title'=>['type'=>'text'],
'coeff'=>['type'=>'float']
]);utilsApi.sql('CREATE TABLE products(title text, coeff float)')res = await utilsApi.sql('CREATE TABLE products(title text, coeff float)');utilsApi.sql("CREATE TABLE products(title text, coeff float)");utilsApi.Sql("CREATE TABLE products(title text, coeff float)");table products
{
type = rt
path = products
rt_field = title
stored_fields = title
rt_attr_float = coeff
}Unlike integer types, comparing two floating-point numbers for equality is not recommended due to potential rounding errors. A more reliable approach is to use a near-equal comparison, by checking the absolute error margin.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
select abs(a-b)<=0.00001 from productsPOST /search
{
"table": "products",
"query": { "match_all": {} } },
"expressions": { "eps": "abs(a-b)" }
}$index->setName('products')->search('')->expression('eps','abs(a-b)')->get();searchApi.search({"table":"products","query":{"match_all":{}},"expressions":{"eps":"abs(a-b)"}})res = await searchApi.search({"table":"products","query":{"match_all":{}},"expressions":{"eps":"abs(a-b)"}});searchRequest = new SearchRequest();
searchRequest.setIndex("forum");
query = new HashMap<String,Object>();
query.put("match_all",null);
searchRequest.setQuery(query);
Object expressions = new HashMap<String,Object>(){{
put("ebs","abs(a-b)");
}};
searchRequest.setExpressions(expressions);
searchResponse = searchApi.search(searchRequest);object query = new { match_all=null };
var searchRequest = new SearchRequest("forum", query);
searchRequest.Expressions = new List<Object>{
new Dictionary<string, string> { {"ebs", "abs(a-b)"} }
};
var searchResponse = searchApi.Search(searchRequest);Another alternative, which can also be used to perform IN(attr,val1,val2,val3) is to compare floats as integers by choosing a multiplier factor and convert the floats to integers in operations. The following example illustrates modifying IN(attr,2.0,2.5,3.5) to work with integer values.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
select in(ceil(attr*100),200,250,350) from productsPOST /search
{
"table": "products",
"query": { "match_all": {} } },
"expressions": { "inc": "in(ceil(attr*100),200,250,350)" }
}$index->setName('products')->search('')->expression('inc','in(ceil(attr*100),200,250,350)')->get();searchApi.search({"table":"products","query":{"match_all":{}}},"expressions":{"inc":"in(ceil(attr*100),200,250,350)"}})res = await searchApi.search({"table":"products","query":{"match_all":{}}},"expressions":{"inc":"in(ceil(attr*100),200,250,350)"}});searchRequest = new SearchRequest();
searchRequest.setIndex("forum");
query = new HashMap<String,Object>();
query.put("match_all",null);
searchRequest.setQuery(query);
Object expressions = new HashMap<String,Object>(){{
put("inc","in(ceil(attr*100),200,250,350)");
}};
searchRequest.setExpressions(expressions);
searchResponse = searchApi.search(searchRequest);object query = new { match_all=null };
var searchRequest = new SearchRequest("forum", query);
searchRequest.Expressions = new List<Object> {
new Dictionary<string, string> { {"ebs", "in(ceil(attr*100),200,250,350)"} }
};
var searchResponse = searchApi.Search(searchRequest);Float values in Manticore are displayed with precision to ensure they reflect the exact stored value. This approach was introduced to prevent precision loss, especially for cases like geographical coordinates, where rounding to 6 decimal places caused inaccuracies.
Now, Manticore first outputs a number with 6 digits, then parses and compares it to the original value. If they don't match, additional digits are added until they do.
For example, if a float value was inserted as 19.45, Manticore will display it as 19.450001 to accurately represent the stored value.
- Example
insert into t(id, f) values(1, 19.45)
--------------
Query OK, 1 row affected (0.02 sec)
--------------
select * from t
--------------
+------+-----------+
| id | f |
+------+-----------+
| 1 | 19.450001 |
+------+-----------+
1 row in set (0.00 sec)
--- 1 out of 1 results in 0ms ---This data type allows for the storage of JSON objects, which is particularly useful for handling schema-less data. When defining JSON values, ensure that the opening and closing curly braces { and } are included for objects, or square brackets [ and ] for arrays. While JSON is not supported by columnar storage, it can be stored in traditional row-wise storage. It's worth noting that both storage types can be combined within the same table.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
- config
CREATE TABLE products(title text, data json);POST /cli -d "CREATE TABLE products(title text, data json)"$index = new \Manticoresearch\Index($client);
$index->setName('products');
$index->create([
'title'=>['type'=>'text'],
'data'=>['type'=>'json']
]);utilsApi.sql('CREATE TABLE products(title text, data json)')res = await utilsApi.sql('CREATE TABLE products(title text, data json)');utilsApi.sql'CREATE TABLE products(title text, data json)');utilsApi.Sql'CREATE TABLE products(title text, data json)');table products
{
type = rt
path = products
rt_field = title
stored_fields = title
rt_attr_json = data
}JSON properties can be used in most operations. There are also special functions such as ALL(), ANY(), GREATEST(), LEAST() and INDEXOF() that allow traversal of property arrays.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
select indexof(x>2 for x in data.intarray) from productsPOST /search
{
"table": "products",
"query": { "match_all": {} } },
"expressions": { "idx": "indexof(x>2 for x in data.intarray)" }
}$index->setName('products')->search('')->expression('idx','indexof(x>2 for x in data.intarray)')->get();searchApi.search({"table":"products","query":{"match_all":{}},"expressions":{"idx":"indexof(x>2 for x in data.intarray)"}})res = await searchApi.search({"table":"products","query":{"match_all":{}},"expressions":{"idx":"indexof(x>2 for x in data.intarray)"}});searchRequest = new SearchRequest();
searchRequest.setIndex("forum");
query = new HashMap<String,Object>();
query.put("match_all",null);
searchRequest.setQuery(query);
Object expressions = new HashMap<String,Object>(){{
put("idx","indexof(x>2 for x in data.intarray)");
}};
searchRequest.setExpressions(expressions);
searchResponse = searchApi.search(searchRequest);object query = new { match_all=null };
var searchRequest = new SearchRequest("forum", query);
searchRequest.Expressions = new List<Object> {
new Dictionary<string, string> { {"idx", "indexof(x>2 for x in data.intarray)"} }
};
var searchResponse = searchApi.Search(searchRequest);Text properties are treated the same as strings, so it's not possible to use them in full-text match expressions. However, string functions such as REGEX() can be used.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
select regex(data.name, 'est') as c from products where c>0POST /search
{
"table": "products",
"query":
{
"match_all": {},
"range": { "c": { "gt": 0 } } }
},
"expressions": { "c": "regex(data.name, 'est')" }
}$index->setName('products')->search('')->expression('idx',"regex(data.name, 'est')")->filter('c','gt',0)->get();searchApi.search({"table":"products","query":{"match_all":{},"range":{"c":{"gt":0}}}},"expressions":{"c":"regex(data.name, 'est')"}})res = await searchApi.search({"table":"products","query":{"match_all":{},"range":{"c":{"gt":0}}}},"expressions":{"c":"regex(data.name, 'est')"}}});searchRequest = new SearchRequest();
searchRequest.setIndex("forum");
query = new HashMap<String,Object>();
query.put("match_all",null);
query.put("range", new HashMap<String,Object>(){{
put("c", new HashMap<String,Object>(){{
put("gt",0);
}});
}});
searchRequest.setQuery(query);
Object expressions = new HashMap<String,Object>(){{
put("idx","indexof(x>2 for x in data.intarray)");
}};
searchRequest.setExpressions(expressions);
searchResponse = searchApi.search(searchRequest);object query = new { match_all=null };
var searchRequest = new SearchRequest("forum", query);
var rangeFilter = new RangeFilter("c");
rangeFilter.Gt = 0;
searchRequest.AttrFilter = rangeFilter;
searchRequest.Expressions = new List<Object> {
new Dictionary<string, string> { {"idx", "indexof(x>2 for x in data.intarray)"} }
};
var searchResponse = searchApi.Search(searchRequest);In the case of JSON properties, enforcing data type may be required for proper functionality in certain situations. For example, when working with float values, DOUBLE() must be used for proper sorting.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
select * from products order by double(data.myfloat) descPOST /search
{
"table": "products",
"query": { "match_all": {} } },
"sort": [ { "double(data.myfloat)": { "order": "desc"} } ]
}$index->setName('products')->search('')->sort('double(data.myfloat)','desc')->get();searchApi.search({"table":"products","query":{"match_all":{}}},"sort":[{"double(data.myfloat)":{"order":"desc"}}]})res = await searchApi.search({"table":"products","query":{"match_all":{}}},"sort":[{"double(data.myfloat)":{"order":"desc"}}]});searchRequest = new SearchRequest();
searchRequest.setIndex("forum");
query = new HashMap<String,Object>();
query.put("match_all",null);
searchRequest.setQuery(query);
searchRequest.setSort(new ArrayList<Object>(){{
add(new HashMap<String,String>(){{ put("double(data.myfloat)",new HashMap<String,String>(){{ put("order","desc");}});}});
}});
searchResponse = searchApi.search(searchRequest);object query = new { match_all=null };
var searchRequest = new SearchRequest("forum", query);
searchRequest.Sort = new List<Object> {
new SortOrder("double(data.myfloat)", SortOrder.OrderEnum.Desc)
};
var searchResponse = searchApi.Search(searchRequest);Float vector attributes allow storing variable-length lists of floats, primarily used for machine learning applications and similarity searches. This type differs from multi-valued attributes (MVAs) in several important ways:
- Preserves the exact order of values (unlike MVAs which may reorder)
- Retains duplicate values (unlike MVAs which deduplicate)
- No additional processing during insertion (unlike MVAs which sort and deduplicate)
- Currently only supported in real-time tables
- Can only be utilized in KNN (k-nearest neighbor) searches
- Not supported in plain tables or other functions/expressions
- When used with KNN settings, you cannot
UPDATEfloat_vectorvalues. UseREPLACEinstead - When used without KNN settings, you can
UPDATEfloat_vectorvalues - Float vectors cannot be used in regular filters or sorting
- The only way to filter by
float_vectorvalues is through vector search operations (KNN)
- Image embeddings for similarity search
- Text embeddings for semantic search
- Feature vectors for machine learning
- Recommendation system vectors
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
- config
CREATE TABLE products(title text, image_vector float_vector);POST /cli -d "CREATE TABLE products(title text, image_vector float_vector)"$index = new \Manticoresearch\Index($client);
$index->setName('products');
$index->create([
'title'=>['type'=>'text'],
'image_vector'=>['type'=>'float_vector']
]);utilsApi.sql('CREATE TABLE products(title text, image_vector float_vector)')res = await utilsApi.sql('CREATE TABLE products(title text, image_vector float_vector)');utilsApi.sql("CREATE TABLE products(title text, image_vector float_vector)");utilsApi.Sql("CREATE TABLE products(title text, image_vector float_vector)");table products
{
type = rt
path = products
rt_field = title
stored_fields = title
rt_attr_float_vector = image_vector
}For information about using float vectors in searches, see KNN search.
Multi-value attributes allow storing variable-length lists of 32-bit unsigned integers. This can be useful for storing one-to-many numeric values, such as tags, product categories, and properties.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
- config
CREATE TABLE products(title text, product_codes multi);POST /cli -d "CREATE TABLE products(title text, product_codes multi)"$index = new \Manticoresearch\Index($client);
$index->setName('products');
$index->create([
'title'=>['type'=>'text'],
'product_codes'=>['type'=>'multi']
]);utilsApi.sql('CREATE TABLE products(title text, product_codes multi)')res = await utilsApi.sql('CREATE TABLE products(title text, product_codes multi)');utilsApi.sql("CREATE TABLE products(title text, product_codes multi)");utilsApi.Sql("CREATE TABLE products(title text, product_codes multi)");table products
{
type = rt
path = products
rt_field = title
stored_fields = title
rt_attr_multi = product_codes
}It supports filtering and aggregation, but not sorting. Filtering can be done using a condition that requires at least one element to pass (using ANY()) or all elements (ALL()) to pass.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
select * from products where any(product_codes)=3POST /search
{
"table": "products",
"query":
{
"match_all": {},
"equals" : { "any(product_codes)": 3 }
}
}$index->setName('products')->search('')->filter('any(product_codes)','equals',3)->get();searchApi.search({"table":"products","query":{"match_all":{},"equals":{"any(product_codes)":3}}}})res = await searchApi.search({"table":"products","query":{"match_all":{},"equals":{"any(product_codes)":3}}}})'searchRequest = new SearchRequest();
searchRequest.setIndex("forum");
query = new HashMap<String,Object>();
query.put("match_all",null);
query.put("equals",new HashMap<String,Integer>(){{
put("any(product_codes)",3);
}});
searchRequest.setQuery(query);
searchResponse = searchApi.search(searchRequest);object query = new { match_all=null };
var searchRequest = new SearchRequest("forum", query);
searchRequest.AttrFilter = new EqualsFilter("any(product_codes)", 3);
var searchResponse = searchApi.Search(searchRequest);Information like least or greatest element and length of the list can be extracted. An example shows ordering by the least element of a multi-value attribute.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
select least(product_codes) l from products order by l ascPOST /search
{
"table": "products",
"query":
{
"match_all": {},
"sort": [ { "product_codes":{ "order":"asc", "mode":"min" } } ]
}
}$index->setName('products')->search('')->sort('product_codes','asc','min')->get();searchApi.search({"table":"products","query":{"match_all":{},"sort":[{"product_codes":{"order":"asc","mode":"min"}}]}})res = await searchApi.search({"table":"products","query":{"match_all":{},"sort":[{"product_codes":{"order":"asc","mode":"min"}}]}});searchRequest = new SearchRequest();
searchRequest.setIndex("forum");
query = new HashMap<String,Object>();
query.put("match_all",null);
searchRequest.setQuery(query);
searchRequest.setSort(new ArrayList<Object>(){{
add(new HashMap<String,String>(){{ put("product_codes",new HashMap<String,String>(){{ put("order","asc");put("mode","min");}});}});
}});
searchResponse = searchApi.search(searchRequest);object query = new { match_all=null };
var searchRequest = new SearchRequest("forum", query);
searchRequest.Sort = new List<Object> {
new SortMVA("product_codes", SortOrder.OrderEnum.Asc, SortMVA.ModeEnum.Min)
};
searchResponse = searchApi.Search(searchRequest);When grouping by a multi-value attribute, a document will contribute to as many groups as there are different values associated with that document. For instance, if a collection contains exactly one document having a 'product_codes' multi-value attribute with values 5, 7, and 11, grouping on 'product_codes' will produce 3 groups with COUNT(*)equal to 1 and GROUPBY() key values of 5, 7, and 11, respectively. Also, note that grouping by multi-value attributes may lead to duplicate documents in the result set because each document can participate in many groups.
- SQL
insert into products values ( 1, 'doc one', (5,7,11) );
select id, count(*), groupby() from products group by product_codes;Query OK, 1 row affected (0.00 sec)
+------+----------+-----------+
| id | count(*) | groupby() |
+------+----------+-----------+
| 1 | 1 | 11 |
| 1 | 1 | 7 |
| 1 | 1 | 5 |
+------+----------+-----------+
3 rows in set (0.00 sec)The order of the numbers inserted as values of multivalued attributes is not preserved. Values are stored internally as a sorted set.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
insert into product values (1,'first',(4,2,1,3));
select * from products;POST /insert
{
"table":"products",
"id":1,
"doc":
{
"title":"first",
"product_codes":[4,2,1,3]
}
}
POST /search
{
"table": "products",
"query": { "match_all": {} }
}$index->addDocument([
"title"=>"first",
"product_codes"=>[4,2,1,3]
]);
$index->search('')-get();indexApi.insert({"table":"products","id":1,"doc":{"title":"first","product_codes":[4,2,1,3]}})
searchApi.search({"table":"products","query":{"match_all":{}}})await indexApi.insert({"table":"products","id":1,"doc":{"title":"first","product_codes":[4,2,1,3]}});
res = await searchApi.search({"table":"products","query":{"match_all":{}}});InsertDocumentRequest newdoc = new InsertDocumentRequest();
HashMap<String,Object> doc = new HashMap<String,Object>(){{
put("title","first");
put("product_codes",new int[] {4,2,1,3});
}};
newdoc.index("products").id(1L).setDoc(doc);
sqlresult = indexApi.insert(newdoc);
Map<String,Object> query = new HashMap<String,Object>();
query.put("match_all",null);
SearchRequest searchRequest = new SearchRequest();
searchRequest.setIndex("products");
searchRequest.setQuery(query);
SearchResponse searchResponse = searchApi.search(searchRequest);
System.out.println(searchResponse.toString() );Dictionary<string, Object> doc = new Dictionary<string, Object>();
doc.Add("title", "first");
doc.Add("product_codes", new List<Object> {4,2,1,3});
InsertDocumentRequest newdoc = new InsertDocumentRequest(index: "products", id: 1, doc: doc);
var sqlresult = indexApi.Insert(newdoc);
object query = new { match_all=null };
var searchRequest = new SearchRequest("products", query);
var searchResponse = searchApi.Search(searchRequest);
Console.WriteLine(searchResponse.ToString())Query OK, 1 row affected (0.00 sec)
+------+---------------+-------+
| id | product_codes | title |
+------+---------------+-------+
| 1 | 1,2,3,4 | first |
+------+---------------+-------+
1 row in set (0.01 sec){
"table":"products",
"_id":1,
"created":true,
"result":"created",
"status":201
}
{
"took":0,
"timed_out":false,
"hits":{
"total":1,
"hits":[
{
"_id": 1,
"_score":1,
"_source":{
"product_codes":[
1,
2,
3,
4
],
"title":"first"
}
}
]
}
}Array
(
[_index] => products
[_id] => 1
[created] => 1
[result] => created
[status] => 201
)
Array
(
[took] => 0
[timed_out] =>
[hits] => Array
(
[total] => 1
[hits] => Array
(
[0] => Array
(
[_id] => 1
[_score] => 1
[_source] => Array
(
[product_codes] => Array
(
[0] => 1
[1] => 2
[2] => 3
[3] => 4
)
[title] => first
)
)
)
)
){'created': True,
'found': None,
'id': 1,
'table': 'products',
'result': 'created'}
{'hits': {'hits': [{u'_id': u'1',
u'_score': 1,
u'_source': {u'product_codes': [1, 2, 3, 4],
u'title': u'first'}}],
'total': 1},
'profile': None,
'timed_out': False,
'took': 29}{"took":0,"timed_out":false,"hits":{"total":1,"hits":[{"_id": 1,"_score":1,"_source":{"product_codes":[1,2,3,4],"title":"first"}}]}}class SearchResponse {
took: 0
timedOut: false
hits: class SearchResponseHits {
total: 1
hits: [{_id=1, _score=1, _source={product_codes=[1, 2, 3, 4], title=first}}]
aggregations: null
}
profile: null
}
class SearchResponse {
took: 0
timedOut: false
hits: class SearchResponseHits {
total: 1
hits: [{_id=1, _score=1, _source={product_codes=[1, 2, 3, 4], title=first}}]
aggregations: null
}
profile: null
}
A data type that allows storing variable-length lists of 64-bit signed integers. It has the same functionality as multi-value integer.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
- config
CREATE TABLE products(title text, values multi64);POST /cli -d "CREATE TABLE products(title text, values multi64)"$index = new \Manticoresearch\Index($client);
$index->setName('products');
$index->create([
'title'=>['type'=>'text'],
'values'=>['type'=>'multi64']
]);utilsApi.sql('CREATE TABLE products(title text, values multi64))')res = await utilsApi.sql('CREATE TABLE products(title text, values multi64))');utilsApi.sql("CREATE TABLE products(title text, values multi64))");utilsApi.Sql("CREATE TABLE products(title text, values multi64))");table products
{
type = rt
path = products
rt_field = title
stored_fields = title
rt_attr_multi_64 = values
}When you use the columnar storage you can specify the following properties for the attributes.
By default, Manticore Columnar storage stores all attributes in a columnar fashion, as well as in a special docstore row by row. This enables fast execution of queries like SELECT * FROM ..., especially when fetching a large number of records at once. However, if you are sure that you do not need it or wish to save disk space, you can disable it by specifying fast_fetch='0' when creating a table or (if you are defining a table in a config) by using columnar_no_fast_fetch as shown in the following example.
- RT mode
- Plain mode
create table t(a int, b int fast_fetch='0') engine='columnar'; desc t;source min {
type = mysql
sql_host = localhost
sql_user = test
sql_pass =
sql_db = test
sql_query = select 1, 1 a, 1 b
sql_attr_uint = a
sql_attr_uint = b
}
table tbl {
path = tbl/col
source = min
columnar_attrs = *
columnar_no_fast_fetch = b
}+-------+--------+---------------------+
| Field | Type | Properties |
+-------+--------+---------------------+
| id | bigint | columnar fast_fetch |
| a | uint | columnar fast_fetch |
| b | uint | columnar |
+-------+--------+---------------------+
3 rows in set (0.00 sec)+-------+--------+---------------------+
| Field | Type | Properties |
+-------+--------+---------------------+
| id | bigint | columnar fast_fetch |
| a | uint | columnar fast_fetch |
| b | uint | columnar |
+-------+--------+---------------------+Manticore's data types can be split into two categories: full-text fields and attributes.
Field names in Manticore must follow these rules:
- Can contain letters (a-z, A-Z), numbers (0-9), and hyphens (-)
- Must start with a letter
- Numbers can only appear after letters
- Underscore (
_) is the only allowed special character - Field names are case-insensitive
For example:
- Valid field names:
title,product_id,user_name_2 - Invalid field names:
2title,-price,user@name
Full-text fields:
- can be indexed with natural language processing algorithms, therefore can be searched for keywords
- cannot be used for sorting or grouping
- original document's content can be retrieved
- original document's content can be used for highlighting
Full-text fields are represented by the data type text. All other data types are called "attributes".
Attributes are non-full-text values associated with each document that can be used to perform non-full-text filtering, sorting and grouping during a search.
It is often desired to process full-text search results based not only on matching document ID and its rank, but also on a number of other per-document values. For example, one might need to sort news search results by date and then relevance, or search through products within a specified price range, or limit a blog search to posts made by selected users, or group results by month. To do this efficiently, Manticore enables not only full-text fields, but also additional attributes to be added to each document. These attributes can be used to filter, sort, or group full-text matches, or to search only by attributes.
The attributes, unlike full-text fields, are not full-text indexed. They are stored in the table, but it is not possible to search them as full-text.
A good example for attributes would be a forum posts table. Assume that only the title and content fields need to be full-text searchable - but that sometimes it is also required to limit search to a certain author or a sub-forum (i.e., search only those rows that have some specific values of author_id or forum_id); or to sort matches by post_date column; or to group matching posts by month of the post_date and calculate per-group match counts.
- SQL
- JSON
- PHP
- Python
- Javascript
- Java
- C#
- config
CREATE TABLE forum(title text, content text, author_id int, forum_id int, post_date timestamp);POST /cli -d "CREATE TABLE forum(title text, content text, author_id int, forum_id int, post_date timestamp)"$index = new \Manticoresearch\Index($client);
$index->setName('forum');
$index->create([
'title'=>['type'=>'text'],
'content'=>['type'=>'text'],
'author_id'=>['type'=>'int'],
'forum_id'=>['type'=>'int'],
'post_date'=>['type'=>'timestamp']
]);utilsApi.sql('CREATE TABLE forum(title text, content text, author_id int, forum_id int, post_date timestamp)')res = await utilsApi.sql('CREATE TABLE forum(title text, content text, author_id int, forum_id int, post_date timestamp)');utilsApi.sql("CREATE TABLE forum(title text, content text, author_id int, forum_id int, post_date timestamp)");
utilsApi.Sql("CREATE TABLE forum(title text, content text, author_id int, forum_id int, post_date timestamp)");
table forum
{
type = rt
path = forum
# when configuring fields via config, they are indexed (and not stored) by default
rt_field = title
rt_field = content
# this option needs to be specified for the field to be stored
stored_fields = title, content
rt_attr_uint = author_id
rt_attr_uint = forum_id
rt_attr_timestamp = post_date
}This example shows running a full-text query filtered by author_id, forum_id and sorted by post_date.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
select * from forum where author_id=123 and forum_id in (1,3,7) order by post_date descPOST /search
{
"table": "forum",
"query":
{
"match_all": {},
"bool":
{
"must":
[
{ "equals": { "author_id": 123 } },
{ "in": { "forum_id": [1,3,7] } }
]
}
},
"sort": [ { "post_date": "desc" } ]
}$client->search([
'table' => 'forum',
'query' =>
[
'match_all' => [],
'bool' => [
'must' => [
'equals' => ['author_id' => 123],
'in' => [
'forum_id' => [
1,3,7
]
]
]
]
],
'sort' => [
['post_date' => 'desc']
]
]);searchApi.search({"table":"forum","query":{"match_all":{},"bool":{"must":[{"equals":{"author_id":123}},{"in":{"forum_id":[1,3,7]}}]}},"sort":[{"post_date":"desc"}]})res = await searchApi.search({"table":"forum","query":{"match_all":{},"bool":{"must":[{"equals":{"author_id":123}},{"in":{"forum_id":[1,3,7]}}]}},"sort":[{"post_date":"desc"}]});HashMap<String,Object> filters = new HashMap<String,Object>(){{
put("must", new HashMap<String,Object>(){{
put("equals",new HashMap<String,Integer>(){{
put("author_id",123);
}});
put("in",
new HashMap<String,Object>(){{
put("forum_id",new int[] {1,3,7});
}});
}});
}};
Map<String,Object> query = new HashMap<String,Object>();
query.put("match_all",null);
query.put("bool",filters);
SearchRequest searchRequest = new SearchRequest();
searchRequest.setIndex("forum");
searchRequest.setQuery(query);
searchRequest.setSort(new ArrayList<Object>(){{
add(new HashMap<String,String>(){{ put("post_date","desc");}});
}});
SearchResponse searchResponse = searchApi.search(searchRequest);object query = new { match_all=null };
var searchRequest = new SearchRequest("forum", query);
var boolFilter = new BoolFilter();
boolFilter.Must = new List<Object> {
new EqualsFilter("author_id", 123),
new InFilter("forum_id", new List<Object> {1,3,7})
};
searchRequest.AttrFilter = boolFilter;
searchRequest.Sort = new List<Object> { new SortOrder("post_date", SortOrder.OrderEnum.Desc) };
var searchResponse = searchApi.Search(searchRequest);Manticore supports two types of attribute storages:
- row-wise - traditional storage available in Manticore Search out of the box
- columnar - provided by Manticore Columnar Library
As can be understood from their names, they store data differently. The traditional row-wise storage:
- stores attributes uncompressed
- all attributes of the same document are stored in one row close to each other
- rows are stored one by one
- accessing attributes is basically done by just multiplying the row ID by the stride (length of a single vector) and getting the requested attribute from the calculated memory location. It gives very low random access latency.
- attributes have to be in memory to get acceptable performance, otherwise due to the row-wise nature of the storage Manticore may have to read from disk too much unneeded data which is in many cases suboptimal.
With the columnar storage:
- each attribute is stored independently of all other attributes in its separate "column"
- storage is split into blocks of 65536 entries
- the blocks are stored compressed. This often allows storing just a few distinct values instead of storing all of them like in the row-wise storage. High compression ratio allows reading from disk faster and makes the memory requirement much lower
- when data is indexed, storage scheme is selected for each block independently. For example, if all values in a block are the same, it gets "const" storage and only one value is stored for the whole block. If there are less than 256 unique values per block, it gets "table" storage and stores indexes to a table of values instead of the values themselves
- search in a block can be early rejected if it's clear the requested value is not present in the block.
The columnar storage was designed to handle large data volume that does not fit into RAM, so the recommendations are:
- if you have enough memory for all your attributes you will benefit from the row-wise storage
- otherwise, the columnar storage can still give you decent performance with a much lower memory footprint which will allow you to store much more documents in your table
The traditional row-wise storage is the default, so if you want everything to be stored in a row-wise fashion, you don't need to do anything when you create a table.
To enable the columnar storage you need to:
- specify
engine='columnar'in CREATE TABLE to make all attributes of the table columnar. Then, if you want to keep a specific attribute row-wise, you need to addengine='rowwise'when you declare it. For example:create table tbl(title text, type int, price float engine='rowwise') engine='columnar' - specify
engine='columnar'for a specific attribute inCREATE TABLEto make it columnar. For example:create table tbl(title text, type int, price float engine='columnar');or
create table tbl(title text, type int, price float engine='columnar') engine='rowwise'; - in the plain mode you need to list attributes you want to be columnar in columnar_attrs.
Below is the list of data types supported by Manticore Search:
The document identifier is a mandatory attribute that must be a unique 64-bit unsigned integer. Document IDs can be explicitly specified when creating a table, but they are always enabled even if not specified. Document IDs cannot be updated.
When you create a table, you can specify ID explicitly, but regardless of the data type you use, it will always behave as described above - stored as unsigned 64-bit but exposed as signed 64-bit integer.
mysql> CREATE TABLE tbl(id bigint, content text);
DESC tbl;
+---------+--------+----------------+
| Field | Type | Properties |
+---------+--------+----------------+
| id | bigint | |
| content | text | indexed stored |
+---------+--------+----------------+
2 rows in set (0.00 sec)You can also omit specifying ID at all, it will be enabled automatically.
mysql> CREATE TABLE tbl(content text);
DESC tbl;
+---------+--------+----------------+
| Field | Type | Properties |
+---------+--------+----------------+
| id | bigint | |
| content | text | indexed stored |
+---------+--------+----------------+
2 rows in set (0.00 sec) When working with document IDs, it's important to know that they are stored internally as unsigned 64-bit integers but are exposed as signed 64-bit integers in queries and results. This means:
- IDs greater than 2^63-1 will appear as negative numbers.
- When filtering by such large IDs, you must use their signed representation.
- Use the UINT64() function to view the actual unsigned value.
For example, let's create a table and insert some values around 2^63:
mysql> create table t(id_text string)
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t values(9223372036854775807, '2 ^ 63 - 1'),(9223372036854775808, '2 ^ 63')
Query OK, 2 rows affected (0.00 sec)Some IDs appear as negative numbers in the results because they exceed 2^63-1. However, using UINT64(id) can reveal their actual unsigned values:
mysql> select *, uint64(id) from t
+----------------------+------------+---------------------+
| id | id_text | uint64(id) |
+----------------------+------------+---------------------+
| 9223372036854775807 | 2 ^ 63 - 1 | 9223372036854775807 |
| -9223372036854775808 | 2 ^ 63 | 9223372036854775808 |
+----------------------+------------+---------------------+
2 rows in set (0.00 sec)
--- 2 out of 2 results in 0ms ---For querying documents with IDs less than 2^63, you can use the unsigned value directly:
mysql> select * from t where id = 9223372036854775807
+---------------------+------------+
| id | id_text |
+---------------------+------------+
| 9223372036854775807 | 2 ^ 63 - 1 |
+---------------------+------------+
1 row in set (0.00 sec)
--- 1 out of 1 results in 0ms ---However, for IDs starting from 2^63, you need to use the signed value:
mysql> select * from t where id = -9223372036854775808
+----------------------+---------+
| id | id_text |
+----------------------+---------+
| -9223372036854775808 | 2 ^ 63 |
+----------------------+---------+
1 row in set (0.00 sec)
--- 1 out of 1 results in 0ms ---If you use an unsigned value instead, you might get incorrect results:
mysql> select * from t where id = 9223372036854775808
+---------------------+------------+
| id | id_text |
+---------------------+------------+
| 9223372036854775807 | 2 ^ 63 - 1 |
+---------------------+------------+
1 row in set (0.00 sec)
--- 1 out of 1 results in 0ms ---General syntax:
string|text [stored|attribute] [indexed]Properties:
indexed- full-text indexed (can be used in full-text queries)stored- stored in a docstore (stored on disk, not in RAM, lazy read)attribute- makes it a string attribute (can sort/group by it)
Specifying at least one property overrides all the default ones (see below), i.e., if you decide to use a custom combination of properties, you need to list all the properties you want.
No properties specified:
string and text are aliases, but if you don't specify any properties, they by default mean different things:
- just
stringby default meansattribute(see details below). - just
textby default meansstored+indexed(see details below).
The text (just text or text/string indexed) data type forms the full-text part of the table. Text fields are indexed and can be searched for keywords.
Text is passed through an analyzer pipeline that converts the text to words, applies morphology transformations, etc. Eventually, a full-text table (a special data structure that enables quick searches for a keyword) gets built from that text.
Full-text fields can only be used in the MATCH() clause and cannot be used for sorting or aggregation. Words are stored in an inverted index along with references to the fields they belong to and positions in the field. This allows searching a word inside each field and using advanced operators like proximity. By default, the original text of the fields is both indexed and stored in document storage. It means that the original text can be returned with the query results and used in search result highlighting.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
- config
CREATE TABLE products(title text);POST /cli -d "CREATE TABLE products(title text)"$index = new \Manticoresearch\Index($client);
$index->setName('products');
$index->create([
'title'=>['type'=>'text']
]);utilsApi.sql('CREATE TABLE products(title text)')res = await utilsApi.sql('CREATE TABLE products(title text)');utilsApi.sql("CREATE TABLE products(title text)");utilsApi.Sql("CREATE TABLE products(title text)");table products
{
type = rt
path = products
# when configuring fields via config, they are indexed (and not stored) by default
rt_field = title
# this option needs to be specified for the field to be stored
stored_fields = title
}This behavior can be overridden by explicitly specifying that the text is only indexed.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
- config
CREATE TABLE products(title text indexed);POST /cli -d "CREATE TABLE products(title text indexed)"$index = new \Manticoresearch\Index($client);
$index->setName('products');
$index->create([
'title'=>['type'=>'text','options'=>['indexed']]
]);utilsApi.sql('CREATE TABLE products(title text indexed)')res = await utilsApi.sql('CREATE TABLE products(title text indexed)');utilsApi.sql("CREATE TABLE products(title text indexed)");utilsApi.Sql("CREATE TABLE products(title text indexed)");table products
{
type = rt
path = products
# when configuring fields via config, they are indexed (and not stored) by default
rt_field = title
}Fields are named, and you can limit your searches to a single field (e.g. search through "title" only) or a subset of fields (e.g. "title" and "abstract" only). You can have up to 256 full-text fields.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
select * from products where match('@title first');POST /search
{
"table": "products",
"query":
{
"match": { "title": "first" }
}
}$index->setName('products')->search('@title')->get();
searchApi.search({"table":"products","query":{"match":{"title":"first"}}})res = await searchApi.search({"table":"products","query":{"match":{"title":"first"}}});utilsApi.sql("CREATE TABLE products(title text indexed)");utilsApi.Sql("CREATE TABLE products(title text indexed)");Unlike full-text fields, string attributes (just string or string/text attribute) are stored as they are received and cannot be used in full-text searches. Instead, they are returned in results, can be used in the WHERE clause for comparison filtering or REGEX, and can be used for sorting and aggregation. In general, it's not recommended to store large texts in string attributes, but use string attributes for metadata like names, titles, tags, keys.
If you want to also index the string attribute, you can specify both as string attribute indexed. It will allow full-text searching and works as an attribute.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
- config
CREATE TABLE products(title text, keys string);POST /cli -d "CREATE TABLE products(title text, keys string)"$index = new \Manticoresearch\Index($client);
$index->setName('products');
$index->create([
'title'=>['type'=>'text'],
'keys'=>['type'=>'string']
]);utilsApi.sql('CREATE TABLE products(title text, keys string)')res = await utilsApi.sql('CREATE TABLE products(title text, keys string)');utilsApi.sql("CREATE TABLE products(title text, keys string)");utilsApi.Sql("CREATE TABLE products(title text, keys string)");table products
{
type = rt
path = products
rt_field = title
stored_fields = title
rt_attr_string = keys
}Manticore doesn't have a dedicated field type for binary data, but you can store it safely by using base64 encoding and the text stored or string stored field types (which are synonyms). If you don't encode the binary data, parts of it may get lost — for example, Manticore trims the end of a string if it encounters a null-byte.
Here is an example where we encode the ls command using base64, store it in Manticore, and then decode it to verify that the MD5 checksum remains unchanged:
- Example
# md5sum /bin/ls
43d1b8a7ccda411118e2caba685f4329 /bin/ls
# encoded_data=`base64 -i /bin/ls `
# mysql -P9306 -h0 -e "drop table if exists test; create table test(data text stored); insert into test(data) values('$encoded_data')"
# mysql -P9306 -h0 -NB -e "select data from test" | base64 -d > /tmp/ls | md5sum
43d1b8a7ccda411118e2caba685f4329 -Integer type allows storing 32 bit unsigned integer values.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
- config
CREATE TABLE products(title text, price int);POST /cli -d "CREATE TABLE products(title text, price int)"$index = new \Manticoresearch\Index($client);
$index->setName('products');
$index->create([
'title'=>['type'=>'text'],
'price'=>['type'=>'int']
]);utilsApi.sql('CREATE TABLE products(title text, price int)')res = await utilsApi.sql('CREATE TABLE products(title text, price int)');utilsApi.sql("CREATE TABLE products(title text, price int)");utilsApi.Sql("CREATE TABLE products(title text, price int)");table products
{
type = rt
path = products
rt_field = title
stored_fields = title
rt_attr_uint = type
}Integers can be stored in shorter sizes than 32-bit by specifying a bit count. For example, if we want to store a numeric value which we know is not going to be bigger than 8, the type can be defined as bit(3). Bitcount integers perform slower than the full-size ones, but they require less RAM. They are saved in 32-bit chunks, so in order to save space, they should be grouped at the end of attribute definitions (otherwise a bitcount integer between 2 full-size integers will occupy 32 bits as well).
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
- config
CREATE TABLE products(title text, flags bit(3), tags bit(2) );POST /cli -d "CREATE TABLE products(title text, flags bit(3), tags bit(2))"$index = new \Manticoresearch\Index($client);
$index->setName('products');
$index->create([
'title'=>['type'=>'text'],
'flags'=>['type'=>'bit(3)'],
'tags'=>['type'=>'bit(2)']
]);utilsApi.sql('CREATE TABLE products(title text, flags bit(3), tags bit(2) ')res = await utilsApi.sql('CREATE TABLE products(title text, flags bit(3), tags bit(2) ');utilsApi.sql("CREATE TABLE products(title text, flags bit(3), tags bit(2)");utilsApi.Sql("CREATE TABLE products(title text, flags bit(3), tags bit(2)");table products
{
type = rt
path = products
rt_field = title
stored_fields = title
rt_attr_uint = flags:3
rt_attr_uint = tags:2
}Big integers (bigint) are 64-bit wide signed integers.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
- config
CREATE TABLE products(title text, price bigint );POST /cli -d "CREATE TABLE products(title text, price bigint)"$index = new \Manticoresearch\Index($client);
$index->setName('products');
$index->create([
'title'=>['type'=>'text'],
'price'=>['type'=>'bigint']
]);utilsApi.sql('CREATE TABLE products(title text, price bigint )')res = await utilsApi.sql('CREATE TABLE products(title text, price bigint )');utilsApi.sql("CREATE TABLE products(title text, price bigint )");utilsApi.Sql("CREATE TABLE products(title text, price bigint )");table products
{
type = rt
path = products
rt_field = title
stored_fields = title
rt_attr_bigint = type
}Declares a boolean attribute. It's equivalent to an integer attribute with bit count of 1.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
- config
CREATE TABLE products(title text, sold bool );POST /cli -d "CREATE TABLE products(title text, sold bool)"$index = new \Manticoresearch\Index($client);
$index->setName('products');
$index->create([
'title'=>['type'=>'text'],
'sold'=>['type'=>'bool']
]);utilsApi.sql('CREATE TABLE products(title text, sold bool )')res = await utilsApi.sql('CREATE TABLE products(title text, sold bool )');utilsApi.sql("CREATE TABLE products(title text, sold bool )");utilsApi.Sql("CREATE TABLE products(title text, sold bool )");table products
{
type = rt
path = products
rt_field = title
stored_fields = title
rt_attr_bool = sold
}The timestamp type represents Unix timestamps, which are stored as 32-bit integers. Unlike basic integers, the timestamp type allows the use of time and date functions. Conversion from string values follows these rules:
- Numbers without delimiters, at least 10 characters long, are converted to timestamps as is.
%Y-%m-%dT%H:%M:%E*S%Z%Y-%m-%d'T'%H:%M:%S%Z%Y-%m-%dT%H:%M:%E*S%Y-%m-%dT%H:%M:%s%Y-%m-%dT%H:%M%Y-%m-%dT%H%Y-%m-%d%Y-%m%Y
The meanings of these conversion specifiers are detailed in the strptime manual, except for %E*S, which stands for milliseconds.
Note that auto-conversion of timestamps is not supported in plain tables.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
- config
CREATE TABLE products(title text, date timestamp);POST /cli -d "CREATE TABLE products(title text, date timestamp)"$index = new \Manticoresearch\Index($client);
$index->setName('products');
$index->create([
'title'=>['type'=>'text'],
'date'=>['type'=>'timestamp']
]);utilsApi.sql('CREATE TABLE products(title text, date timestamp)')res = await utilsApi.sql('CREATE TABLE products(title text, date timestamp)');utilsApi.sql("CREATE TABLE products(title text, date timestamp)");utilsApi.Sql("CREATE TABLE products(title text, date timestamp)");table products
{
type = rt
path = products
rt_field = title
stored_fields = title
rt_attr_timestamp = date
}Real numbers are stored as 32-bit IEEE 754 single precision floats.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
- config
CREATE TABLE products(title text, coeff float);POST /cli -d "CREATE TABLE products(title text, coeff float)"$index = new \Manticoresearch\Index($client);
$index->setName('products');
$index->create([
'title'=>['type'=>'text'],
'coeff'=>['type'=>'float']
]);utilsApi.sql('CREATE TABLE products(title text, coeff float)')res = await utilsApi.sql('CREATE TABLE products(title text, coeff float)');utilsApi.sql("CREATE TABLE products(title text, coeff float)");utilsApi.Sql("CREATE TABLE products(title text, coeff float)");table products
{
type = rt
path = products
rt_field = title
stored_fields = title
rt_attr_float = coeff
}Unlike integer types, comparing two floating-point numbers for equality is not recommended due to potential rounding errors. A more reliable approach is to use a near-equal comparison, by checking the absolute error margin.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
select abs(a-b)<=0.00001 from productsPOST /search
{
"table": "products",
"query": { "match_all": {} } },
"expressions": { "eps": "abs(a-b)" }
}$index->setName('products')->search('')->expression('eps','abs(a-b)')->get();searchApi.search({"table":"products","query":{"match_all":{}},"expressions":{"eps":"abs(a-b)"}})res = await searchApi.search({"table":"products","query":{"match_all":{}},"expressions":{"eps":"abs(a-b)"}});searchRequest = new SearchRequest();
searchRequest.setIndex("forum");
query = new HashMap<String,Object>();
query.put("match_all",null);
searchRequest.setQuery(query);
Object expressions = new HashMap<String,Object>(){{
put("ebs","abs(a-b)");
}};
searchRequest.setExpressions(expressions);
searchResponse = searchApi.search(searchRequest);object query = new { match_all=null };
var searchRequest = new SearchRequest("forum", query);
searchRequest.Expressions = new List<Object>{
new Dictionary<string, string> { {"ebs", "abs(a-b)"} }
};
var searchResponse = searchApi.Search(searchRequest);Another alternative, which can also be used to perform IN(attr,val1,val2,val3) is to compare floats as integers by choosing a multiplier factor and convert the floats to integers in operations. The following example illustrates modifying IN(attr,2.0,2.5,3.5) to work with integer values.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
select in(ceil(attr*100),200,250,350) from productsPOST /search
{
"table": "products",
"query": { "match_all": {} } },
"expressions": { "inc": "in(ceil(attr*100),200,250,350)" }
}$index->setName('products')->search('')->expression('inc','in(ceil(attr*100),200,250,350)')->get();searchApi.search({"table":"products","query":{"match_all":{}}},"expressions":{"inc":"in(ceil(attr*100),200,250,350)"}})res = await searchApi.search({"table":"products","query":{"match_all":{}}},"expressions":{"inc":"in(ceil(attr*100),200,250,350)"}});searchRequest = new SearchRequest();
searchRequest.setIndex("forum");
query = new HashMap<String,Object>();
query.put("match_all",null);
searchRequest.setQuery(query);
Object expressions = new HashMap<String,Object>(){{
put("inc","in(ceil(attr*100),200,250,350)");
}};
searchRequest.setExpressions(expressions);
searchResponse = searchApi.search(searchRequest);object query = new { match_all=null };
var searchRequest = new SearchRequest("forum", query);
searchRequest.Expressions = new List<Object> {
new Dictionary<string, string> { {"ebs", "in(ceil(attr*100),200,250,350)"} }
};
var searchResponse = searchApi.Search(searchRequest);Float values in Manticore are displayed with precision to ensure they reflect the exact stored value. This approach was introduced to prevent precision loss, especially for cases like geographical coordinates, where rounding to 6 decimal places caused inaccuracies.
Now, Manticore first outputs a number with 6 digits, then parses and compares it to the original value. If they don't match, additional digits are added until they do.
For example, if a float value was inserted as 19.45, Manticore will display it as 19.450001 to accurately represent the stored value.
- Example
insert into t(id, f) values(1, 19.45)
--------------
Query OK, 1 row affected (0.02 sec)
--------------
select * from t
--------------
+------+-----------+
| id | f |
+------+-----------+
| 1 | 19.450001 |
+------+-----------+
1 row in set (0.00 sec)
--- 1 out of 1 results in 0ms ---This data type allows for the storage of JSON objects, which is particularly useful for handling schema-less data. When defining JSON values, ensure that the opening and closing curly braces { and } are included for objects, or square brackets [ and ] for arrays. While JSON is not supported by columnar storage, it can be stored in traditional row-wise storage. It's worth noting that both storage types can be combined within the same table.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
- config
CREATE TABLE products(title text, data json);POST /cli -d "CREATE TABLE products(title text, data json)"$index = new \Manticoresearch\Index($client);
$index->setName('products');
$index->create([
'title'=>['type'=>'text'],
'data'=>['type'=>'json']
]);utilsApi.sql('CREATE TABLE products(title text, data json)')res = await utilsApi.sql('CREATE TABLE products(title text, data json)');utilsApi.sql'CREATE TABLE products(title text, data json)');utilsApi.Sql'CREATE TABLE products(title text, data json)');table products
{
type = rt
path = products
rt_field = title
stored_fields = title
rt_attr_json = data
}JSON properties can be used in most operations. There are also special functions such as ALL(), ANY(), GREATEST(), LEAST() and INDEXOF() that allow traversal of property arrays.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
select indexof(x>2 for x in data.intarray) from productsPOST /search
{
"table": "products",
"query": { "match_all": {} } },
"expressions": { "idx": "indexof(x>2 for x in data.intarray)" }
}$index->setName('products')->search('')->expression('idx','indexof(x>2 for x in data.intarray)')->get();searchApi.search({"table":"products","query":{"match_all":{}},"expressions":{"idx":"indexof(x>2 for x in data.intarray)"}})res = await searchApi.search({"table":"products","query":{"match_all":{}},"expressions":{"idx":"indexof(x>2 for x in data.intarray)"}});searchRequest = new SearchRequest();
searchRequest.setIndex("forum");
query = new HashMap<String,Object>();
query.put("match_all",null);
searchRequest.setQuery(query);
Object expressions = new HashMap<String,Object>(){{
put("idx","indexof(x>2 for x in data.intarray)");
}};
searchRequest.setExpressions(expressions);
searchResponse = searchApi.search(searchRequest);object query = new { match_all=null };
var searchRequest = new SearchRequest("forum", query);
searchRequest.Expressions = new List<Object> {
new Dictionary<string, string> { {"idx", "indexof(x>2 for x in data.intarray)"} }
};
var searchResponse = searchApi.Search(searchRequest);Text properties are treated the same as strings, so it's not possible to use them in full-text match expressions. However, string functions such as REGEX() can be used.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
select regex(data.name, 'est') as c from products where c>0POST /search
{
"table": "products",
"query":
{
"match_all": {},
"range": { "c": { "gt": 0 } } }
},
"expressions": { "c": "regex(data.name, 'est')" }
}$index->setName('products')->search('')->expression('idx',"regex(data.name, 'est')")->filter('c','gt',0)->get();searchApi.search({"table":"products","query":{"match_all":{},"range":{"c":{"gt":0}}}},"expressions":{"c":"regex(data.name, 'est')"}})res = await searchApi.search({"table":"products","query":{"match_all":{},"range":{"c":{"gt":0}}}},"expressions":{"c":"regex(data.name, 'est')"}}});searchRequest = new SearchRequest();
searchRequest.setIndex("forum");
query = new HashMap<String,Object>();
query.put("match_all",null);
query.put("range", new HashMap<String,Object>(){{
put("c", new HashMap<String,Object>(){{
put("gt",0);
}});
}});
searchRequest.setQuery(query);
Object expressions = new HashMap<String,Object>(){{
put("idx","indexof(x>2 for x in data.intarray)");
}};
searchRequest.setExpressions(expressions);
searchResponse = searchApi.search(searchRequest);object query = new { match_all=null };
var searchRequest = new SearchRequest("forum", query);
var rangeFilter = new RangeFilter("c");
rangeFilter.Gt = 0;
searchRequest.AttrFilter = rangeFilter;
searchRequest.Expressions = new List<Object> {
new Dictionary<string, string> { {"idx", "indexof(x>2 for x in data.intarray)"} }
};
var searchResponse = searchApi.Search(searchRequest);In the case of JSON properties, enforcing data type may be required for proper functionality in certain situations. For example, when working with float values, DOUBLE() must be used for proper sorting.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
select * from products order by double(data.myfloat) descPOST /search
{
"table": "products",
"query": { "match_all": {} } },
"sort": [ { "double(data.myfloat)": { "order": "desc"} } ]
}$index->setName('products')->search('')->sort('double(data.myfloat)','desc')->get();searchApi.search({"table":"products","query":{"match_all":{}}},"sort":[{"double(data.myfloat)":{"order":"desc"}}]})res = await searchApi.search({"table":"products","query":{"match_all":{}}},"sort":[{"double(data.myfloat)":{"order":"desc"}}]});searchRequest = new SearchRequest();
searchRequest.setIndex("forum");
query = new HashMap<String,Object>();
query.put("match_all",null);
searchRequest.setQuery(query);
searchRequest.setSort(new ArrayList<Object>(){{
add(new HashMap<String,String>(){{ put("double(data.myfloat)",new HashMap<String,String>(){{ put("order","desc");}});}});
}});
searchResponse = searchApi.search(searchRequest);object query = new { match_all=null };
var searchRequest = new SearchRequest("forum", query);
searchRequest.Sort = new List<Object> {
new SortOrder("double(data.myfloat)", SortOrder.OrderEnum.Desc)
};
var searchResponse = searchApi.Search(searchRequest);Float vector attributes allow storing variable-length lists of floats, primarily used for machine learning applications and similarity searches. This type differs from multi-valued attributes (MVAs) in several important ways:
- Preserves the exact order of values (unlike MVAs which may reorder)
- Retains duplicate values (unlike MVAs which deduplicate)
- No additional processing during insertion (unlike MVAs which sort and deduplicate)
- Currently only supported in real-time tables
- Can only be utilized in KNN (k-nearest neighbor) searches
- Not supported in plain tables or other functions/expressions
- When used with KNN settings, you cannot
UPDATEfloat_vectorvalues. UseREPLACEinstead - When used without KNN settings, you can
UPDATEfloat_vectorvalues - Float vectors cannot be used in regular filters or sorting
- The only way to filter by
float_vectorvalues is through vector search operations (KNN)
- Image embeddings for similarity search
- Text embeddings for semantic search
- Feature vectors for machine learning
- Recommendation system vectors
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
- config
CREATE TABLE products(title text, image_vector float_vector);POST /cli -d "CREATE TABLE products(title text, image_vector float_vector)"$index = new \Manticoresearch\Index($client);
$index->setName('products');
$index->create([
'title'=>['type'=>'text'],
'image_vector'=>['type'=>'float_vector']
]);utilsApi.sql('CREATE TABLE products(title text, image_vector float_vector)')res = await utilsApi.sql('CREATE TABLE products(title text, image_vector float_vector)');utilsApi.sql("CREATE TABLE products(title text, image_vector float_vector)");utilsApi.Sql("CREATE TABLE products(title text, image_vector float_vector)");table products
{
type = rt
path = products
rt_field = title
stored_fields = title
rt_attr_float_vector = image_vector
}For information about using float vectors in searches, see KNN search.
Multi-value attributes allow storing variable-length lists of 32-bit unsigned integers. This can be useful for storing one-to-many numeric values, such as tags, product categories, and properties.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
- config
CREATE TABLE products(title text, product_codes multi);POST /cli -d "CREATE TABLE products(title text, product_codes multi)"$index = new \Manticoresearch\Index($client);
$index->setName('products');
$index->create([
'title'=>['type'=>'text'],
'product_codes'=>['type'=>'multi']
]);utilsApi.sql('CREATE TABLE products(title text, product_codes multi)')res = await utilsApi.sql('CREATE TABLE products(title text, product_codes multi)');utilsApi.sql("CREATE TABLE products(title text, product_codes multi)");utilsApi.Sql("CREATE TABLE products(title text, product_codes multi)");table products
{
type = rt
path = products
rt_field = title
stored_fields = title
rt_attr_multi = product_codes
}It supports filtering and aggregation, but not sorting. Filtering can be done using a condition that requires at least one element to pass (using ANY()) or all elements (ALL()) to pass.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
select * from products where any(product_codes)=3POST /search
{
"table": "products",
"query":
{
"match_all": {},
"equals" : { "any(product_codes)": 3 }
}
}$index->setName('products')->search('')->filter('any(product_codes)','equals',3)->get();searchApi.search({"table":"products","query":{"match_all":{},"equals":{"any(product_codes)":3}}}})res = await searchApi.search({"table":"products","query":{"match_all":{},"equals":{"any(product_codes)":3}}}})'searchRequest = new SearchRequest();
searchRequest.setIndex("forum");
query = new HashMap<String,Object>();
query.put("match_all",null);
query.put("equals",new HashMap<String,Integer>(){{
put("any(product_codes)",3);
}});
searchRequest.setQuery(query);
searchResponse = searchApi.search(searchRequest);object query = new { match_all=null };
var searchRequest = new SearchRequest("forum", query);
searchRequest.AttrFilter = new EqualsFilter("any(product_codes)", 3);
var searchResponse = searchApi.Search(searchRequest);Information like least or greatest element and length of the list can be extracted. An example shows ordering by the least element of a multi-value attribute.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
select least(product_codes) l from products order by l ascPOST /search
{
"table": "products",
"query":
{
"match_all": {},
"sort": [ { "product_codes":{ "order":"asc", "mode":"min" } } ]
}
}$index->setName('products')->search('')->sort('product_codes','asc','min')->get();searchApi.search({"table":"products","query":{"match_all":{},"sort":[{"product_codes":{"order":"asc","mode":"min"}}]}})res = await searchApi.search({"table":"products","query":{"match_all":{},"sort":[{"product_codes":{"order":"asc","mode":"min"}}]}});searchRequest = new SearchRequest();
searchRequest.setIndex("forum");
query = new HashMap<String,Object>();
query.put("match_all",null);
searchRequest.setQuery(query);
searchRequest.setSort(new ArrayList<Object>(){{
add(new HashMap<String,String>(){{ put("product_codes",new HashMap<String,String>(){{ put("order","asc");put("mode","min");}});}});
}});
searchResponse = searchApi.search(searchRequest);object query = new { match_all=null };
var searchRequest = new SearchRequest("forum", query);
searchRequest.Sort = new List<Object> {
new SortMVA("product_codes", SortOrder.OrderEnum.Asc, SortMVA.ModeEnum.Min)
};
searchResponse = searchApi.Search(searchRequest);When grouping by a multi-value attribute, a document will contribute to as many groups as there are different values associated with that document. For instance, if a collection contains exactly one document having a 'product_codes' multi-value attribute with values 5, 7, and 11, grouping on 'product_codes' will produce 3 groups with COUNT(*)equal to 1 and GROUPBY() key values of 5, 7, and 11, respectively. Also, note that grouping by multi-value attributes may lead to duplicate documents in the result set because each document can participate in many groups.
- SQL
insert into products values ( 1, 'doc one', (5,7,11) );
select id, count(*), groupby() from products group by product_codes;Query OK, 1 row affected (0.00 sec)
+------+----------+-----------+
| id | count(*) | groupby() |
+------+----------+-----------+
| 1 | 1 | 11 |
| 1 | 1 | 7 |
| 1 | 1 | 5 |
+------+----------+-----------+
3 rows in set (0.00 sec)The order of the numbers inserted as values of multivalued attributes is not preserved. Values are stored internally as a sorted set.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
insert into product values (1,'first',(4,2,1,3));
select * from products;POST /insert
{
"table":"products",
"id":1,
"doc":
{
"title":"first",
"product_codes":[4,2,1,3]
}
}
POST /search
{
"table": "products",
"query": { "match_all": {} }
}$index->addDocument([
"title"=>"first",
"product_codes"=>[4,2,1,3]
]);
$index->search('')-get();indexApi.insert({"table":"products","id":1,"doc":{"title":"first","product_codes":[4,2,1,3]}})
searchApi.search({"table":"products","query":{"match_all":{}}})await indexApi.insert({"table":"products","id":1,"doc":{"title":"first","product_codes":[4,2,1,3]}});
res = await searchApi.search({"table":"products","query":{"match_all":{}}});InsertDocumentRequest newdoc = new InsertDocumentRequest();
HashMap<String,Object> doc = new HashMap<String,Object>(){{
put("title","first");
put("product_codes",new int[] {4,2,1,3});
}};
newdoc.index("products").id(1L).setDoc(doc);
sqlresult = indexApi.insert(newdoc);
Map<String,Object> query = new HashMap<String,Object>();
query.put("match_all",null);
SearchRequest searchRequest = new SearchRequest();
searchRequest.setIndex("products");
searchRequest.setQuery(query);
SearchResponse searchResponse = searchApi.search(searchRequest);
System.out.println(searchResponse.toString() );Dictionary<string, Object> doc = new Dictionary<string, Object>();
doc.Add("title", "first");
doc.Add("product_codes", new List<Object> {4,2,1,3});
InsertDocumentRequest newdoc = new InsertDocumentRequest(index: "products", id: 1, doc: doc);
var sqlresult = indexApi.Insert(newdoc);
object query = new { match_all=null };
var searchRequest = new SearchRequest("products", query);
var searchResponse = searchApi.Search(searchRequest);
Console.WriteLine(searchResponse.ToString())Query OK, 1 row affected (0.00 sec)
+------+---------------+-------+
| id | product_codes | title |
+------+---------------+-------+
| 1 | 1,2,3,4 | first |
+------+---------------+-------+
1 row in set (0.01 sec){
"table":"products",
"_id":1,
"created":true,
"result":"created",
"status":201
}
{
"took":0,
"timed_out":false,
"hits":{
"total":1,
"hits":[
{
"_id": 1,
"_score":1,
"_source":{
"product_codes":[
1,
2,
3,
4
],
"title":"first"
}
}
]
}
}Array
(
[_index] => products
[_id] => 1
[created] => 1
[result] => created
[status] => 201
)
Array
(
[took] => 0
[timed_out] =>
[hits] => Array
(
[total] => 1
[hits] => Array
(
[0] => Array
(
[_id] => 1
[_score] => 1
[_source] => Array
(
[product_codes] => Array
(
[0] => 1
[1] => 2
[2] => 3
[3] => 4
)
[title] => first
)
)
)
)
){'created': True,
'found': None,
'id': 1,
'table': 'products',
'result': 'created'}
{'hits': {'hits': [{u'_id': u'1',
u'_score': 1,
u'_source': {u'product_codes': [1, 2, 3, 4],
u'title': u'first'}}],
'total': 1},
'profile': None,
'timed_out': False,
'took': 29}{"took":0,"timed_out":false,"hits":{"total":1,"hits":[{"_id": 1,"_score":1,"_source":{"product_codes":[1,2,3,4],"title":"first"}}]}}class SearchResponse {
took: 0
timedOut: false
hits: class SearchResponseHits {
total: 1
hits: [{_id=1, _score=1, _source={product_codes=[1, 2, 3, 4], title=first}}]
aggregations: null
}
profile: null
}
class SearchResponse {
took: 0
timedOut: false
hits: class SearchResponseHits {
total: 1
hits: [{_id=1, _score=1, _source={product_codes=[1, 2, 3, 4], title=first}}]
aggregations: null
}
profile: null
}
A data type that allows storing variable-length lists of 64-bit signed integers. It has the same functionality as multi-value integer.
- SQL
- JSON
- PHP
- Python
- javascript
- java
- C#
- config
CREATE TABLE products(title text, values multi64);POST /cli -d "CREATE TABLE products(title text, values multi64)"$index = new \Manticoresearch\Index($client);
$index->setName('products');
$index->create([
'title'=>['type'=>'text'],
'values'=>['type'=>'multi64']
]);utilsApi.sql('CREATE TABLE products(title text, values multi64))')res = await utilsApi.sql('CREATE TABLE products(title text, values multi64))');utilsApi.sql("CREATE TABLE products(title text, values multi64))");utilsApi.Sql("CREATE TABLE products(title text, values multi64))");table products
{
type = rt
path = products
rt_field = title
stored_fields = title
rt_attr_multi_64 = values
}When you use the columnar storage you can specify the following properties for the attributes.
By default, Manticore Columnar storage stores all attributes in a columnar fashion, as well as in a special docstore row by row. This enables fast execution of queries like SELECT * FROM ..., especially when fetching a large number of records at once. However, if you are sure that you do not need it or wish to save disk space, you can disable it by specifying fast_fetch='0' when creating a table or (if you are defining a table in a config) by using columnar_no_fast_fetch as shown in the following example.
- RT mode
- Plain mode
create table t(a int, b int fast_fetch='0') engine='columnar'; desc t;source min {
type = mysql
sql_host = localhost
sql_user = test
sql_pass =
sql_db = test
sql_query = select 1, 1 a, 1 b
sql_attr_uint = a
sql_attr_uint = b
}
table tbl {
path = tbl/col
source = min
columnar_attrs = *
columnar_no_fast_fetch = b
}+-------+--------+---------------------+
| Field | Type | Properties |
+-------+--------+---------------------+
| id | bigint | columnar fast_fetch |
| a | uint | columnar fast_fetch |
| b | uint | columnar |
+-------+--------+---------------------+
3 rows in set (0.00 sec)+-------+--------+---------------------+
| Field | Type | Properties |
+-------+--------+---------------------+
| id | bigint | columnar fast_fetch |
| a | uint | columnar fast_fetch |
| b | uint | columnar |
+-------+--------+---------------------+In Manticore Search, there are two ways to manage tables:
Real-time mode requires no table definition in the configuration file. However, the data_dir directive in the searchd section is mandatory. Table files are stored inside the data_dir.
Replication is only available in this mode.
You can use SQL commands such as CREATE TABLE, ALTER TABLE and DROP TABLE to create and modify table schema, and to drop it. This mode is particularly useful for real-time and percolate tables.
Table names are converted to lowercase when created.
In this mode, you can specify the table schema in the configuration file. Manticore reads this schema on startup and creates the table if it doesn't exist yet. This mode is particularly useful for plain tables that use data from an external storage.
To drop a table, remove it from the configuration file or remove the path setting and send a HUP signal to the server or restart it.
Table names are case-sensitive in this mode.
All table types are supported in this mode.
| Table type | RT mode | Plain mode |
|---|---|---|
| Real-time | supported | supported |
| Plain | not supported | supported |
| Percolate | supported | supported |
| Distributed | supported | supported |
| Template | not supported | supported |
A real-time table is a main type of table in Manticore. It lets you add, update, and delete documents, and you can see these changes right away. You can set up a real-time Table in a configuration file or use commands like CREATE, UPDATE, DELETE, or ALTER.
Internally a real-time table consists of one or more plain tables called chunks. There are two kinds of chunks:
- multiple disk chunks - these are saved on a disk and are structured like a plain table.
- single ram chunk - this is kept in memory and collects all changes.
The size of the RAM chunk is controlled by the rt_mem_limit setting. Once this limit is reached, the RAM chunk is transferred to disk as a disk chunk. If there are too many disk chunks, Manticore combines some of them to improve performance.
You can create a new real-time table in two ways: by using the CREATE TABLE command or through the _mapping endpoint of the HTTP JSON API.
You can use this command via both SQL and HTTP protocols:
- SQL
- JSON
- PHP
- Python
- Javascript
- Java
- C#
- Typescript
- Go
- CONFIG
CREATE TABLE products(title text, price float) morphology='stem_en';POST /cli -d "CREATE TABLE products(title text, price float) morphology='stem_en'"$index = new \Manticoresearch\Index($client);
$index->setName('products');
$index->create([
'title'=>['type'=>'text'],
'price'=>['type'=>'float'],
]);utilsApi.sql('CREATE TABLE forum(title text, price float)')res = await utilsApi.sql('CREATE TABLE forum(title text, price float)');utilsApi.sql("CREATE TABLE forum(title text, price float)");utilsApi.Sql("CREATE TABLE forum(title text, price float)");res = await utilsApi.sql('CREATE TABLE forum(title text, price float)');utilsAPI.Sql(context.Background()).Body("CREATE TABLE forum(title text, price float)").Execute()table products {
type = rt
path = tbl
rt_field = title
rt_attr_uint = price
stored_fields = title
}Query OK, 0 rows affected (0.00 sec){
"total":0,
"error":"",
"warning":""
}NOTE: The
_mappingAPI requires Manticore Buddy. If it doesn't work, make sure Buddy is installed.
Alternatively, you can create a new table via the _mapping endpoint. This endpoint allows you to define an Elasticsearch-like table structure to be converted to a Manticore table.
The body of your request must have the following structure:
"properties"
{
"FIELD_NAME_1":
{
"type": "FIELD_TYPE_1"
},
"FIELD_NAME_2":
{
"type": "FIELD_TYPE_2"
},
...
"FIELD_NAME_N":
{
"type": "FIELD_TYPE_M"
}
}When creating a table, Elasticsearch data types will be mapped to Manticore types according to the following rules:
- aggregate_metric => json
- binary => string
- boolean => bool
- byte => int
- completion => string
- date => timestamp
- date_nanos => bigint
- date_range => json
- dense_vector => json
- flattened => json
- flat_object => json
- float => float
- float_range => json
- geo_point => json
- geo_shape => json
- half_float => float
- histogram => json
- integer => int
- integer_range => json
- ip => string
- ip_range => json
- keyword => string
- knn_vector => float_vector
- long => bigint
- long_range => json
- match_only_text => text
- object => json
- point => json
- scaled_float => float
- search_as_you_type => text
- shape => json
- short => int
- text => text
- unsigned_long => int
- version => string
- JSON
POST /your_table_name/_mapping -d '
{
"properties": {
"price": {
"type": "float"
},
"title": {
"type": "text"
}
}
}
'{
"total":0,
"error":"",
"warning":""
}You can create a copy of a real-time table, with or without its data. Please note that if the table is large, copying it with data may take some time. Copying works in synchronous mode, but if the connection is dropped, it will continue in the background.
CREATE TABLE [IF NOT EXISTS] table_name LIKE old_table_name [WITH DATA]NOTE: Copying a table requires Manticore Buddy. If it doesn't work, make sure Buddy is installed.
- SQL
- Example (WITH DATA)
create table products LIKE old_products;create table products LIKE old_products WITH DATA;- Add documents.
- Update attributes and full-text fields using the Update process.
- Delete documents.
- Empty the table.
- Change the schema online with the
ALTERcommand, as explained in Change schema online. - Define the table in a configuration file, as detailed in Define table.
- Use the UUID feature for automatic ID provisioning.
- Ingest data using the indexer feature.
- Connect it to sources for easy indexing from external storage.
- Update the killlist_target, as it is automatically managed by the real-time table.
The following table outlines the different file extensions and their respective descriptions in a real-time table:
| Extension | Description |
|---|---|
.lock |
A lock file that ensures that only one process can access the table at a time. |
.ram |
The RAM chunk of the table, stored in memory and used as an accumulator of changes. |
.meta |
The headers of the real-time table that define its structure and settings. |
.*.sp* |
Disk chunks that are stored on disk with the same format as plain tables. They are created when the RAM chunk size exceeds the rt_mem_limit. |
For more information on the structure of disk chunks, refer to the plain table files structure.