Extension:SphinxSearch/SphinxQL
SphinxQL is another way of querying Sphinx (Sphinx API being the other). It uses the MySQL 4.1 transport protocol and for this reason no API is required, any MySQL client can be used instead. SphinxQL is an SQL subset, similar to MySQL. The SphinxSearch extension is built on the Sphinx API. SphinxQL is useful for running queries from the command-line to see raw results and data. See From API to SphinxQL and Back Again additional information.
NOTE: These instructions are specific to a Windows installation.
Configuration
editSphinxQL is included in the Sphinx installation so there is nothing else to install, just some configuration to enable access to it.
- Set up SphinxQL by making the following changes to the sphinx.conf file in the # searchd settings:
| Description | Original | Updated |
|---|---|---|
| Add the SphinxQL listener to MySQL. |
# IP address and port on which search daemon will bind and accept
listen = 127.0.0.1:9312
|
# IP address and port on which search daemon will bind and accept
listen = 127.0.0.1:9312
listen = localhost:9306:mysql41
|
| Turn on SphinxQL logging to see the actual SphinxQL queries. |
# all the search queries are logged here
query_log = C:\Wiki\Sphinx\sphinx-2.0.6-release-win32\query.log
|
# all the search queries are logged here
query_log_format = sphinxql
query_log = C:\Wiki\Sphinx\sphinx-2.0.6-release-win32\query.log
|
- Restart the Sphinx service to pick up the changes:
Run (in linux):
searchd --stop
searchd
Usage
editVerify query logging
editPerform a search in the wiki application and verify that the SphinxQL output was logged in the query_log file specified:
/* Sun Sep 22 10:08:42.274 2013 conn 1 wall 0.001 found 2884 */ SELECT * FROM wiki_main,wiki_incremental WHERE MATCH('msis') AND page_namespace=0 AND page_is_redirect=0;
Connect to SphinxQL
edit- Connect to MySQL via the MySQLSphinxQL port:
c:\Wiki\MySQL\MySQL-5.5\bin>mysql -P 9306
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 2.0.6-release (r3473)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
Query via SphinxQL command-line
editRun the SphinxQL from the query_log output file.
mysql> SELECT * FROM wiki_main,wiki_incremental WHERE MATCH('msis') AND page_namespace=0 AND page_is_redirect=0;
+-------+----------------+------------------+--------+----------+
| id | page_namespace | page_is_redirect | old_id | category |
+-------+----------------+------------------+--------+----------+
| 47671 | 0 | 0 | 100135 | |
| 44830 | 0 | 0 | 97294 | |
| 44831 | 0 | 0 | 97295 | |
| 44852 | 0 | 0 | 97316 | |
| 44857 | 0 | 0 | 97321 | |
| 44867 | 0 | 0 | 97331 | |
| 44875 | 0 | 0 | 97339 | |
| 44876 | 0 | 0 | 97340 | |
| 44877 | 0 | 0 | 97341 | |
| 44878 | 0 | 0 | 97342 | |
| 44879 | 0 | 0 | 97343 | |
| 44880 | 0 | 0 | 97344 | |
| 44881 | 0 | 0 | 97345 | |
| 44882 | 0 | 0 | 97346 | |
| 45106 | 0 | 0 | 97570 | |
| 45107 | 0 | 0 | 97571 | |
| 45108 | 0 | 0 | 97572 | |
| 45109 | 0 | 0 | 97573 | |
| 45110 | 0 | 0 | 97574 | |
| 45111 | 0 | 0 | 97575 | |
+-------+----------------+------------------+--------+----------+
20 rows in set (0.01 sec)
The Sphinx weight is not included in the results by default. Tell the query to include the weight in the results:
mysql> SELECT weight(), * FROM wiki_main,wiki_incremental WHERE MATCH('msis') AND page_namespace=0 AND page_is_redirect=0;
+----------+-------+----------------+------------------+--------+----------+
| weight() | id | page_namespace | page_is_redirect | old_id | category |
+----------+-------+----------------+------------------+--------+----------+
| 2632 | 47671 | 0 | 0 | 100135 | |
| 2631 | 44830 | 0 | 0 | 97294 | |
| 2631 | 44831 | 0 | 0 | 97295 | |
| 2631 | 44852 | 0 | 0 | 97316 | |
| 2631 | 44857 | 0 | 0 | 97321 | |
| 2631 | 44867 | 0 | 0 | 97331 | |
| 2631 | 44875 | 0 | 0 | 97339 | |
| 2631 | 44876 | 0 | 0 | 97340 | |
| 2631 | 44877 | 0 | 0 | 97341 | |
| 2631 | 44878 | 0 | 0 | 97342 | |
| 2631 | 44879 | 0 | 0 | 97343 | |
| 2631 | 44880 | 0 | 0 | 97344 | |
| 2631 | 44881 | 0 | 0 | 97345 | |
| 2631 | 44882 | 0 | 0 | 97346 | |
| 2631 | 45106 | 0 | 0 | 97570 | |
| 2631 | 45107 | 0 | 0 | 97571 | |
| 2631 | 45108 | 0 | 0 | 97572 | |
| 2631 | 45109 | 0 | 0 | 97573 | |
| 2631 | 45110 | 0 | 0 | 97574 | |
| 2631 | 45111 | 0 | 0 | 97575 | |
+----------+-------+----------------+------------------+--------+----------+
20 rows in set (0.01 sec)
Unfortunately SphinxQL does not support JOINs so there is no way to pull in the page title in the results. A separate query against the regular MySQL port is required:
mysql> select page_title from page where page_id = 47671;
+--------------------------------+
| page_title |
+--------------------------------+
| My page related to msis |
+--------------------------------+
1 row in set (0.00 sec)
See also
edit- Extension:SphinxSearch
- Extension:SphinxSearch/Page rank shows how to include custom fields (like number of page views) in the Sphinx index and use those fields to better sort the results to break ties with the built-in weights.