Coreseeksphinx create tables and Indexes

As mentioned above, the installation of coreseeksphinxmmsegmysql and so on will explain how to use it. One, coreseeksphinx start, there will be an extra port, and can log on like mysql, but not login mysql [root @ localhosttank] # mysql-h127.0.0.1-P9306 is not really connected to mysql, but connected to sphinxin

As mentioned above, the installation of coreseek sphsf-mmseg mysql and so on will explain how to use it. 1. After coreseek sphek is started, an additional port is added and can be logged on like mysql, but instead of logging on to mysql [root @ localhost tank] # mysql-h 127.0.0.1-P 9306 // It is not true to connect to mysql, but to sph‑in

As mentioned above, the installation of coreseek sphsf-mmseg mysql and so on will explain how to use it.

1. After coreseek sphek is started, there will be an additional port, and you can log on like mysql, but not to mysql

[Root @ localhost tank] # mysql-h 127.0.0.1-P 9306 // connection to mysql instead of sphsf-indexwelcome to the MySQL monitor. commands end with; or \ g. your MySQL connection id is 1 Server version: 1.11-id64-dev (r2540) Copyright (c) 2000,201 3, Oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. other names may be trademarks of their respectiveowners. type 'help; 'or' \ H' for help. type '\ C' to clear the current input statement. mysql> select * from tank_test where match ('tank '); // This write method, the original sphashes are different. + ------ + -------- + ---------- + ------ + | id | weight | user_id | u_id | + ------ + -------- + ------------ + ------ + | 3 | 2230 | 1311895260 | 62 | 5 | 2230 | 1311895260 | 33 | 4 | 1304 | 1311895262 | 0 | 6 | 1304 | 1311895262 | 34 | + ------ + -------- + ---------- + ------ + 4 rows in set (0.00 sec) mysql> show META; // last retrieved information + --------------- + ------- + | Variable_name | Value | + --------------- + ------- + | total | 3 | total_found | 3 | time | 0.000 | keyword [0] | test | docs [0] | 3 | hits [0] | 5 | + --------------- + ------- + 6 rows in set (0.00 sec) mysql> show tables; // the table here is not actually a real table or a create table, is sphsf-index + -------------- + ------------- + | Index | Type | + -------------- + ------------- + | dist1 | distributed | myorder | local | rt | tank_test | rt | test1 | local | test1stemmed | local | + -------------- + ------------- + 6 rows in set (0.00 sec)

2. Create a sphsf-index

1. Modify/usr/local/sphinx/etc/sphinx. conf.

# Vim/usr/local/sphinx/etc/sphinx. conf // Add index tank_test {type = rt path =/usr/local/sphinx/var/data/rt charset_dictpath =/usr/local/mmseg3/etc/charset_type = zh_cn.utf-8 ngram_len = 0 rt_field = name rt_field = title rt_field = sub_title rt_attr_uint = user_id rt_attr_uint = uid}

Note that rt_field is the retrieval field and rt_attr_uint is the returned field.

2. Restart sphinx.

# pkill -9 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

3. insert and view Data

Mysql> show tables; + -------------- + ------------- + | Index | Type | + ---------------- + ------------- + | dist1 | distributed | rt | tank_test | rt | // new Index | test1 | local | test1stemmed | local | + -------------- + ------------- + 5 rows in set (0.00 sec) mysql> desc tank_test; + ----------- + --------- + | Field | Type | + ----------- + --------- + | id | bigint | name | field | title | field | sub_title | field | user_id | integer | | u_id | integer | + ----------- + --------- + 6 rows in set (0.00 sec) mysql> insert into tank_test values (3, 'tank ', 'tank is tank', 'technical Director ', 1311895260,33); mysql> insert into tank_test values (4, 'tank', 'tank' is tank ', 'technical Director', 1311895262,34); mysql> select * from tank_test where match ('tank '); // The matched search field is rt_field + ------ + -------- + ------------ + ------ + | id | weight | user_id | u_id | // The returned field is rt_attr_uint + ------ + -------- + ------------ + ------ + | 3 | 2230 | 1311895260 | 33 | 4 | 1304 | 1311895262 | 34 | + ------ + -------- + ------------ + ------ + 2 rows in set (0.00 sec)

Id and weight are returned fields of the system.

The index is created here. You can view the newly created tank_test during show tables. You cannot see it by using phpmyadmin or other mysql database connection tools, because it is not a real table at all. Can sphenders use real tables?

3. Create a table and add an index

1. Create a real table and insert data

Create table if not exists 'Orders '('id' int (11) not null AUTO_INCREMENT, 'user _ id' int (11) not null, 'username' varchar (20) not null, 'create _ time' datetime not null, 'product _ name' varchar (20) not null, 'summary 'text not null, primary key ('id ')) ENGINE = InnoDB default charset = utf8 AUTO_INCREMENT = 1; insert into 'Orders '('user _ id', 'username', 'create _ time', 'product _ name ', 'summary ') VALUES ('123', 'zhang san', '2017-08-01 00:24:54', 'tank is tank ', 'technical Director'), ('123 ', 'tank zhang'er ', '2014-08-01 00:24:54', 'tank is tank ', 'technical manager'), ('2014 ', 'tank zhangyi ', '2017-08-01 00:24:54 ', 'tank is tank', 'dnb manager'), ('20170101', 'tank zhang', '2017-08-01 00:24:54 ', 'tank is tank ', 'O & M Director ');

It should be noted that port 3306 is connected to mysql, not port 9306 connected to coreseek sphsf-

2. Modify/usr/local/sphinx/etc/sphinx. conf and add the following content:

source order{ type            = mysql sql_host        = localhost sql_user        = root sql_pass        = sql_db            = test sql_query_pre        = SET NAMES utf8 sql_query        = \ SELECT id, user_id, username, UNIX_TIMESTAMP(create_time) AS create_time, product_name, summary  \ FROM orders sql_attr_uint        = user_id 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}

3. Restart sphinx.

# pkill -9 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

4. Switch to 9306, search and Test

Mysql> show tables; + -------------- + ------------- + | Index | Type | + ---------------- + ------------- + | dist1 | distributed | myorder | local | rt | tank_test | rt | test1 | local | | test1stemmed | local | + -------------- + ------------- + 6 rows in set (0.00 sec) mysql> desc myorder; + -------------- + ----------- + | Field | Type | + -------------- + ----------- + | id | bigint | username | field | product_name | field | summary | field | user_id | integer | | create_time | timestamp | + -------------- + ----------- + 6 rows in set (0.00 sec) mysql> select * from myorder where match ('tank '); + ------ + -------- + ------------ + ------------- + | id | weight | user_id | create_time | + ------ + -------- + ------------ + ------------- + | 5 | 1304 | 1311895262 | 6 | 1407081600 | 1311895263 | 1406823894 | 7 | 1304 | 1311895264 | 1406823894 | 8 | 1304 | 1311895265 | 1406823894 | + ------ + -------- + ------------ + ------------- + 4 rows in set (0.00 sec)

As mentioned above, the installation of coreseek sphsf-mmseg mysql and so on will explain how to use it. 1. After coreseek sphek is started, an additional port is added and can be logged on like mysql, but instead of logging on to mysql [root @ localhost tank] # mysql-h 127.0.0.1-P 9306 // It is not true to connect to mysql, but to sphsf-indexwelcome to the MySQL monitor. commands end with; or \ g. your MySQL connection id is 1 Server version: 1.11-id64-dev (r2540) Copyright (c) 2000,201 3, Oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of Oracle Corporation and/or [...]