Distributed index instance of sphsf-incremental Index

The incremental index is actually the added content. For example, if there are 100 deposits and 10 are earned today, these 10 are incremental distributed indexes. You can understand that you don't have enough money to open a company, you need to borrow money from many people. That is to say, the money is used together. 1. Test the table and data mysqldescsph_counter; + ------------ + --------- + ------

The incremental index is actually the added content. For example, if there are 100 deposits and 10 are earned today, these 10 are incremental distributed indexes. You can understand that you don't have enough money to open a company, you need to borrow money from many people. That is to say, the money is used together. I. Test the table and data mysql desc sph_counter; + ------------ + --------- + ------

The incremental index is actually the added content. For example, if there are 100 deposits and 10 are earned today, the 10 are incremental.

Distributed index, you can understand that if you want to open a company with insufficient money, you need to borrow money from many people. That is to say, the money is used together.

I. test tables and data

Mysql> desc sph_counter; + ------------ + --------- + ------ + ----- + --------- + ------- + | Field | Type | Null | Key | Default | Extra | + ------------ + --------- + ------ + ----- + --------- + ------- + | counter_id | int (11) | NO | PRI | NULL | max_doc_id | int (11) | NO | NULL | + ------------ + --------- + ------ + ----- + --------- + ------- + 2 rows in set (0.00 sec) mysql> desc orders; + -------------- + ------------- + ------ + ----- + --------- + ---------------- + | Field | Type | Null | Key | Default | Extra | + -------------- + ----------- + ------ + ----- + --------- + certificate + | 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; + ---- + ------------ + users + ---------------- + ------------ + | id | user_id | create_time | product_name | summary | + ---- + ------------ + users + -------------- + | 9 | 1311895262 | 00:24:54 | tank is tank | Technical Director | 10 | 1311895263 | 00:24:54 | tank is tank | Technical Manager | 11 | 1311895264 | 00:24:54 | tank is tank | DNB manager | 12 | 1311895265 | 00:24:54 | tank is tank | O & M Director | + ---- + ------------ + --------------------- + ---------------- + -------------- + 4 rows in set (0.00 sec) 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 | Zhang San | 1311895263 | tank Zhang 'er | 1311895264 | tank Zhang Yi | 1311895265 | tank Zhang | + ------------ + ------------ + 4 rows in set (0.00 sec)

Ii. sphworker. conf configuration

