¶ 28

References

SQL commands

Schema management

CREATE TABLE - Creates new table
CREATE TABLE LIKE - Creates table using another one as a template
DESCRIBE - Prints out table's field list and their types
ALTER TABLE - Changes table schema / settings
ALTER TABLE REBUILD SECONDARY - Updates/recovers secondary indexes
DROP TABLE IF EXISTS - Deletes a table (if it exists)
SHOW TABLES - Shows tables list
SHOW CREATE TABLE - Shows SQL command how to create the table
SHOW TABLE STATUS - Shows information about current table status
SHOW TABLE SETTINGS - Shows table settings

Data management

INSERT - Adds new documents
REPLACE - Replaces existing documents with new ones
UPDATE - Does in-place update in documents
DELETE - Deletes documents
TRUNCATE TABLE - Deletes all documents from table

Backup

BACKUP - Backs up your tables

SELECT

SELECT - Searches
WHERE - Filters
GROUP BY - Groups search results
GROUP BY ORDER - Orders groups
GROUP BY HAVING - Filters groups
OPTION - Query Options
FACET - Faceted search
SUB-SELECTS - About using SELECT sub-queries
EXPLAIN QUERY - Shows query execution plan without running the query itself
SHOW META - Shows extended information about executed query
SHOW PROFILE - Shows profiling information about executed query
SHOW PLAN - Shows query execution plan after the query was executed
SHOW WARNINGS - Shows warnings from the latest query

Flushing misc things

FLUSH ATTRIBUTES - Forces flushing updated attributes to disk
FLUSH HOSTNAMES - Renews IPs associates to agent host names
FLUSH LOGS - Initiates reopen of searchd log and query log files (similar to USR1)

Real-time table optimization

FLUSH RAMCHUNK - Force creating a new disk chunk
FLUSH TABLE - Flushes real-time table RAM chunk to disk
OPTIMIZE TABLE - Enqueues real-time table for optimization

Importing to a real-time table

ATTACH TABLE - Moves data from a plain table to a real-time table
IMPORT TABLE - Imports previously created RT or PQ table into a server running in the RT mode

Replication

JOIN CLUSTER - Joins a replication cluster
ALTER CLUSTER - Adds/deletes a table to a replication cluster
SET CLUSTER - Changes replication cluster settings
DELETE CLUSTER - Deletes a replication cluster

Plain table rotate

RELOAD TABLE - Rotates a plain table
RELOAD TABLES - Rotates all plain tables

Transactions

BEGIN - Begins a transaction
COMMIT - Finishes a transaction
ROLLBACK - Rolls back a transaction

CALL

CALL SUGGEST, CALL QSUGGEST - Suggests spell-corrected words
CALL SNIPPETS - Builds a highlighted results snippet from provided data and query
CALL PQ - Runs a percolate query
CALL KEYWORDS - Used to check how keywords are tokenized. Also allows to retrieve tokenized forms of provided keywords

Plugins

CREATE FUNCTION - Installs a user-defined function (UDF)
DROP FUNCTION - Drops a user-defined function (UDF)
CREATE PLUGIN - Installs a plugin
CREATE BUDDY PLUGIN - Installs a Buddy plugin
DROP PLUGIN - Drops a plugin
DROP BUDDY PLUGIN - Drops a Buddy plugin
RELOAD PLUGINS - Reloads all plugins from a given library
ENABLE BUDDY PLUGIN - Reactivates a previously disabled Buddy plugin
DISABLE BUDDY PLUGIN - Deactivates an active Buddy plugin

Server status

SHOW STATUS - Displays a number of useful performance counters
SHOW THREADS - Lists all currently active client threads
SHOW VARIABLES - Lists server-wide variables and their values
SHOW VERSION - Provides detailed version information of various components of the instance.

HTTP endpoints

