Show Menu
Cheatography
  1. Home >
  2. Cheat Sheets >

Manticore Search SphinxQL Cheat Sheet (DRAFT) by

This is a draft cheat sheet. It is a work in progress and is not finished yet.

Index manipu­lation

ALTER TABLE index {ADD|DROP} COLUMN column­_name [{INTE­GER­|IN­T|B­IGI­NT|­FLO­AT|­BOO­L|M­ULT­I|M­ULT­I64­|JS­ON|­STR­ING}]
Change index structure
ATTACH INDEX diskindex TO RTINDEX rtindex
move data from a regular disk index to a RT index
SHOW TABLES [ LIKE pattern ]
display active indexes
RELOAD INDEX idx [ FROM '/path­/to­/in­dex­_files' ]
rotate specific index
RELOAD INDEXES
rotate indexes
TRUNCATE RTINDEX rtindex
truncate RealTime data
OPTIMIZE INDEX index_name
Optimize a RealTime index
{DESC | DESCRIBE} index [ LIKE pattern ]
lists index columns and their associated types.
FLUSH RAMCHUNK rtindex
forcibly creates a new disk chunk in a RT index
FLUSH RTINDEX rtindex
forcibly flushes RT index RAM chunk contents to disk
SHOW AGENT ['agen­t'|­'in­dex'] STATUS [ LIKE pattern ]
Displays the statistic of remote agents or distri­buted index

DBA commands

SHOW STATUS [ LIKE pattern ]
Display daemon perfor­mance counters
SET [GLOBAL] server­_va­ria­ble­_name = value
SET a global variable
SHOW THREADS [ OPTION column­s=width ]
List active client threads
SHOW [{GLOBAL | SESSION}] VARIABLES [WHERE variab­le_­nam­e='­xxx']
Display global variables
FLUSH HOSTNAMES
Renew IPs associates to agent host names
FLUSH LOGS
Initiate reopen of log files
FLUSH ATTRIBUTES
Flushes all in-memory attribute updates
SHOW PLUGINS
Displays all the loaded plugins and UDFs
CREATE FUNCTION udf_name RETURNS {INT | INTEGER | BIGINT | FLOAT | STRING} SONAME 'udf_l­ib_­file'
installs a user-d­efined function (UDF)
CREATE PLUGIN plugin­_name TYPE 'plugi­n_type' SONAME 'plugi­n_l­ibrary'
Loads the given library (if it is not loaded yet) and loads the specified plugin from it.
DROP FUNCTION udf_name
deinstalls a user-d­efined function (UDF)
DROP PLUGIN plugin­_name TYPE 'plugi­n_type'
Markes the specified plugin for unloading

CALL SNIPPETS

CALL SNIPPE­TS(­data, index, query[, opt_value AS opt_name[, ...]])

CALL SUGGES­T/Q­SUGGEST

CALL QSUGGE­ST(­word, index [,opti­ons])
CALL SUGGES­T(word, index [,opti­ons])
Options:
- limit
- max_edits
- result­_stats
- delta_len
- max_ma­tches
- reject
- result­_line
- non_char

CALL PQ

CALL PQ(data, index[, opt_value AS opt_name[, ...]])
Options:
- docs_json
- docs
- verbose
-query

CALL KEYWORDS

CALL KEYWOR­DS(­text, index [, options])
call keywords( 'que*', 'myindex', 1 as fold_w­ild­cards, 1 as fold_l­emmas, 1 as fold_b­lended, 1 as expans­ion­_limit, 1 as stats);
call keywords(
'que*',
'myindex',
1 as fold_w­ild­cards,
1 as fold_l­emmas,
1 as fold_b­lended,
1 as expans­ion­_limit,
1 as stats);
  

INSERT

INSERT INTO myinde­x(i­d,f­iel­d1,­fie­ld2­,co­l1,­col2) VALUES­(1,­'ti­tle­','­con­ten­t',­10,­100);
INSERT INTO myinde­x(i­d,f­iel­d1,­fie­ld2­,co­l1,­col2) VALUES­(1,­'ti­tle­','­con­ten­t',­10,­100­),(­2,'new title'­,'next conten­t',­50,­100);

REPLACE

REPLACE INTO myinde­x(i­d,f­iel­d1,­fie­ld2­,co­l1,­col2) VALUES­(1,­'ti­tle­','­con­ten­t',­10,­100);

UPDATE

INSERT UPDATE myindex SET col2=200 WHERE id=1;

DELETE

DELETE FROM myindex WHERE id=1

SELECT

Full-text match
SELECT * FROM myindex WHERE MATCH(­'find me')
Simple select
SELECT * FROM myindex
GROUP BY
SELECT * FROM myindex WHERE MATCH(­'...') GROUP BY col1;
GROUP n BY
SELECT * FROM myindex WHERE MATCH(­'...') GROUP 5 BY col1;
WITHIN GROUP ORDER BY
SELECT * FROM myindex WHERE MATCH(­'...') GROUP by col1 WITHIN GROUP BY ORDER by col2 DESC order by col3 ASC
HAVING
SELECT * FROM myindex WHERE MATCH(­'...') GROUP BY col1 HAVING col2>10
ORDER BY
SELECT * FROM myindex WHERE MATCH(­'...') ORDER BY WEIGHT() DESC, col1 ASC
LIMIT
SELECT * FROM myindex LIMIT 10,20
FACET
SELECT * FROM myindex FACET {expr_­list} [BY {expr_­list}] [ORDER BY {expr | FACET()} {ASC | DESC}] [LIMIT [offset,] count]

SELECT OPTION

agent_­que­ry_­timeout
max time in millis­econds to wait for remote queries to complete
boolea­n_s­implify
enables simpli­fying the query to speed it up
comment
user comment that gets copied to a query log file
cutoff
max found matches threshold
field_­weights
per-field user weights for ranking
global_idf
use global statistics from the global_idf file rather than local idf
idf
IDF comput­ation flags
local_df
sum DFs over all the local parts of a distri­buted index
index_­weights
er-index user weights for ranking
max_ma­tches
per-query max matches value
max_qu­ery­_time
max search time threshold, msec
max_pr­edi­cte­d_time
max predicted search time
ranker
any of proxim­ity­_bm25, bm25, none, wordcount, proximity, matchany, fieldmask, sph04, expr, or export
retry_­count
distri­buted retries count
retry_­delay
distri­buted retry delay, msec
revers­e_scan
control the order in which full-scan query processes the row
sort_m­ethod
pq (priority queue, set by default) or kbuffer (gives faster sorting for already pre-sorted data)
rand_seed
nteger seed value for an ORDER BY RAND()
low_pr­iority
runs the query with idle priority
expand­_ke­ywords
expand keywords with exact forms and/or stars

Query Meta and Profiling

SHOW META [ LIKE pattern ]
shows additional meta-i­nfo­rmation about the latest query
SHOW WARNINGS
retrieve the warning produced by the latest query
SHOW PLAN
displays the execution plan of the previous SELECT statement (requires SET profil­ing=1)
SHOW PROFILE
detailed execution profile of the previous SQL statement (requires SET profil­ing=1)
START TRANSA­CTION | BEGIN COMMIT ROLLBACK SET AUTOCOMMIT = {0 | 1}
start, commit or rollback transa­ctions