Source myorder {type = mysql SQL _host = localhost SQL _user = root SQL _pass = SQL _db = test SQL _query_pre = set names utf8 SQL _query_pre = set session priority = OFF SQL _query_pre = REPLACE INTO sph_counter SELECT 1, MAX (id) FROM orders SQL _query = \ SELECT. id,. user_id, B. username, UNIX_TIMESTAMP (. create_time) AS create_time,. product_name,. summary \ FROM orders a left join users B on. user_id = B. user_id SQL _attr_uint = user_id SQL _field_string = username SQL _field_string = product_name lifecycle = create_time SQL _ranged_throttle = 0 # SQL _query_info = SELECT * FROM orders WHERE id = $ id} source moreorder: myorder {SQL _query_pre = set names utf8 SQL _query_pre = set session query_cache_type = OFF SQL _query = \ SELECT. id,. user_id, B. username, UNIX_TIMESTAMP (. create_time) AS create_time,. product_name,. summary \ FROM orders a left join users B on. user_id = B. user_id where. id> (SELECT max_doc_id FROM sph_counter WHERE counter_id = 1) SQL _attr_uint = user_id} index myorder // primary index {source = myorder path =/usr/local/sphinx2/var/data/myorder docinfo = extern mlock = 0 morphology = none min_word_len = 1 # charset_type = zh_cn.utf-8 html_strip = 1 charset_table = U + ff10 .. U + FF19-> 0 .. 9, 0 .. 9, U + FF41 .. U + FF5A-> .. z, U + ff21 .. U + FF3A-> .. z, .. z-> .. z, .. z, U + 0149, U + 017F, U + 0138, U + 00DF, U + 00FF, U + 00C0 .. U + 00D6-> U + 00E0 .. U + 00F6, U + 00E0 .. U + 00F6, U + 00D8 .. U + 00DE-> U + 00F8 .. U + 00FE, U + 00F8 .. U + 00FE, U + 0100-> U + 0101, U + 0101, U + 0102-> U + 0103, U + 0103, U + 0104-> U + 0105, U + 0105, U + 0106-> U + 0107, U + 0107, U + 0108-> U + 0109, U + 0109, U + 010A-> U + 010B, U + 010B, U + 010C-> U + 010D, U + 010D, U + 010E-> U + 010F, U + 010F, U + 0110-> U + 0111, U + 0111, U + 0112-> U + 0113, U + 0113, U + 0114-> U + 0115, U + 0115, U + 0116-> U + 0117, U + 0117, U + 0118-> U + 0119, U + 0119, U + 011A-> U + 011B, U + 011B, U + 011C-> U + 011D, U + 011D, U + 011E-> U + 011F, U + 011F, U + 0130-> U + 0131, U + 0131, U + 0132-> U + 0133, U + 0133, U + 0134-> U + 0135, U + 0135, U + 0136-> U + 0137, U + 0137, U + 0139-> U + 013A, U + 013A, U + 013B-> U + 013C, U + 013C, U + 013D-> U + 013E, U + 013E, U + 013F-> U + 0140, U + 0140, U + 0141-> U + 0142, U + 0142, U + 0143-> U + 0144, U + 0144, U + 0145-> U + 0146, U + 0146, U + 0147-> U + 0148, U + 0148, U + 014A-> U + 014B, U + 014B, U + 014C-> U + 014D, U + 014D, U + 014E-> U + 014F, U + 014F, U + 0150-> U + 0151, U + 0151, U + 0152-> U + 0153, U + 0153, U + 0154-> U + 0155, U + 0155, U + 0156-> U + 0157, U + 0157, U + 0158-> U + 0159, U + 0159, U + 015A-> U + 015B, U + 015B, U + 015C-> U + 015D, U + 015D, U + 015E-> U + 015F, U + 015F, U + 0160-> U + 0161, U + 0161, U + 0162-> U + 0163, U + 0163, U + 0164-> U + 0165, U + 0165, U + 0166-> U + 0167, U + 0167, U + 0168-> U + 0169, U + 0169, U + 016A-> U + 016B, U + 016B, U + 016C-> U + 016D, U + 016D, U + 016E-> U + 016F, U + 016F, U + 0170-> U + 0171, U + 0171, U + 0172-> U + 0173, U + 0173, U + 0174-> U + 0175, U + 0175, U + 0176-> U + 0177, U + 0177, U + 0178-> U + 00FF, U + 00FF, U + 0179-> U + 017A, U + 017A, U + 017B-> U + 017C, U + 017C, U + 017D-> U + 017E, U + 017E, U + 0410 .. U + 042F-> U + 0430 .. U + 044F, U + 0430 .. U + 044F, U + 05D0 .. U + 05EA, U + 0531 .. U + 0556-> U + 0561 .. U + 0586, U + 0561 .. U + 0587, U + 0621 .. U + 063A, U + 01B9, U + 01BF, U + 0640 .. U + 064A, U + 0660 .. U + 0669, U + 066E, U + 066F, U + 0671 .. U + 06D3, U + 06F0 .. U + 06FF, U + 0904 .. U + 0939, U + 0958 .. U + 095F, U + 0960 .. U + 0963, U + 0966 .. U + 096F, U + 097B .. U + 097F, U + 0985 .. U + 09B9, U + 09CE, U + 09DC .. U + 09E3, U + 09E6 .. U + 09EF, U + 0A05 .. U + 0A39, U + 0A59 .. U + 0A5E, U + 0A66 .. U + 0A6F, U + 0A85 .. U + 0AB9, U + 0AE0 .. U + 0AE3, U + 0AE6 .. U + 0AEF, U + 0B05 .. U + 0B39, U + 0B5C .. U + 0B61, U + 0B66 .. U + 0B6F, U + 0B71, U + 0B85 .. U + 0BB9, U + 0BE6 .. U + 0BF2, U + 0C05 .. U + 0C39, U + 0C66 .. U + 0C6F, U + 0C85 .. U + 0CB9, U + 0CDE .. U + 0e3, U + 0CE6 .. U + 0CEF, U + 0D05 .. U + 0D39, U + 0D60, U + 0D61, U + 0D66 .. U + 0D6F, U + 0D85 .. U + 0DC6, U + 1900 .. U + 1938, U + 1946 .. U + 194F, U + a800 .. U + A805, U + a807 .. U + A822, U + 0386-> U + 03B1, U + 03AC-> U + 03B1, U + 0388-> U + 03B5, U + 03AD-> U + 03B5, U + 0389-> U + 03B7, U + 03AE-> U + 03B7, U + 038A-> U + 03B9, U + 0390-> U + 03B9, U + 03AA-> U + 03B9, U + 03AF-> U + 03B9, U + 03CA-> U + 03B9, U + 038C-> U + 03BF, U + 03CC-> U + 03BF, U + 038E-> U + 03C5, U + 03AB-> U + 03C5, U + 03B0-> U + 03C5, U + 03CB-> U + 03C5, U + 03CD-> U + 03C5, U + 038F-> U + 03C9, U + 03CE-> U + 03C9, U + 03C2-> U + 03C3, U + 0391 .. U + 03A1-> U + 03B1 .. U + 03C1, U + 03A3 .. U + 03A9-> U + 03C3 .. U + 03C9, U + 03B1 .. U + 03C1, U + 03C3 .. U + 03C9, U + 0E01 .. U + 0E2E, U + 0E30 .. U + 0E3A, U + 0E40 .. U + 0E45, U + 0E47, U + 0E50 .. U + 0E59, U + a000 .. U + A48F, U + 4E00 .. U + 9FBF, U + 3400 .. U + 4DBF, U + 20000 .. U + 2A6DF, U + f900 .. U + FAFF, U + 2F800 .. U + 2FA1F, U + 2E80 .. U + 2EFF, U + 2F00 .. U + 2FDF, U + 3100 .. U + 312F, U + 31A0 .. U + 31BF, U + 3040 .. U + 309F, U + 30A0 .. U + 30FF, U + 31F0 .. U + 31FF, U + ac00 .. U + D7AF, U + 1100 .. U + 11FF, U + 3130 .. U + 318F, U + a000 .. U + A48F, U + a490 .. U + A4CF ngram_len = 1 ngram_chars = U + 4E00 .. U + 9FBF, U + 3400 .. U + 4DBF, U + 20000 .. U + 2A6DF, U + f900 .. U + FAFF, U + 2F800 .. U + 2FA1F, U + 2E80 .. U + 2EFF, U + 2F00 .. U + 2FDF, U + 3100 .. U + 312F, U + 31A0 .. U + 31BF, U + 3040 .. U + 309F, U + 30A0 .. U + 30FF, U + 31F0 .. U + 31FF, U + ac00 .. U + D7AF, U + 1100 .. U + 11FF, U + 3130 .. U + 318F, U + a000 .. U + A48F, U + a490 .. U + A4CF} index moreorder: myorder // incremental index {source = moreorder path =/usr/local/sphinx2/var/data/moreorder docinfo = extern mlock = 0 morphology = none min_word_len = 1 # charset_type = zh_cn.utf-8 html_strip = 1 charset_table = U + ff10 .. U + FF19-> 0 .. 9, 0 .. 9, U + FF41 .. U + FF5A-> .. z, U + ff21 .. U + FF3A-> .. z, .. z-> .. z, .. z, U + 0149, U + 017F, U + 0138, U + 00DF, U + 00FF, U + 00C0 .. U + 00D6-> U + 00E0 .. U + 00F6, U + 00E0 .. U + 00F6, U + 00D8 .. U + 00DE-> U + 00F8 .. U + 00FE, U + 00F8 .. U + 00FE, U + 0100-> U + 0101, U + 0101, U + 0102-> U + 0103, U + 0103, U + 0104-> U + 0105, U + 0105, U + 0106-> U + 0107, U + 0107, U + 0108-> U + 0109, U + 0109, U + 010A-> U + 010B, U + 010B, U + 010C-> U + 010D, U + 010D, U + 010E-> U + 010F, U + 010F, U + 0110-> U + 0111, U + 0111, U + 0112-> U + 0113, U + 0113, U + 0114-> U + 0115, U + 0115, U + 0116-> U + 0117, U + 0117, U + 0118-> U + 0119, U + 0119, U + 011A-> U + 011B, U + 011B, U + 011C-> U + 011D, U + 011D, U + 011E-> U + 011F, U + 011F, U + 0130-> U + 0131, U + 0131, U + 0132-> U + 0133, U + 0133, U + 0134-> U + 0135, U + 0135, U + 0136-> U + 0137, U + 0137, U + 0139-> U + 013A, U + 013A, U + 013B-> U + 013C, U + 013C, U + 013D-> U + 013E, U + 013E, U + 013F-> U + 0140, U + 0140, U + 0141-> U + 0142, U + 0142, U + 0143-> U + 0144, U + 0144, U + 0145-> U + 0146, U + 0146, U + 0147-> U + 0148, U + 0148, U + 014A-> U + 014B, U + 014B, U + 014C-> U + 014D, U + 014D, U + 014E-> U + 014F, U + 014F, U + 0150-> U + 0151, U + 0151, U + 0152-> U + 0153, U + 0153, U + 0154-> U + 0155, U + 0155, U + 0156-> U + 0157, U + 0157, U + 0158-> U + 0159, U + 0159, U + 015A-> U + 015B, U + 015B, U + 015C-> U + 015D, U + 015D, U + 015E-> U + 015F, U + 015F, U + 0160-> U + 0161, U + 0161, U + 0162-> U + 0163, U + 0163, U + 0164-> U + 0165, U + 0165, U + 0166-> U + 0167, U + 0167, U + 0168-> U + 0169, U + 0169, U + 016A-> U + 016B, U + 016B, U + 016C-> U + 016D, U + 016D, U + 016E-> U + 016F, U + 016F, U + 0170-> U + 0171, U + 0171, U + 0172-> U + 0173, U + 0173, U + 0174-> U + 0175, U + 0175, U + 0176-> U + 0177, U + 0177, U + 0178-> U + 00FF, U + 00FF, U + 0179-> U + 017A, U + 017A, U + 017B-> U + 017C, U + 017C, U + 017D-> U + 017E, U + 017E, U + 0410 .. U + 042F-> U + 0430 .. U + 044F, U + 0430 .. U + 044F, U + 05D0 .. U + 05EA, U + 0531 .. U + 0556-> U + 0561 .. U + 0586, U + 0561 .. U + 0587, U + 0621 .. U + 063A, U + 01B9, U + 01BF, U + 0640 .. U + 064A, U + 0660 .. U + 0669, U + 066E, U + 066F, U + 0671 .. U + 06D3, U + 06F0 .. U + 06FF, U + 0904 .. U + 0939, U + 0958 .. U + 095F, U + 0960 .. U + 0963, U + 0966 .. U + 096F, U + 097B .. U + 097F, U + 0985 .. U + 09B9, U + 09CE, U + 09DC .. U + 09E3, U + 09E6 .. U + 09EF, U + 0A05 .. U + 0A39, U + 0A59 .. U + 0A5E, U + 0A66 .. U + 0A6F, U + 0A85 .. U + 0AB9, U + 0AE0 .. U + 0AE3, U + 0AE6 .. U + 0AEF, U + 0B05 .. U + 0B39, U + 0B5C .. U + 0B61, U + 0B66 .. U + 0B6F, U + 0B71, U + 0B85 .. U + 0BB9, U + 0BE6 .. U + 0BF2, U + 0C05 .. U + 0C39, U + 0C66 .. U + 0C6F, U + 0C85 .. U + 0CB9, U + 0CDE .. U + 0e3, U + 0CE6 .. U + 0CEF, U + 0D05 .. U + 0D39, U + 0D60, U + 0D61, U + 0D66 .. U + 0D6F, U + 0D85 .. U + 0DC6, U + 1900 .. U + 1938, U + 1946 .. U + 194F, U + a800 .. U + A805, U + a807 .. U + A822, U + 0386-> U + 03B1, U + 03AC-> U + 03B1, U + 0388-> U + 03B5, U + 03AD-> U + 03B5, U + 0389-> U + 03B7, U + 03AE-> U + 03B7, U + 038A-> U + 03B9, U + 0390-> U + 03B9, U + 03AA-> U + 03B9, U + 03AF-> U + 03B9, U + 03CA-> U + 03B9, U + 038C-> U + 03BF, U + 03CC-> U + 03BF, U + 038E-> U + 03C5, U + 03AB-> U + 03C5, U + 03B0-> U + 03C5, U + 03CB-> U + 03C5, U + 03CD-> U + 03C5, U + 038F-> U + 03C9, U + 03CE-> U + 03C9, U + 03C2-> U + 03C3, U + 0391 .. U + 03A1-> U + 03B1 .. U + 03C1, U + 03A3 .. U + 03A9-> U + 03C3 .. U + 03C9, U + 03B1 .. U + 03C1, U + 03C3 .. U + 03C9, U + 0E01 .. U + 0E2E, U + 0E30 .. U + 0E3A, U + 0E40 .. U + 0E45, U + 0E47, U + 0E50 .. U + 0E59, U + a000 .. U + A48F, U + 4E00 .. U + 9FBF, U + 3400 .. U + 4DBF, U + 20000 .. U + 2A6DF, U + f900 .. U + FAFF, U + 2F800 .. U + 2FA1F, U + 2E80 .. U + 2EFF, U + 2F00 .. U + 2FDF, U + 3100 .. U + 312F, U + 31A0 .. U + 31BF, U + 3040 .. U + 309F, U + 30A0 .. U + 30FF, U + 31F0 .. U + 31FF, U + ac00 .. U + D7AF, U + 1100 .. U + 11FF, U + 3130 .. U + 318F, U + a000 .. U + A48F, U + a490 .. U + A4CF ngram_len = 1 ngram_chars = U + 4E00 .. U + 9FBF, U + 3400 .. U + 4DBF, U + 20000 .. U + 2A6DF, U + f900 .. U + FAFF, U + 2F800 .. U + 2FA1F, U + 2E80 .. U + 2EFF, U + 2F00 .. U + 2FDF, U + 3100 .. U + 312F, U + 31A0 .. U + 31BF, U + 3040 .. U + 309F, U + 30A0 .. U + 30FF, U + 31F0 .. U + 31FF, U + ac00 .. U + D7AF, U + 1100 .. U + 11FF, U + 3130 .. U + 318F, U + a000 .. U + A48F, U + a490 .. U + A4CF}

3. insert data and update the incremental Index

1. Insert data

Mysql> insert into users (username) VALUES ', 'I got a xx', 'capture asdfasdfasdf ');

2. Update the incremental Index

/usr/local/sphinx2/bin/indexer --config /usr/local/sphinx2/etc/sphinx.conf --rotate moreorder

After the incremental index is updated, the two new data entries are in the incremental index instead of the primary index.

Update incremental indexes using sphinx_rotate

Mysql> select * from moreorder where match ('zhang '); // In the incremental index, + ------ + ------------ + ----------- + ------------- + -------------- + | id | user_id | username | create_time | product_name | + ------ + ------------ + --------- + ------------ + | 13 | 1311895266 | Zhang Sanfeng | 0 | I got XX | 14 | 1311895267 | Zhang Sifeng | 0 | iasdfasdf | + ------ + ------------ + ----------- + -------------- + 2 rows in set (0.00 sec) mysql> select * from myorder where match ('zhang '); // No primary index + ------ + ------------ + ------------- + ---------------- + | id | user_id | username | create_time | product_name | + ------ + ------------ + ------------- + hour + | 9 | 1311895262 | zhangsan | 1406823894 | tank is tank | 10 | 1311895263 | tank Zhangji | 1406823894 | tank is tank | 11 | 1311895264 | tank zhangyi | 1406823894 | tank is tank | 12 | 1311895265 | tank sheets | 1406823894 | tank is tank | + ------ + ------------ + ------------- + ---------------- + 4 rows in set (0.00 sec)

There are two ways to solve this problem,One uses distributed indexes and the other combines incremental indexes and primary indexes.

4. sphsf-distributed index Configuration

1. Modify sphworker. conf and add the following content.

Index mytest {type = distributed local = myorder // local = moreorder // local # agent = 192.168.10.103: 9313: myuser // remote agent_connect_timeout = 1000 agent_query_timeout = 3000}

Restart sphinx

2. Insert new data and update the incremental index. The root is the same as above.

3. Test the sphinx increment.

Mysql> select * from myorder where match ('zhang '); // new data not added + ------ + ------------ + ------------- + -------------- + | id | user_id | username | create_time | product_name | + ------ + ------------ + ------------- + hour + | 9 | 1311895262 | zhangsan | 1406823894 | tank is tank | 10 | 1311895263 | tank Zhangji | 1406823894 | tank is tank | 11 | 1311895264 | tank zhangyi | 1406823894 | tank is tank | 12 | 1311895265 | tank Zhang | 1406823894 | tank is tank | 13 | 1311895266 | Zhang Sanfeng | 0 | I got XX | 14 | 1311895267 | Zhang Sifeng | 0 | iasdfasdf | + ------ + ------------ + ------------- + ---------------- + 6 rows in set (0.00 sec) mysql> select * from moreorder where match ('zhang '); // Add data in the incremental index + ------ + -------------- + ----------- + ------------- + -------------- + | id | user_id | username | create_time | product_name | + ------ + ------------ + ----------- + -------------- + | 15 | 1311895268 | Zhang Wufeng | 0 | I got XX | 16 | 1311895269 | Zhang Liufeng | 0 | iasdfasdf | + ------ + ------------ + ----------- + -------------- + 2 rows in set (0.00 sec) mysql> select * from mytest where match ('zhang '); // here we can regard mytest as a connection pool + ------ + ------------ + ------------- + certificate + | id | user_id | username | create_time | product_name | + ------ + ------------ + ------------- + ---------------- + | 15 | 1311895268 | Zhang Wufeng | 0 | XX | 16 | 1311895269 | Zhang Liufeng | 0 | iasdfasdf | 9 | 1311895262 | Zhang San | 1406823894 | tank is tank | 10 | 1311895263 | tank Zhangji | 1406823894 | tank is tank | 11 | 1311895264 | tank zhangyi | 1406823894 | tank is tank | 12 | 1311895265 | tank | 1406823894 | tank is tank | 13 | 1311895266 | Zhang sancrazy | 0 | I got XX | 14 | 1311895267 | Zhang sicrazy | 0 | iasdfasdf | + ------ + ------------ + ---------- + ------------- + ---------------- + 8 rows in set (0.00 sec)

5. Merge incremental and primary Indexes

1. Insert new data and update the incremental index. The root is the same as above.

2. Merge incremental and primary Indexes

# /usr/local/sphinx2/bin/indexer --config /usr/local/sphinx2/etc/sphinx.conf --merge myorder moreorder --rotate

3. Test sphinx

Mysql> select * from myorder where match ('zhang'); // when you look at the primary index, there are two new data records + ------ + -------------- + ------------ + ------------- + ---------------- + | id | user_id | username | create_time | product_name | + ------ + ------------ + ---------------- + | 9 | 1311895262 | zhangsan | 1406823894 | tank is tank | 10 | 1311895263 | tank Zhangji | 1406823894 | tank is tank | 11 | 1311895264 | tank zhangyi | 1406823894 | tank is tank | 12 | 1311895265 | tank Zhang | 1406823894 | tank is tank | 13 | 1311895266 | Zhang Sanfeng | 0 | I got XX | 14 | 1311895267 | Zhang Sifeng | 0 | iasdfasdf | + ------ + ------------ + ------------- + ---------------- + 6 rows in set (0.00 sec)

Original article address: sphsf-incremental index distributed index instance. Thank you for sharing it with the original author.