/sql - Execute an SQL statement over HTTP JSON
/cli - Provides an HTTP command line interface
/insert - Inserts a document into a real-time table
/pq/tbl_name/doc - Adds a PQ rule to a percolate table
/update - Updates a document in a real-time table
/replace - Replaces an existing document in a real-time table or inserts it if it doesn't exist
/pq/tbl_name/doc/N?refresh=1 - Replaces a PQ rule in a percolate table
/delete - Removes a document from a table
/bulk - Executes multiple insert, update, or delete operations in a single call. Learn more about bulk inserts here.
/search - Performs a search
/search -> knn - Performs a KNN vector search
/pq/tbl_name/search - Performs a reverse search in a percolate table
/tbl_name/_mapping - Creates a table schema in the Elasticsearch style

Common things

Common table settings

Plain table settings

Distributed table settings

RT table settings

Full-text search operators

OR
MAYBE
NOT - NOT operator
@field - field search operator
@field%5BN%5D - field position limit modifier
@(field1,field2,...) - multi-field search operator
@!field - ignore field search operator
@!(field1,field2,...) - ignore multi-field search operator
@* - all-field search operator
"word1 word2 ... " - phrase search operator
"word1 word2 ... "~N - proximity search operator
"word1 word2 ... "/N - quorum matching operator
word1 <<< word2 <<< word3 - strict order operator
=word1 - exact form modifier
^word1 - field-start modifier
word2$ - field-end modifier
word^N - keyword IDF boost modifier
word1 NEAR/N word2 - NEAR, generalized proximity operator
word1 NOTNEAR/N word2 - NOTNEAR, negative assertion operator
word1 PARAGRAPH word2 PARAGRAPH "word3 word4" - PARAGRAPH operator
word1 SENTENCE word2 SENTENCE "word3 word4" - SENTENCE operator
ZONE:(h3,h4) - ZONE limit operator
ZONESPAN:(h2) - ZONESPAN limit operator
@@relaxed - suppresses errors about missing fields
t?st - wildcard operators
REGEX(/pattern/) - REGEX operator

Functions

Mathematical

ABS() - Returns absolute value
ATAN2() - Returns arctangent function of two arguments
BITDOT() - Returns sum of products of each bit of a mask multiplied with its weight
CEIL() - Returns smallest integer value greater or equal to the argument
COS() - Returns cosine of the argument
CRC32() - Returns CRC32 value of the argument
EXP() - Returns exponent of the argument
FIBONACCI() - Returns the N-th Fibonacci number, where N is the integer argument
FLOOR() - Returns the largest integer value lesser or equal to the argument
GREATEST() - Takes JSON/MVA array as the argument and returns the greatest value in that array
IDIV() - Returns result of an integer division of the first argument by the second argument
LEAST() - Takes JSON/MVA array as the argument, and returns the least value in that array
LN() - Returns natural logarithm of the argument
LOG10() - Returns common logarithm of the argument
LOG2() - Returns binary logarithm of the argument
MAX() - Returns the larger of two arguments
MIN() - Returns the smaller of two arguments
POW() - Returns the first argument raised to the power of the second argument
RAND() - Returns random float between 0 and 1
SIN() - Returns sine of the argument
SQRT() - Returns square root of the argument

Searching and ranking

BM25F() - Returns precise BM25F formula value
EXIST() - Replaces non-existing columns with default values
GROUP_CONCAT() - Produces a comma-separated list of the attribute values of all documents in the group
HIGHLIGHT() - Highlights search results
MIN_TOP_SORTVAL() - Returns sort key value of the worst found element in the current top-N matches
MIN_TOP_WEIGHT() - Returns weight of the worst found element in the current top-N matches
PACKEDFACTORS() - Outputs weighting factors
REMOVE_REPEATS() - Removes repeated adjusted rows with the same 'column' value
WEIGHT() - Returns fulltext match score
ZONESPANLIST() - Returns pairs of matched zone spans
QUERY() - Returns current full-text query

Type casting

