Using Sphinx to index MySQL data

The database table is as follows

Mysql> select * from Tb_account;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | ciaos | 3 |
| 2 | Stone | 6 |
+----+-------+------+

Installing Sphinx

Yum Install Sphinx libsphinxclient-devel

Configure Sphinx instead of MySQL full-text indexing (/etc/sphinx/sphinx.conf) (detailed configuration can be referred to the Sphinx configuration file for full resolution)

SOURCE ACCOUNT_SRC {type = MySQL Sql_host = localhost Sql_user = root Sql_pass          = sql_db = Test Sql_port = 3306 sql_query_pre = SET NAMES UTF8 sql_query = SELECT * from Tb_account #sql_attr_uint = idx_id sql_field_string = name Sql_attr_uint = Age} Index account_idx {Source = account_src Path =/var/lib/sphinx/account_src docin                  fo = extern Charset_type = utf-8} indexer {mem_limit = 32M} searchd {Listen = 127.0.0.1:9312 Listen = 9306:mysql41 log =/var/log/sphin X/searchd.log Query_log =/var/log/sphinx/query.log read_timeout = 5 Max_ Children = Pid_file =/var/run/sphinx/searchd.pid max_matches = 1 Seamless_rotate = 1 Preopen_indexes = 1 Unlink_old = 1 workers = Threads # for RT-Work Binlog_path =/var/lib/sphinx}

Create a full-scale index

Indexer--config/etc/sphinx/sphinx.conf--all

Start the background Search service

Searchd--config/etc/sphinx/sphinx.conf

Crontab can be set to build indexes on a regular basis

Indexer--config/etc/sphinx/sphinx.conf--rotate Account_idx

Testing, source installation Sphinx PHP Extensions

wget https://pecl.php.net/get/sphinx-1.3.3.tgzPhpize &&/configure && make &&Make Install<?PHP$s=Newsphinxclient; $s->setserver ("localhost", 9312); $s-Setmatchmode (Sph_match_any); $s->setmaxquerytime (3); $result=$s->query ("Stone"); Var_dump($result); ?>

Running PHP test.php

Array (Ten) {
  ["Error"]=>
  string (0) ""
  ["Warning"]=>
  string (0) ""
  ["status"]=>
  int (0)
  ["Fields"]=>
  Array (1) {
    [0]=>
    string (4) "Name"
  }
  ["Attrs"]=>
  Array (2) {
    ["Name"]=>
    Int (7)
    ["Age"]=>
    int (1)
  }
  ["matches"]=>
  Array (1) {
    [2]= >
    Array (2) {
      ["weight"]=>
      int (1)
      ["Attrs"]=>
      Array (2) {
        ["name"]=>
        string (5) "Stone"
        ["Age"]=>
        int (6)}}
  }
  ["Total"]=>
  int (1)
  ["Total_found"]=>
  Int (1)
  ["Time"]=>
  float (0)
  ["Words"]=>
  Array (1) {
    ["stone"]=>
    Array (2) {
      ["Docs"]=>
      int (1)
      ["hits"]=>
      int (1)
}}}

Using Sphinx to index MySQL data