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
[edit]SphinxQL 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
[edit]Verify query logging
[edit]Perform 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
[edit]Run 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.