When Manticore executes a fullscan query, it can either use plain scan to check every document against the filters, or it can use additional data and/or algorithms to speed up the query execution. To decide which approach to take, Manticore uses a query cost-based optimizer ("CBO" also known as "query optimizer").
The CBO may decide to replace one or more query filters with one of the following entities if it determines that it will improve performance:
- A docid index, which uses a special docid-only secondary index stored in files with the
.sptextension. In addition to improving filters on document ids, the docid index is also used to speed up document id to row id lookups, and to speed up the application of large killlists on daemon startup. - A columnar scan, which uses columnar storage and can only be used on a columnar attribute. It still scans every value and tests it against the filter, but it is heavily optimized and is usually faster than the default approach.
- Secondary indexes, which are generated for all attributes by default. They use the PGM index together with Manticore's built-in inverted index to retrieve the list of row ids corresponding to a value or range of values. The secondary indexes are stored in files with the
.spidxextension.
The optimizer estimates the cost of each execution path using different attribute statistics, including:
- Information on the data distribution within an attribute (histograms, stored in
.sphifiles). Histograms are generated automatically when data is indexed and are the main source of information for the CBO. - Information from PGM (secondary indexes), which is used to estimate the number of document lists to read. This helps to estimate doclist merge performance and to choose the correct merge algorithm (priority queue merge or bitmap merge).
- Columnar encoding statistics, which are used to estimate columnar data decompression performance.
- A columnar min-max tree. The CBO uses histograms to estimate the number of documents left after the filter was applied, but it also needs to estimate how many documents the filter had to process. For columnar attributes, partial evaluation of the min-max tree is used for that purpose.
The optimizer calculates the execution cost for every filter used in a query. Because certain filters can be replaced with several different entities (e.g., for a document id, Manticore can use a plain scan, a docid index lookup, a columnar scan (if the document id is columnar), and a secondary index), the optimizer evaluates every available combination. Note that there is a maximum limit of 1024 combinations.
To estimate query execution costs, the optimizer calculates the estimated costs of the most significant operations that are performed when the query is executed. It uses preset constants to represent the cost of each operation.
The optimizer compares the costs of each execution path and chooses the path with the lowest cost to execute the query.
Another thing to consider is multithreaded query execution (when pseudo_sharding is enabled). The CBO knows that some queries can be executed in multiple threads and takes that into account. The CBO favors smaller query execution times (i.e., latency) over throughput. For example, if a query using a columnar scan can be executed in multiple threads (and occupy multiple CPU cores) and is faster than a query executed in a single thread using secondary indexes, multithreaded execution will be preferred.
Queries using secondary indexes and docid indexes always run in a single thread, as benchmarks show that there is little to no benefit in making them multithreaded.
Currently, the optimizer only uses CPU costs and does not consider memory or disk usage.
ALTER TABLE table ADD COLUMN column_name [{INTEGER|INT|BIGINT|FLOAT|BOOL|MULTI|MULTI64|JSON|STRING|TIMESTAMP|TEXT [INDEXED [ATTRIBUTE]]}] [engine='columnar']
ALTER TABLE table DROP COLUMN column_nameIt supports adding one field at a time for RT tables. Supported data types are:
int- integer attributetimestamp- timestamp attributebigint- big integer attributefloat- float attributebool- boolean attributemulti- multi-valued integer attributemulti64- multi-valued bigint attributejson- json attributestring/text attribute/string attribute- string attributetext/text indexed stored/string indexed stored- full-text indexed field with original value stored in docstoretext indexed/string indexed- full-text indexed field, indexed only (the original value is not stored in docstore)text indexed attribute/string indexed attribute- full text indexed field + string attribute (not storing the original value in docstore)text stored/string stored- the value will be only stored in docstore, not full-text indexed, not a string attribute- adding
engine='columnar'to any attribute (except for json) will make it stored in the columnar storage
- ❗It's recommended to backup table files before
ALTERing it to avoid data corruption in case of a sudden power interruption or other similar issues. - Querying a table is impossible while a column is being added.
- Newly created attribute's values are set to 0.
ALTERwill not work for distributed tables and tables without any attributes.DROP COLUMNwill fail if a table has only one field.- When dropping a field which is both a full-text field and a string attribute the first
ALTER DROPdrops the attribute, the second one drops the full-text field. - Adding/dropping full-text field is only supported in the RT mode.
- Example
mysql> desc rt;
+------------+-----------+
| Field | Type |
+------------+-----------+
| id | bigint |
| text | field |
| group_id | uint |
| date_added | timestamp |
+------------+-----------+
mysql> alter table rt add column test integer;
mysql> desc rt;
+------------+-----------+
| Field | Type |
+------------+-----------+
| id | bigint |
| text | field |
| group_id | uint |
| date_added | timestamp |
| test | uint |
+------------+-----------+
mysql> alter table rt drop column group_id;
mysql> desc rt;
+------------+-----------+
| Field | Type |
+------------+-----------+
| id | bigint |
| text | field |
| date_added | timestamp |
| test | uint |
+------------+-----------+
mysql> alter table rt add column title text indexed;
mysql> desc rt;
+------------+-----------+------------+
| Field | Type | Properties |
+------------+-----------+------------+
| id | bigint | |
| text | text | indexed |
| title | text | indexed |
| date_added | timestamp | |
| test | uint | |
+------------+-----------+------------+
mysql> alter table rt add column title text attribute;
mysql> desc rt;
+------------+-----------+------------+
| Field | Type | Properties |
+------------+-----------+------------+
| id | bigint | |
| text | text | indexed |
| title | text | indexed |
| date_added | timestamp | |
| test | uint | |
| title | string | |
+------------+-----------+------------+
mysql> alter table rt drop column title;
mysql> desc rt;
+------------+-----------+------------+
| Field | Type | Properties |
+------------+-----------+------------+
| id | bigint | |
| text | text | indexed |
| title | text | indexed |
| date_added | timestamp | |
| test | uint | |
+------------+-----------+------------+
mysql> alter table rt drop column title;
mysql> desc rt;
+------------+-----------+------------+
| Field | Type | Properties |
+------------+-----------+------------+
| id | bigint | |
| text | text | indexed |
| date_added | timestamp | |
| test | uint | |
+------------+-----------+------------+ALTER TABLE table ft_setting='value'[, ft_setting2='value']You can also use ALTER to modify full-text settings of your table in the RT mode. Just remember that it doesn't affect existing documents, it only affects new ones. Take a look at the example where we:
- create a table with a full-text field and
charset_tablethat allows only 3 searchable characters:a,bandc. - then we insert document 'abcd' and find it by query
abcd, thedjust gets ignored since it's not in thecharset_tablearray - then we understand, that we want
dto be searchable too, so we add it with help ofALTER - but the same query
where match('abcd')still says it searched byabc, because the existing document remembers previous contents ofcharset_table - then we add another document
abcdand search byabcdagain - now it finds the both documents and
show metasays it used two keywords:abc(to find the old document) andabcd(for the new one).
- Example
mysql> create table rt(title text) charset_table='a,b,c';
mysql> insert into rt(title) values('abcd');
mysql> select * from rt where match('abcd');
+---------------------+-------+
| id | title |
+---------------------+-------+
| 1514630637682688054 | abcd |
+---------------------+-------+
mysql> show meta;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| total | 1 |
| total_found | 1 |
| time | 0.000 |
| keyword[0] | abc |
| docs[0] | 1 |
| hits[0] | 1 |
+---------------+-------+
mysql> alter table rt charset_table='a,b,c,d';
mysql> select * from rt where match('abcd');
+---------------------+-------+
| id | title |
+---------------------+-------+
| 1514630637682688054 | abcd |
+---------------------+-------+
mysql> show meta
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| total | 1 |
| total_found | 1 |
| time | 0.000 |
| keyword[0] | abc |
| docs[0] | 1 |
| hits[0] | 1 |
+---------------+-------+
mysql> insert into rt(title) values('abcd');
mysql> select * from rt where match('abcd');
+---------------------+-------+
| id | title |
+---------------------+-------+
| 1514630637682688055 | abcd |
| 1514630637682688054 | abcd |
+---------------------+-------+
mysql> show meta;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| total | 2 |
| total_found | 2 |
| time | 0.000 |
| keyword[0] | abc |
| docs[0] | 1 |
| hits[0] | 1 |
| keyword[1] | abcd |
| docs[1] | 1 |
| hits[1] | 1 |
+---------------+-------+ALTER TABLE table RECONFIGUREALTER can also reconfigure an RT table in the plain mode, so that new tokenization, morphology and other text processing settings from the configuration file take effect for new documents. Note, that the existing document will be left intact. Internally, it forcibly saves the current RAM chunk as a new disk chunk and adjusts the table header, so that new documents are tokenized using the updated full-text settings.
- Example
mysql> show table rt settings;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| settings | |
+---------------+-------+
1 row in set (0.00 sec)
mysql> alter table rt reconfigure;
Query OK, 0 rows affected (0.00 sec)
mysql> show table rt settings;
+---------------+----------------------+
| Variable_name | Value |
+---------------+----------------------+
| settings | morphology = stem_en |
+---------------+----------------------+
1 row in set (0.00 sec)ALTER TABLE table REBUILD SECONDARYALTER can also be used to rebuild secondary indexes in a given table. Sometimes a secondary index can be disabled for the whole table or for one/multiple attributes in it:
- On
UPDATEof an attribute: in this case its secondary index gets disabled. - In case Manticore loads a table with old formatted secondary indexes: in this case secondary indexes will be disabled for the whole table.
ALTER TABLE table REBUILD SECONDARY rebuilds secondary indexes from attribute data and enables them again.
- Example
ALTER TABLE rt REBUILD SECONDARY;Query OK, 0 rows affected (0.00 sec)Returns the absolute value of the argument.
Returns the arctangent function of two arguments, expressed in radians.
BITDOT(mask, w0, w1, ...) returns the sum of products of an each bit of a mask multiplied with its weight. bit0*w0 + bit1*w1 + ...
Returns the smallest integer value greater or equal to the argument.
Returns the cosine of the argument.
Returns the CRC32 value of a string argument.
Returns the exponent of the argument (e=2.718... to the power of the argument).
Returns the N-th Fibonacci number, where N is the integer argument. That is, arguments of 0 and up will generate the values 0, 1, 1, 2, 3, 5, 8, 13 and so on. Note that the computations are done using 32-bit integer math and thus numbers 48th and up will be returned modulo 2\^32.
Returns the largest integer value lesser or equal to the argument.
GREATEST(attr_json.some_array) function takes JSON array as the argument, and returns the greatest value in that array. Also works for MVA.
Returns the result of an integer division of the first argument by the second argument. Both arguments must be of an integer type.
LEAST(attr_json.some_array) function takes JSON array as the argument, and returns the least value in that array. Also works for MVA.
Returns the natural logarithm of the argument (with the base of e=2.718...).
Returns the common logarithm of the argument (with the base of 10).
Returns the binary logarithm of the argument (with the base of 2).
Returns the bigger of two arguments.
Returns the smaller of two arguments.
Returns the first argument raised to the power of the second argument.
RAND(seed) function returns a random float between 0..1. Optionally can accept seed which can be:
- constant integer
- or integer attribute's name
If you use the seed take into account that it resets rand()'s starting point separately for each plain table / RT disk / RAM chunk / pseudo shard, so queries to a distributed table in any form can return multiple identical random values.
Returns the sine of the argument.
Returns the square root of the argument.