Shulou(Shulou.com)06/01 Report--

This article mainly explains "sphinx how to achieve multi-table query", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "sphinx how to achieve multi-table query" bar!

Sql_query = SELECT `id`, 2 AS table_id, `username`, `password`, `email`, `salt`, `from` FROM table_2

["matches"] = >

Array (16) {

[0] = >

Array (3) {

["id"] = >

String (2) "68"

["weight"] = >

String (1) "2"

["attrs"] = >

Array (1) {

["table_id"] = >

String (1) "2"

Okay, I'm bored again, Orz.

The trouser library built before is a single table, and when building the index, we did not consider any subsequent expansion, and some partners said that they wanted to play with multi-table query, so I studied it.

Why not use incremental indexes? Originally, 10G of a table is large enough, and incremental indexes have to be added from time to time & merge indexes. It's just for fun on this machine, so forget it.

Turn to an article about inheritance and overloading in configuration files, which is helpful for adding multiple data sources. Excerpt:

Define the parent class email

Source email {....}

Define all settings of the subclass subemail inheriting from the email class:

Source subemail: email {# can also use inheritance in addition to source,index.}

Settings in email can be overloaded in subclasses

Source subemail: email {sql_host = www.ibos.com.cn # reload host sql_query = SELECT * FROM subemail # reload sql_query statement}

Inheritance is rarely used, but a very practical example is that inheritance comes in handy when many data sources use the same database.

Source setdb {# setdb class only connects to database sql_host = localhost sql_user = root sql_pass = root sql_db = ibos sql_port = 3306} souce email: setdb {# inherits setdb class sql_query = .. # write the query statement directly Instead of writing database information} souce diary: setdb {sql_query =.} souce article: setdb {sql_query =.} souce forum: setdb {sql_query =.}

Then I changed it based on the last configuration file and added another table as the data source.

But when changing the php file, I found a problem: the previous source code used $sql = "select * from table_1 where id in ($ids)", but now it is not easy to do after adding another table.

Because the ID fields of both tables are incremented from 1, it is possible to pull out rows of the same ID in both tables if you use multiple table Union. One solution is to change the self-incrementing starting value of the second table ID to the end value of the first table ID-but this method only works in rare cases.

Then Baidu was fruitless for a long time, and Google could not find it. Later, I changed the search keyword to coreseek indexes in different tables and found a lot of Orz.

According to the answer of this Using Sphinx with PHP with multiple indexes, change the configuration file again:

# Source definition source table_1 {type = mysql sql_host = localhost sql_user = test sql_pass = test sql_db = sed sql_port = 3306 sql_query_pre = SET NAMES utf8 sql_query = SELECT `id`, 1 AS table_id, `username`, `password`, `email` `salt`, `from` FROM table_1 sql_attr_uint = table_id # the value read from SQL must be an integer # sql_attr_timestamp = date_added # the value read from SQL must be an integer As a time attribute sql_query_info_pre = SET NAMES utf8 # command line query, when setting the correct character set sql_query_info = SELECT * WHERE ID=$id # command line query Read raw data information from the database} source table_2: table_1 {sql_query = SELECT `id`, 2 AS table_id, `username`, `password`, `email`, `salt`, `from` FROM table_2} # index definition index table_1 {source = table_1 # corresponding source name path = E:/SQL_DATA/coreseek/var/data/table_1 # Please change to the actual absolute path For example: / usr/local/coreseek/var/... Docinfo = extern mlock = 0 morphology = none min_word_len = 1 ondisk_dict = 1 html_strip = Chinese word segmentation configuration. For more information, please see: http://www.coreseek.cn/products-install/coreseek_mmseg/ # charset_dictpath = / usr/local/mmseg3/etc/ # BSD, set in Linux environment / symbol ending charset_dictpath = E:/SQL_DATA/coreseek/etc/ # Windows environment setting, / symbol ending, it is best to give an absolute path, for example: CRAUR hand, coreseek hand, Etc, etc. Charset_type = zh_cn.utf-8} index table_2: table_1 {source = table_2 path = E:/SQL_DATA/coreseek/var/data/table_2} # Global index definition indexer {mem_limit = 1024M} # searchd Service definition searchd {listen = 9000 read_timeout = 5 max_children = 30 max_matches = 1000 seamless_rotate = 0 preopen_indexes = 0 unlink_old = 1 pid_file = E:/SQL_DATA/coreseek/var/log/searchd_mysql.pid # Please modify it to the absolute path actually used For example: / usr/local/coreseek/var/... Log = E:/SQL_DATA/coreseek/var/log/searchd_mysql.log # Please modify to the absolute path actually used, for example: / usr/local/coreseek/var/... Query_log = E:/SQL_DATA/coreseek/var/log/query_mysql.log # Please modify to the absolute path actually used, for example: / usr/local/coreseek/var/... Binlog_path = # close binlog log}

So it is fine to add a table_id attribute to the returned matches. After indexing, the matches returns a value similar to this:

["matches"] = > array (16) {[0] = > array (3) {["id"] = > string (2) "68" ["weight"] = > string (1) "2" ["attrs"] = > array (1) {["table_id"] = > string (1) "2"} [1] = > array (3) ) {["id"] = > string (3) "350" ["weight"] = > string (1) "2" ["attrs"] = > array (1) {["table_id"] = > string (1) "1"}}

It is important to note that if you have previously registered searchd as a service, remember to change the port.

Finally, change the PHP file used for search (don't blame the dregs code). ):

The Web of Answers function check (form) {if (form.q.value== "") {alert ("Not null!"); form.q.focus (); return false;}} H2 {font-family: Times New Roman, Lucida Handwriting } body {background-image: url (img/bg.jpg) } The Web of Answers full match MD5 match (16 bits) MD5 match (32-bit)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.