When you run a query via SQL over the MySQL protocol, you receive the requested columns as a result or an empty result set if nothing is found.
- SQL
SELECT * FROM tbl;+------+------+--------+
| id | age | name |
+------+------+--------+
| 1 | 25 | joe |
| 2 | 25 | mary |
| 3 | 33 | albert |
+------+------+--------+
3 rows in set (0.00 sec)Additionally, you can use the SHOW META call to see extra meta-information about the latest query.
- SQL
SELECT id,story_author,comment_author FROM hn_small WHERE story_author='joe' LIMIT 3; SHOW META;++--------+--------------+----------------+
| id | story_author | comment_author |
+--------+--------------+----------------+
| 152841 | joe | SwellJoe |
| 161323 | joe | samb |
| 163735 | joe | jsjenkins168 |
+--------+--------------+----------------+
3 rows in set (0.01 sec)
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| total | 3 |
| total_found | 20 |
| total_relation | gte |
| time | 0.010 |
+----------------+-------+
4 rows in set (0.00 sec)In some cases, such as when performing a faceted search, you may receive multiple result sets as a response to your SQL query.
- SQL
SELECT * FROM tbl WHERE MATCH('joe') FACET age;+------+------+
| id | age |
+------+------+
| 1 | 25 |
+------+------+
1 row in set (0.00 sec)
+------+----------+
| age | count(*) |
+------+----------+
| 25 | 1 |
+------+----------+
1 row in set (0.00 sec)In case of a warning, the result set will include a warning flag, and you can see the warning using SHOW WARNINGS.
- SQL
SELECT * from tbl where match('"joe"/3'); show warnings;+------+------+------+
| id | age | name |
+------+------+------+
| 1 | 25 | joe |
+------+------+------+
1 row in set, 1 warning (0.00 sec)
+---------+------+--------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------------------------------+
| warning | 1000 | quorum threshold too high (words=1, thresh=3); replacing quorum operator with AND operator |
+---------+------+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)If your query fails, you will receive an error:
- SQL
SELECT * from tbl where match('@surname joe');ERROR 1064 (42000): index idx: query error: no field 'surname' found in schemaVia the HTTP JSON interface, the query result is sent as a JSON document. Example:
{
"took":10,
"timed_out": false,
"hits":
{
"total": 2,
"hits":
[
{
"_id": 1,
"_score": 1,
"_source": { "gid": 11 }
},
{
"_id": 2,
"_score": 1,
"_source": { "gid": 12 }
}
]
}
}took: time in milliseconds it took to execute the searchtimed_out: whether the query timed out or nothits: search results, with the following properties:total: total number of matching documentshits: an array containing matches
The query result can also include query profile information. See Query profile.
Each match in the hits array has the following properties:
_id: match id_score: match weight, calculated by the ranker_source: an array containing the attributes of this match
By default, all attributes are returned in the _source array. You can use the _source property in the request payload to select the fields you want to include in the result set. Example:
{
"table":"test",
"_source":"attr*",
"query": { "match_all": {} }
}You can specify the attributes you want to include in the query result as a string ("_source": "attr*") or as an array of strings ("_source": [ "attr1", "attri*" ]"). Each entry can be an attribute name or a wildcard (*, % and ? symbols are supported).
You can also explicitly specify which attributes you want to include and which to exclude from the result set using the includes and excludes properties:
"_source":
{
"includes": [ "attr1", "attri*" ],
"excludes": [ "*desc*" ]
}An empty list of includes is interpreted as "include all attributes," while an empty list of excludes does not match anything. If an attribute matches both the includes and excludes, then the excludes win.
WHERE is an SQL clause that works for both full-text matching and additional filtering. The following operators are available:
- Comparison operators
<, >, <=, >=, =, <>, BETWEEN, IN, IS NULL - Boolean operators
AND, OR, NOT
MATCH('query') is supported and maps to a full-text query.
The {col_name | expr_alias} [NOT] IN @uservar condition syntax is supported. Refer to the SET syntax for a description of global user variables.
If you prefer the HTTP JSON interface, you can also apply filtering. It might seem more complex than SQL, but it is recommended for cases when you need to prepare a query programmatically, such as when a user fills out a form in your application.
Here's an example of several filters in a bool query.
This full-text query matches all documents containing product in any field. These documents must have a price greater than or equal to 500 (gte) and less than or equal to 1000 (lte). All of these documents must not have a revision less than 15 (lt).
- JSON
POST /search
{
"table": "test1",
"query": {
"bool": {
"must": [
{ "match" : { "_all" : "product" } },
{ "range": { "price": { "gte": 500, "lte": 1000 } } }
],
"must_not": {
"range": { "revision": { "lt": 15 } }
}
}
}
}The bool query matches documents based on boolean combinations of other queries and/or filters. Queries and filters must be specified in must, should, or must_not sections and can be nested.
- JSON
POST /search
{
"table":"test1",
"query": {
"bool": {
"must": [
{ "match": {"_all":"keyword"} },
{ "range": { "revision": { "gte": 14 } } }
]
}
}
}Queries and filters specified in the must section are required to match the documents. If multiple fulltext queries or filters are specified, all of them must match. This is the equivalent of AND queries in SQL. Note that if you want to match against an array (multi-value attribute), you can specify the attribute multiple times. The result will be positive only if all the queried values are found in the array, e.g.:
"must": [
{"equals" : { "product_codes": 5 }},
{"equals" : { "product_codes": 6 }}
]Note also, it may be better in terms of performance to use:
{"in" : { "all(product_codes)": [5,6] }}(see details below).
Queries and filters specified in the should section should match the documents. If some queries are specified in must or must_not, should queries are ignored. On the other hand, if there are no queries other than should, then at least one of these queries must match a document for it to match the bool query. This is the equivalent of OR queries. Note, if you want to match against an array (multi-value attribute) you can specify the attribute multiple times, e.g.:
"should": [
{"equals" : { "product_codes": 7 }},
{"equals" : { "product_codes": 8 }}
]Note also, it may be better in terms of performance to use:
{"in" : { "any(product_codes)": [7,8] }}(see details below).
Queries and filters specified in the must_not section must not match the documents. If several queries are specified under must_not, the document matches if none of them match.
- JSON
POST /search
{
"table":"t",
"query": {
"bool": {
"should": [
{
"equals": {
"b": 1
}
},
{
"equals": {
"b": 3
}
}
],
"must": [
{
"equals": {
"a": 1
}
}
],
"must_not": {
"equals": {
"b": 2
}
}
}
}
}A bool query can be nested inside another bool so you can make more complex queries. To make a nested boolean query just use another bool instead of must, should or must_not. Here is how this query:
a = 2 and (a = 10 or b = 0)should be presented in JSON.
- JSON
a = 2 and (a = 10 or b = 0)
POST /search
{
"table":"t",
"query": {
"bool": {
"must": [
{
"equals": {
"a": 2
}
},
{
"bool": {
"should": [
{
"equals": {
"a": 10
}
},
{
"equals": {
"b": 0
}
}
]
}
}
]
}
}
}More complex query:
(a = 1 and b = 1) or (a = 10 and b = 2) or (b = 0)- JSON
(a = 1 and b = 1) or (a = 10 and b = 2) or (b = 0)
POST /search
{
"table":"t",
"query": {
"bool": {
"should": [
{
"bool": {
"must": [
{
"equals": {
"a": 1
}
},
{
"equals": {
"b": 1
}
}
]
}
},
{
"bool": {
"must": [
{
"equals": {
"a": 10
}
},
{
"equals": {
"b": 2
}
}
]
}
},
{
"bool": {
"must": [
{
"equals": {
"b": 0
}
}
]
}
}
]
}
}
}Queries in SQL format (query_string) can also be used in bool queries.
- JSON
POST /search
{
"table": "test1",
"query": {
"bool": {
"must": [
{ "query_string" : "product" },
{ "query_string" : "good" }
]
}
}
}Equality filters are the simplest filters that work with integer, float and string attributes.
- JSON
POST /search
{
"table":"test1",
"query": {
"equals": { "price": 500 }
}
}Filter equals can be applied to a multi-value attribute and you can use:
any()which will be positive if the attribute has at least one value which equals to the queried value;all()which will be positive if the attribute has a single value and it equals to the queried value
- JSON
POST /search
{
"table":"test1",
"query": {
"equals": { "any(price)": 100 }
}
}Set filters check if attribute value is equal to any of the values in the specified set.
Set filters support integer, string and multi-value attributes.
- JSON
POST /search
{
"table":"test1",
"query": {
"in": {
"price": [1,10,100]
}
}
}When applied to a multi-value attribute you can use:
any()(equivalent to no function) which will be positive if there's at least one match between the attribute values and the queried values;all()which will be positive if all the attribute values are in the queried set
- JSON
POST /search
{
"table":"test1",
"query": {
"in": {
"all(price)": [1,10]
}
}
}Range filters match documents that have attribute values within a specified range.
Range filters support the following properties:
gte: greater than or equal togt: greater thanlte: less than or equal tolt: less than
- JSON
POST /search
{
"table":"test1",
"query": {
"range": {
"price": {
"gte": 500,
"lte": 1000
}
}
}
}geo_distance filters are used to filter the documents that are within a specific distance from a geo location.
Specifies the pin location, in degrees. Distances are calculated from this point.
Specifies the attributes that contain latitude and longitude.
Specifies distance calculation function. Can be either adaptive or haversine. adaptive is faster and more precise, for more details see GEODIST(). Optional, defaults to adaptive.
Specifies the maximum distance from the pin locations. All documents within this distance match. The distance can be specified in various units. If no unit is specified, the distance is assumed to be in meters. Here is a list of supported distance units:
- Meter:
mormeters - Kilometer:
kmorkilometers - Centimeter:
cmorcentimeters - Millimeter:
mmormillimeters - Mile:
miormiles - Yard:
ydoryards - Feet:
ftorfeet - Inch:
inorinch - Nautical mile:
NM,nmiornauticalmiles
location_anchor and location_source properties accept the following latitude/longitude formats:
- an object with lat and lon keys:
{ "lat": "attr_lat", "lon": "attr_lon" } - a string of the following structure:
"attr_lat, attr_lon" - an array with the latitude and longitude in the following order:
[attr_lon, attr_lat]
Latitude and longitude are specified in degrees.
- Basic example
- Advanced example
POST /search
{
"table":"test",
"query": {
"geo_distance": {
"location_anchor": {"lat":49, "lon":15},
"location_source": {"attr_lat, attr_lon"},
"distance_type": "adaptive",
"distance":"100 km"
}
}
}Table joins in Manticore Search enable you to combine documents from two tables by matching related columns. This functionality allows for more complex queries and enhanced data retrieval across multiple tables.
SELECT
select_expr [, select_expr] ...
FROM tbl_name
{INNER | LEFT} JOIN tbl2_name
ON join_condition
[...other select options]
join_condition: {
left_table.attr = right_table.attr
| left_table.json_attr.string_id = string(right_table.json_attr.string_id)
| left_table.json_attr.int_id = int(right_table.json_attr.int_id)
}For more information on select options, refer to the SELECT section.
When joining by a value from a JSON attribute, you need to explicitly specify the value's type using the int() or string() function.
- String JSON attribute
- Int JSON attribute
SELECT ... ON left_table.json_attr.string_id = string(right_table.json_attr.string_id)POST /search
{
"table": "table_name",
"query": {
<optional full-text query against the left table>
},
"join": [
{
"type": "inner" | "left",
"table": "joined_table_name",
"query": {
<optional full-text query against the right table>
},
"on": [
{
"left": {
"table": "left_table_name",
"field": "field_name",
"type": "<common field's type when joining using json attributes>"
},
"operator": "eq",
"right": {
"table": "right_table_name",
"field": "field_name"
}
}
]
}
],
"options": {
...
}
}
on.type: {
int
| string
}Note, there is the type field in the left operand section which you should use when joining two tables using json attributes. The allowed values are string and int.
Manticore Search supports two types of joins:
- INNER JOIN: Returns only the rows where there is a match in both tables. For example, the query performs an INNER JOIN between the
ordersandcustomerstables, including only the orders that have matching customers.
- SQL
- JSON
SELECT product, customers.email, customers.name, customers.address
FROM orders
INNER JOIN customers
ON customers.id = orders.customer_id
WHERE MATCH('maple', customers)
ORDER BY customers.email ASC;+---------+-------------------+----------------+-------------------+
| product | customers.email | customers.name | customers.address |
+---------+-------------------+----------------+-------------------+
| Laptop | [email protected] | Alice Johnson | 123 Maple St |
| Tablet | [email protected] | Alice Johnson | 123 Maple St |
+---------+-------------------+----------------+-------------------+
2 rows in set (0.00 sec)- LEFT JOIN: Returns all rows from the left table and the matched rows from the right table. If there is no match, NULL values are returned for the right table's columns. For example, this query retrieves all customers along with their corresponding orders using a LEFT JOIN. If no corresponding order exists, NULL values will appear. The results are sorted by the customer's email, and only the customer's name and the order quantity are selected.
- SQL
- JSON
SELECT
name, orders.quantity
FROM customers
LEFT JOIN orders
ON orders.customer_id = customers.id
ORDER BY email ASC;+---------------+-----------------+-------------------+
| name | orders.quantity | @int_attr_email |
+---------------+-----------------+-------------------+
| Alice Johnson | 1 | [email protected] |
| Alice Johnson | 1 | [email protected] |
| Bob Smith | 2 | [email protected] |
| Carol White | 1 | [email protected] |
| John Smith | NULL | [email protected] |
+---------------+-----------------+-------------------+
5 rows in set (0.00 sec)Building on the previous examples, let's explore a more advanced scenario where we combine table joins with faceting. This allows us to not only retrieve joined data but also aggregate and analyze it in meaningful ways.
This query retrieves products, customer names, product prices, and product tags from the orders and customers tables. It performs a LEFT JOIN, ensuring all customers are included even if they have not made an order. The query filters the results to include only orders with a price greater than 500 and matches the products to the terms 'laptop', 'phone', or 'monitor'. The results are ordered by the id of the orders in ascending order. Additionally, the query facets the results based on the warranty details from the JSON attributes of the joined orders table.
- SQL
- JSON
SELECT orders.product, name, orders.details.price, orders.tags
FROM customers
LEFT JOIN orders
ON customers.id = orders.customer_id
WHERE orders.details.price > 500
AND MATCH('laptop|phone|monitor', orders)
ORDER BY orders.id ASC
FACET orders.details.warranty;+----------------+---------------+----------------------+-------------+
| orders.product | name | orders.details.price | orders.tags |
+----------------+---------------+----------------------+-------------+
| Laptop | Alice Johnson | 1200 | 101,102 |
| Phone | Bob Smith | 800 | 103 |
+----------------+---------------+----------------------+-------------+
2 rows in set (0.01 sec)
--- 2 out of 2 results in 0ms ---
+-------------------------+----------+
| orders.details.warranty | count(*) |
+-------------------------+----------+
| 2 years | 1 |
| 1 year | 1 |
+-------------------------+----------+
2 rows in set (0.01 sec)
--- 2 out of 2 results in 0ms ---Separate options can be specified for queries in a join: for the left table and the right table. The syntax is OPTION(<table_name>) for SQL queries and one or more subobjects under "options" for JSON queries.
Here's an example of how to specify different field weights for a full-text query on the right table. To retrieve match weights via SQL, use the <table_name>.weight() expression.
In JSON queries, this weight is represented as <table_name>._score.
- SQL
- JSON
SELECT product, customers.email, customers.name, customers.address, customers.weight()
FROM orders
INNER JOIN customers
ON customers.id = orders.customer_id
WHERE MATCH('maple', customers)
OPTION(customers) field_weights=(address=1500);+---------+-------------------+----------------+-------------------+--------------------+
| product | customers.email | customers.name | customers.address | customers.weight() |
+---------+-------------------+----------------+-------------------+--------------------+
| Laptop | [email protected] | Alice Johnson | 123 Maple St | 1500680 |
| Tablet | [email protected] | Alice Johnson | 123 Maple St | 1500680 |
+---------+-------------------+----------------+-------------------+--------------------+
2 rows in set (0.00 sec)When performing table joins, Manticore Search processes the results in batches to optimize performance and resource usage. Here's how it works:
-
How Batching Works:
- The query on the left table is executed first, and the results are accumulated into a batch.
- This batch is then used as input for the query on the right table, which is executed as a single operation.
- This approach minimizes the number of queries sent to the right table, improving efficiency.
-
Configuring Batch Size:
- The size of the batch can be adjusted using the
join_batch_sizesearch option. - It is also configurable in the
searchdsection of the configuration file. - The default batch size is
1000, but you can increase or decrease it depending on your use case. - Setting
join_batch_size=0disables batching entirely, which may be useful for debugging or specific scenarios.
- The size of the batch can be adjusted using the
-
Performance Considerations:
- A larger batch size can improve performance by reducing the number of queries executed on the right table.
- However, larger batches may consume more memory, especially for complex queries or large datasets.
- Experiment with different batch sizes to find the optimal balance between performance and resource usage.
To further optimize join operations, Manticore Search employs a caching mechanism for queries executed on the right table. Here's what you need to know:
-
How Caching Works:
- Each query on the right table is defined by the
JOIN ONconditions. - If the same
JOIN ONconditions are repeated across multiple queries, the results are cached and reused. - This avoids redundant queries and speeds up subsequent join operations.
- Each query on the right table is defined by the
-
Configuring Cache Size:
- The size of the join cache can be configured using the join_cache_size option in the
searchdsection of the configuration file. - The default cache size is
20MB, but you can adjust it based on your workload and available memory. - Setting
join_cache_size=0disables caching entirely.
- The size of the join cache can be configured using the join_cache_size option in the
-
Memory Considerations:
- Each thread maintains its own cache, so the total memory usage depends on the number of threads and the cache size.
- Ensure your server has sufficient memory to accommodate the cache, especially for high-concurrency environments.
When using JOINs in Manticore Search, keep the following points in mind:
-
Field selection: When selecting fields from two tables in a JOIN, do not prefix fields from the left table, but do prefix fields from the right table. For example:
SELECT field_name, right_table.field_name FROM ... -
JOIN conditions: Always explicitly specify the table names in your JOIN conditions:
JOIN ON table_name.some_field = another_table_name.some_field -
Expressions with JOINs: When using expressions that combine fields from both joined tables, alias the result of the expression:
SELECT *, (nums2.n + 3) AS x, x * n FROM nums LEFT JOIN nums2 ON nums2.id = nums.num2_id -
Filtering on aliased expressions: You cannot use aliases for expressions involving fields from both tables in the WHERE clause.
-
JSON attributes: When joining on JSON attributes, you must explicitly cast the values to the appropriate type:
-- Correct: SELECT * FROM t1 LEFT JOIN t2 ON int(t1.json_attr.id) = t2.json_attr.id -- Incorrect: SELECT * FROM t1 LEFT JOIN t2 ON t1.json_attr.id = t2.json_attr.id -
NULL handling: You can use IS NULL and IS NOT NULL conditions on joined fields:
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.name IS NULL SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.name IS NOT NULL -
Using ANY with MVA: When using the
ANY()function with multi-valued attributes in JOINs, alias the multi-valued attribute from the joined table:SELECT *, t2.m AS alias FROM t LEFT JOIN t2 ON t.id = t2.t_id WHERE ANY(alias) IN (3, 5)
By following these guidelines, you can effectively use JOINs in Manticore Search to combine data from multiple indexes and perform complex queries.