BIGINT() - Forcibly promotes the integer argument to 64-bit type
DOUBLE() - Forcibly promotes given argument to floating point type
INTEGER() - Forcibly promotes given argument to 64-bit signed type
TO_STRING() - Forcibly promotes the argument to string type
UINT() - Converts the given argument to 32-bit unsigned integer type
UINT64() - Converts the given argument to 64-bit unsigned integer type
SINT() - Interprets 32-bit unsigned integer as signed 64-bit integer

Arrays and conditions

ALL() - Returns 1 if condition is true for all elements in the array
ANY() - Returns 1 if condition is true for any element in the array
CONTAINS() - Checks whether the (x,y) point is within the given polygon
IF() - Checks whether the 1st argument is equal to 0.0, returns the 2nd argument if it is not zero or the 3rd one when it is
IN() - Returns 1 if the first argument is equal to any of the other arguments, or 0 otherwise
INDEXOF() - Iterates through all elements in the array and returns index of the first matching element
INTERVAL() - Returns index of the argument that is less than the first argument
LENGTH() - Returns number of elements in MVA
REMAP() - Allows to make some exceptions of expression values depending on the condition values

Date and time

NOW() - Returns current timestamp as an INTEGER
CURTIME() - Returns current time in local timezone
CURDATE() - Returns current date in local timezone
UTC_TIME() - Returns current time in UTC timezone
UTC_TIMESTAMP() - Returns current date/time in UTC timezone
SECOND() - Returns integer second from the timestamp argument
MINUTE() - Returns integer minute from the timestamp argument
HOUR() - Returns integer hour from the timestamp argument
DAY() - Returns integer day from the timestamp argument
MONTH() - Returns integer month from the timestamp argument
QUARTER() - Returns the integer quarter of the year from a timestamp argument
YEAR() - Returns integer year from the timestamp argument
DAYNAME() - Returns the weekday name for a given timestamp argument
MONTHNAME() - Returns the name of the month for a given timestamp argument
DAYOFWEEK() - Returns the integer weekday index for a given timestamp argument
DAYOFYEAR() - Returns the integer day of the year for a given timestamp argument
YEARWEEK() - Returns the integer year and the day code of the first day of current week for a given timestamp argument
YEARMONTH() - Returns integer year and month code from the timestamp argument
YEARMONTHDAY() - Returns integer year, month and day code from the timestamp argument
TIMEDIFF() - Returns difference between the timstamps
DATEDIFF() - Returns the number of days between two given timestamps
DATE() - Formats the date part from a timestamp argument
TIME() - Formats the time part from a timestamp argument
DATE_FORMAT() - Returns a formatted string based on the provided date and format arguments

Geo-spatial

GEODIST() - Computes geosphere distance between two given points
GEOPOLY2D() - Creates a polygon that takes in account the Earth's curvature
POLY2D() - Creates a simple polygon in plain space

String

CONCAT() - Concatenates two or more strings
REGEX() - Returns 1 if regular expression matched to string of attribute and 0 otherwise
SNIPPET() - Highlights search results
SUBSTRING_INDEX() - Returns a substring of the string before the specified number of delimiter occurs
*

Other

CONNECTION_ID() - Returns the current connection ID
KNN_DIST() - Returns KNN vector search distance
LAST_INSERT_ID() - Returns ids of documents inserted or replaced by last statement in the current session

Common settings in configuration file

To be put to section common {} in configuration file:

lemmatizer_base - Lemmatizer dictionaries base path
progressive_merge - Defines order of merging disk chunks in a real-time table
json_autoconv_keynames - Whether and how to auto-convert key names within JSON attributes
json_autoconv_numbers - Automatically detects and converts possible JSON strings that represent numbers into numeric attributes
on_json_attr_error - What to do if JSON format errors are found
plugin_dir - Location for the dynamic libraries and UDFs

Indexer

indexer is a tool to create plain tables

Indexer settings in configuration file

To be put to section indexer {} in configuration file:

