Several examples of Sphinx implementation Joint query

Remember the previous Sphinx is not supporting the joint query, the first contact Sphinx, as if at the beginning of 2010, when I wrote an article about Sphinx installation. Sphinx mmseg MySQL Chinese participle, the following examples illustrate the Sphinx of federated query usage.

One, add two test tables and data

The code is as follows

Copy Code

1,users Tables and data mysql> desc users; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | user_id | Int (11) | NO | PRI | NULL | auto_increment | | Username | varchar (20) | NO | | NULL | | +----------+-------------+------+-----+---------+----------------+ 2 rows in Set (0.00 sec) Mysql> select * from users; +------------+------------+ | user_id | Username | +------------+------------+ | 1311895262 | John | | 1311895263 | Tank Zhang Yi | | 1311895264 | Tank Zhang | | 1311895265 | Tank Zhang | +------------+------------+ 4 rows in Set (0.00 sec) 2,orders Tables and data mysql> desc orders; +--------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+----------------+ | ID | Int (11) | NO | PRI | NULL | auto_increment | | user_id | Int (11) | NO | | NULL | | | Create_time | datetime | NO | | NULL | | | Product_Name | varchar (20) | NO | | NULL | | | Summary | Text | NO | | NULL | | +--------------+-------------+------+-----+---------+----------------+ 5 rows in Set (0.00 sec) Mysql> select * from Orders; +----+------------+---------------------+----------------+--------------+ | ID | user_id | Create_time | Product_Name | Summary | +----+------------+---------------------+----------------+--------------+ | 9 | 1311895262 | 2014-08-01 00:24:54 | Tank is Tank | Technical Director | | 10 | 1311895263 | 2014-08-01 00:24:54 | Tank is Tank | Technical Manager | | 11 | 1311895264 | 2014-08-01 00:24:54 | Tank is Tank | DNB Manager | | 12 | 1311895265 | 2014-08-01 00:24:54 | Tank is Tank | Director of operation and Maintenance | +----+------------+---------------------+----------------+--------------+ 4 rows in Set (0.00 sec)

Second, configure sphinx.conf

The code is as follows

Copy Code

SOURCE Order { Type = MySQL Sql_host = localhost Sql_user = root Sql_pass = sql_db = Test Sql_query_pre = SET NAMES UTF8 Sql_query =\ SELECT a.id, A.user_id,b.username, Unix_timestamp (a.create_time) as Create_time, A.product_name, a.summary\ From the orders a LEFT join the users b on a.user_id = b.user_id Sql_attr_uint = user_id Sql_field_string = Username sql_field_string = Product_Name Sql_attr_timestamp = Create_time Sql_ranged_throttle = 0 Sql_query_info = SELECT * FROM orders WHERE id= $id } Index Myorder { Source = Order Path =/usr/local/sphinx/var/data/myorder DocInfo = extern Mlock = 0 Morphology = None Min_word_len = 1 Charset_dictpath =/usr/local/mmseg3/etc/ Charset_type = Zh_cn.utf-8 Ngram_len = 0 Html_strip = 0 }

Note: here a.user_id = b.user_id, equals two sides must have a space, otherwise it will be an error.
Third, restart Sphinx

The code is as follows	Copy Code
# Pkill Searchd #/usr/local/sphinx/bin/indexer--config/usr/local/sphinx/etc/sphinx.conf--all #/usr/local/sphinx/bin/searchd--config/usr/local/sphinx/etc/sphinx.conf

Four, Test Sphinx

The code is as follows

Copy Code

[Root@localhost etc]# mysql-h 127.0.0.1-p 9306//Login sphinx,9306 port, not true MySQL Welcome to the MySQL Monitor. Commands End With; Or\g. Your MySQL Connection ID is 1 Server Version:1.11-id64-dev (r2540) Copyright (c), 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark the Oracle Corporation and/or its Affiliates. Names may trademarks of their respective Owners. Type ' help, ' or ' \h ' for help. Type ' \c ' to clear the current input statement. Mysql> SELECT * from Myorder where match (' Zhang '); +------+--------+------------+------------+-------------+----------------+ | ID | Weight | user_id | Username | Create_time | Product_Name | +------+--------+------------+------------+-------------+----------------+ | 9 | 1304 | 1311895262 | John | 1406823894 | Tank is Tank | | 10 | 1304 | 1311895263 | Tank Zhang Yi | 1406823894 | Tank is Tank | | 11 | 1304 | 1311895264 | Tank Zhang | 1406823894 | Tank is Tank | | 12 | 1304 | 1311895265 | Tank Zhang | 1406823894 | Tank is Tank | +------+--------+------------+------------+-------------+----------------+ 4 rows in Set (0.01 sec) Mysql> SELECT * from Myorder where match (' John '); +------+--------+------------+----------+-------------+----------------+ | ID | Weight | user_id | Username | Create_time | Product_Name | +------+--------+------------+----------+-------------+----------------+ | 9 | 2500 | 1311895262 | John | 1406823894 | Tank is Tank | +------+--------+------------+----------+-------------+----------------+ 1 row in Set (0.00 sec)