- Create the file
/usr/local/sphinx/etc/sphinx-ql.confand add the following code:source items { type = mysql sql_host = localhost sql_user = root sql_pass = sql_db = sphinx_conf sql_query = SELECT id, title, content, UNIX_TIMESTAMP(created) AS created FROM items sql_attr_timestamp = created } index items { source = items path = /usr/local/sphinx/var/data/items-ql charset_type = utf-8 } searchd { listen = localhost:9306:mysql41 log = /usr/local/sphinx/var/log/ql-searchd.log query_log = /usr/local/sphinx/var/log/ql-query.log pid_file = /usr/local/sphinx/var/log/ql-searchd.pid } - Create the index by running the
indexerutility:$/usr/local/sphinx/bin/indexer -c /usr/local/sphinx/etc/sphinx-ql.conf --all - Start the
searchddaemon:$/usr/local/sphinx/bin/searchd -c /usr/local/sphinx/etc/sphinx-ql.conf - Connect to the MySQL CLI program:
$mysql -u dbuser pdbpass -h localhost -P 9306 - The previous command will connect to the MySQL server at localhost on port
9306.
- Issue the following query from MySQL CLI:
mysql>SELECT * FROM items WHERE MATCH ('search term'), - The previous query will return the following results:
Firstly, we created a new configuration file to index the items database table. We put the following value for the listen option in searchd section of the configuration.
listen = localhost:9306:mysql41
This line in the configuration enables the MySQL protocol support, and configures Sphinx so that when MySQL client is started at port 9306, it will use the Sphinx as the server.
This new access method is supported in addition to the native Sphinx API. You can specify more than one listen options in the searchd section, so that one uses the native API and other the serves the MySQL:
listen = localhost:9312 listen = localhost:9306:mysql41
We then started the searchd daemon and connected to MySQL CLI. While starting MySQL CLI we used port 9306 , the same port where Sphinx is listening. You would notice that when MySQL gets connected it shows the Sphinx version against the "Server Version", and not the actual MySQL server version. This means that MySQL CLI will now fire queries against Sphinx instead of MySQL server.
After that we fired the following query:
mysql>SELECT * FROM items WHERE MATCH ('search term'),
The query syntax is similar to the MySQL full-text search query syntax. SphinxQL supports the following SQL statements:
- SELECT
- SHOW WARNINGS
- SHOW STATUS
- SHOW META
Let's see the usage of each of these.
The SphinxQL syntax adds several Sphinx specific extensions to the regular SQL syntax. Usage of @ symbol for fields in the index and OPTION clause are few Sphinx specific extensions. In addition, there are a few omissions, such as SphinxQL, which does not support JOINs.
Column (field) names, SQL arbitrary expressions, and star (*) are allowed. Some special names, such as @id and @weight, should be used with a leading @ (at-sign). This requirement will be lifted in future versions:
SELECT @id AS item_id, category_id, (points + 2)
AS totalpoints FROM items WHERE MATCH ('search term'),
The FROM clause should contain the list of indexes to be searched. Multiple index names are enumerated by a comma. This is unlike SQL, where comma in FROM means JOIN:
SELECT * FROM items1, items2 WHERE MATCH ('search term'),
WHERE works for both full-text queries and filters. For filtering, normal comparison operators such as =, !=, <, >, <=, >=, IN(), AND, NOT, and BETWEEN are all supported , and these operators map to filters. Full-text search can be performed using the MATCH() method, which takes the full-text search query as an argument. The full-text query is interpreted according to the full-text query language rules as explained in Chapter 4,Searching.
SELECT * FROM items WHERE created > 1281912160;
This query will get all the documents from the items index where the created (timestamp) attribute has a value greater than 1281912160, which is the timestamp for 16th August 2010:
SELECT * FROM items
WHERE MATCH ('search term') AND created > 1281912160;
This query will get all the documents where full-text fields match 'search term' and the created attribute is greater than 1281912160:
SELECT * FROM items
WHERE MATCH ('@content (hello | world) @title bye'),
This query will search for all documents whose content field matches "hello" or "world", but whose title field does not match "bye".
Currently only single column grouping is supported. However, the column can be a computed expression.
AVG(), MIN(), MAX(), SUM(); functions that are used for aggregating data can be used with either plain attributes or arbitrary expressions as arguments. GROUP BY will add an implicit COUNT(*) in the form of @count column to the result:
SELECT *, AVG(points) AS avgpoints FROM items WHERE created > 1281912160 GROUP BY category_id;
ORDER BY works similar to the SQL ORDER BY clause, with the difference being that ASC and DESC are explicitly required, and only column names are allowed and not expressions:
SELECT * FROM items WHERE MATCH ('search term')
ORDER BY created DESC;
LIMIT works exactly similar to the SQL LIMIT clause. However, as in the Sphinx API, an implicit LIMIT 0,20 is always present by default.
SELECT * FROM items WHERE MATCH ('@content search_term')
LIMIT 50;
SELECT * FROM items WHERE MATCH ('@title search_term')
LIMIT 50, 100;
This Sphinx-specific extension lets you control a number of per-query options. The options and values are given in the following table:
|
Option |
Values |
|---|---|
|
|
None, bm25, proximity_bm25, wordcount, proximity, matchany or fieldmask |
|
|
integer (per query max matches) |
|
|
integer (max found matches threshold) |
|
|
integer (max search time threshold in milliseconds) |
|
Option |
Values |
|
|
integer (distributed retries count) |
|
|
integer (distributed retry delay in milliseconds) |
SELECT * FROM items WHERE MATCH ('search term')
OPTION ranker=bm25, max_matches=5;
This statement is used to get the warning messages produced by the previous query:
mysql>SELECT * FROM items WHERE MATCH ('"search term"/3'),
This query will give the following output:
We searched for "search term" with a quorum threshold value of 3. This gave a result, but with one warning. To retrieve the warning message we can fire the SHOW WARNINGS statement:
SHOW WARNINGS;
And it gives the following output:
This way you can retrieve the warning messages of the latest query.
The following statement shows the performance counters:
SHOW STATUS will show the IO and CPU counters if searchd was started with --iostats and --cpustats switches respectively. The variables returned by SHOW STATUS provide information about the server performance and operation. For example, uptime shows the number of seconds that the server has been running. connections shows the number of attempts that have been made to connect to the server (successful or unsuccessful).
This shows the additional information about the previous query, which includes query time and other statistics:
SELECT * FROM items WHERE MATCH ('test'),
SHOW META;
This query will output the information as shown in the next screenshot:
So, SHOW META gives the same information that we get in the data returned when we use Sphinx Client API and fire a full-text query.