lemmatizer_cache - Lemmatizer cache size
max_file_field_buffer - Maximum file field adaptive buffer size
max_iops - Maximum indexation I/O operations per second
max_iosize - Maximum allowed I/O operation size
max_xmlpipe2_field - Maximum allowed field size for XMLpipe2 source type
mem_limit - Indexing RAM usage limit
on_file_field_error - How to handle IO errors in file fields
write_buffer - Write buffer size
ignore_non_plain - To ignore warnings about non-plain tables

Indexer start parameters

indexer [OPTIONS] [indexname1 [indexname2 [...]]]

--all - Rebuilds all tables from the config
--buildstops - Analyzes the table source as if indexing the data, generating a list of indexed terms
--buildfreqs - Adds the frequency count to the table for --buildstops
--config, -c - Specifies the path to the configuration file
--dump-rows - Dumps rows retrieved by SQL source(s) into the specified file
--help - Displays all available parameters
--keep-attrs - Allows reuse of existing attributes when reindexing
--keep-attrs-names - Specifies which attributes to reuse from the existing table
--merge-dst-range - Applies the given filter range during merging
--merge-killlists - Alters kill list processing when merging tables
--merge - Combines two plain tables into one
--nohup - Prevents indexer from sending SIGHUP when this option is enabled
--noprogress - Hides progress details
--print-queries - Outputs SQL queries sent by the indexer to the database
--print-rt - Displays data fetched from SQL source(s) as INSERTs into a real-time table
--quiet - Suppresses all output
--rotate - Initiates table rotation after all tables are built
--sighup-each - Triggers rotation of each table after it's built
-v - Displays indexer version

Table Converter for Manticore v2 / Sphinx v2

index_converter is a tool designed to convert tables created with Sphinx/Manticore Search 2.x into the Manticore Search 3.x table format.

index_converter {--config /path/to/config|--path}

Table converter start parameters

--config, -c - Path to table configuration file
--index - Specifies which table to convert
--path - Sets path containing table(s) instead of the configuration file
--strip-path - Removes path from filenames referenced by table
--large-docid - Allows conversion of documents with ids larger than 2^63
--output-dir - Writes new files in a specified folder
--all - Converts all tables from the configuration file / path
--killlist-target - Sets target tables for applying kill-lists

Searchd

searchd is the Manticore server.

Searchd settings in a configuration file

To be put in the searchd {} section of the configuration file:

access_blob_attrs - Defines how table's blob attributes file is accessed
access_doclists - Defines how table's doclists file is accessed
access_hitlists - Defines how table's hitlists file is accessed
access_plain_attrs - Defines how search server accesses table's plain attributes
access_dict - Defines how table's dictionary file is accessed
agent_connect_timeout - Remote agent connection timeout
agent_query_timeout - Remote agent query timeout
agent_retry_count - Specifies the number of times Manticore tries to connect and query remote agents
agent_retry_delay - Specifies the delay before retrying to query a remote agent in case of failure
attr_flush_period - Sets the time period between flushing updated attributes to disk
binlog_flush - Binary log transaction flush/sync mode
binlog_max_log_size - Maximum binary log file size
binlog_path - Binary log files path
client_timeout - Maximum time to wait between requests when using persistent connections
collation_libc_locale - Server libc locale
collation_server - Default server collation
data_dir - Path to data directory where Manticore stores everything (RT mode)
docstore_cache_size - Maximum size of document blocks from document storage held in memory
expansion_limit - Maximum number of expanded keywords for a single wildcard
grouping_in_utc - Enables using UTC timezone for grouping time fields
ha_period_karma - Agent mirror statistics window size
ha_ping_interval - Interval between agent mirror pings
hostname_lookup - Hostnames renew strategy
jobs_queue_size - Defines the maximum number of "jobs" allowed in the queue simultaneously
listen - Specifies IP address and port or Unix-domain socket path for searchd to listen on
listen_backlog - TCP listen backlog
listen_tfo - Enables TCP_FASTOPEN flag for all listeners
log - Path to Manticore server log file
max_batch_queries - Limits the number of queries per batch
max_connections - Maximum number of active connections
max_filters - Maximum allowed per-query filter count
max_filter_values - Maximum allowed per-filter values count
max_open_files - Maximum number of files allowed to be opened by server
max_packet_size - Maximum allowed network packet size
mysql_version_string - Server version string returned via MySQL protocol
net_throttle_accept - Defines how many clients are accepted on each iteration of the network loop
net_throttle_action - Defines how many requests are processed on each iteration of the network loop
net_wait_tm - Controls busy loop interval of a network thread
net_workers - Number of network threads
network_timeout - Network timeout for client requests
node_address - Specifies network address of the node
persistent_connections_limit - Maximum number of simultaneous persistent connections to remote persistent agents
pid_file - Path to Manticore server pid file
predicted_time_costs - Costs for the query time prediction model
preopen_tables - Determines whether to forcibly preopen all tables on startup
pseudo_sharding - Enables pseudo-sharding for search queries to plain and real-time tables
qcache_max_bytes - Maximum RAM allocated for cached result sets
qcache_thresh_msec - Minimum wall time threshold for a query result to be cached
qcache_ttl_sec - Expiration period for a cached result set
query_log - Path to query log file
query_log_format - Query log format
query_log_min_msec - Prevents logging too fast queries
query_log_mode - Query log file permissions mode
read_buffer_docs - Per-keyword read buffer size for document lists
read_buffer_hits - Per-keyword read buffer size for hit lists
read_unhinted - Unhinted read size
rt_flush_period - How often Manticore flushes real-time tables' RAM chunks to disk
rt_merge_iops - Maximum number of I/O operations (per second) that real-time chunks merging thread is allowed to do
rt_merge_maxiosize - Maximum size of an I/O operation that real-time chunks merging thread is allowed to do
seamless_rotate - Prevents searchd stalls while rotating tables with huge amounts of data to precache
secondary_indexes - Enables using secondary indexes for search queries
server_id - Server identifier used as a seed to generate a unique document ID
shutdown_timeout - Searchd --stopwait timeout
shutdown_token - SHA1 hash of the password required to invoke shutdown command from VIP SQL connection
snippets_file_prefix - Prefix to prepend to the local file names when generating snippets in load_files mode
sphinxql_state - Path to file where the current SQL state will be serialized
sphinxql_timeout - Maximum time to wait between requests from a MySQL client
ssl_ca - Path to SSL Certificate Authority certificate file
ssl_cert - Path to server's SSL certificate
ssl_key - Path to SSL certificate key of the server
subtree_docs_cache - Maximum common subtree document cache size
subtree_hits_cache - Maximum common subtree hit cache size, per-query
timezone - Timezone used by date/time-related functions
thread_stack - Maximum stack size for a job
unlink_old - Whether to unlink .old table copies on successful rotation
watchdog - Whether to enable or disable Manticore server watchdog

Searchd start parameters

searchd [OPTIONS]

--config, -c - Specifies the path to the configuration file
--console - Forces the server to run in console mode
--coredump - Enables core dump saving upon crash
--cpustats - Enables CPU time reporting
--delete - Removes the Manticore service from Microsoft Management Console and other locations where services are registered
--force-preread - Prevents the server from serving incoming connections until table files are pre-read
--help, -h - Displays all available parameters
--table (--index) - Restricts the server to serve only the specified table
--install - Installs searchd as a service in Microsoft Management Console
--iostats - Enables input/output reporting
--listen, -l - Overrides listen from the configuration file
--logdebug, --logdebugv, --logdebugvv - Enables additional debug output in the server log
--logreplication - Enables extra replication debug output in the server log
--new-cluster - Initializes a replication cluster and sets the server as a reference node with cluster restart protection
--new-cluster-force - Initializes a replication cluster and sets the server as a reference node, bypassing cluster restart protection
--nodetach - Keeps searchd running in the foreground
--ntservice - Used by Microsoft Management Console to launch searchd as a service on Windows platforms
--pidfile - Overrides pid_file in the configuration file
--port, p - Specifies the port searchd should listen on, ignoring the port specified in the configuration file
--replay-flags - Sets additional binary log replay options
--servicename - Assigns the given name to searchd when installing or deleting the service, as displayed in Microsoft Management Console
--status - Queries the running search service to return its status
--stop - Stops the Manticore server
--stopwait - Stops the Manticore server gracefully
--strip-path - Removes path names from all file names referenced in the table
-v - Displays version information

Searchd environment variables

MANTICORE_TRACK_DAEMON_SHUTDOWN - Enables detailed logging during searchd shutdown

Indextool

Assorted table maintenance features helpful for troubleshooting.

indextool <command> [options]

Indextool Start Parameters

Utilized for dumping various debug information related to the physical table.

indextool <command> [options]

--config, -c - Specifies the path to the configuration file
--quiet, -q - Keeps indextool quiet; no banner output, etc.
--help, -h - Lists all available parameters
-v - Displays version information
Indextool - Verifies the configuration file
--buildidf - Builds an IDF file from one or more dictionary dumps
--build-infixes - Builds infixes for an existing dict=keywords table
--dumpheader - Quickly dumps the provided table header file
--dumpconfig - Dumps table definition from the given table header file in a nearly compliant manticore.conf format
--dumpheader - Dumps table header by table name while looking up the header path in the configuration file
--dumpdict - Dumps the table dictionary
--dumpdocids - Dumps document IDs by table name
--dumphitlist - Dumps all occurrences of the given keyword/id in the specified table
--docextract - Runs table check pass on entire dictionary/docs/hits and collects all words and hits belonging to the requested document
--fold - Tests tokenization based on table settings
--htmlstrip - Filters STDIN using HTML stripper settings for the specified table
--mergeidf - Merges multiple .idf files into a single file
--morph - Applies morphology to the provided STDIN and outputs the result to stdout
--check - Checks table data files for consistency
--check-id-dups - Checks for duplicate IDs
--check-disk-chunk - Checks a single disk chunk of an RT table
--strip-path - Removes path names from all file names referenced in the table
--rotate - Determines whether to check a table waiting for rotation when using --check
--apply-killlists - Applies kill-lists for all tables listed in the configuration file

Wordbreaker

Splits compound words into their components.

wordbreaker [-dict path/to/dictionary_file] {split|test|bench}

Wordbreaker start parameters

STDIN - Accepts a string to break into parts
-dict - Specifies the dictionary file to use
split|test|bench - Specifies the command

Spelldump

Extracts the contents of a dictionary file using ispell or MySpell format

spelldump [options] <dictionary> <affix> [result] [locale-name]

dictionary - Main dictionary file
affix - Affix file for the dictionary
result - Specifies the output destination for the dictionary data
locale-name - Specifies the locale details to use

List of reserved keywords

A comprehensive alphabetical list of keywords currently reserved in Manticore SQL syntax (thus, they cannot be used as identifiers).

AND, AS, BY, COLUMNARSCAN, DATE_ADD, DATE_SUB, DAY, DISTINCT, DIV, DOCIDINDEX, EXPLAIN, FACET, FALSE, FORCE, FROM, HOUR, IGNORE, IN, INTERVAL, INDEXES, INNER, IS, JOIN, KNN, LEFT, LIMIT, MINUTE, MOD, MONTH, NOT, NO_COLUMNARSCAN, NO_DOCIDINDEX, NO_SECONDARYINDEX, NULL, OFFSET, ON, OR, ORDER, QUARTER, REGEX, RELOAD, SECOND, SECONDARYINDEX, SELECT, SYSFILTERS, TRUE, WEEK, YEAR

Documentation for old Manticore versions

¶ 28.1