婉兮清扬

Full Text Search: Sphinx on Ubuntu 14.04

发表时间：2014-12-05 04:08:56

---

Below is some quick notes on how to setup a simple PHP based website with full text search capability. The content to be searched is stored in a MySQL database, and the full text search engine is Sphinx.

Step 1 - Launch an EC2 instance on AWS, the operating system is Ubuntu 14.04.

Step 2 - SSH into the EC2 instance, install MySQL server and set up a test database. In this demo, our test database have one table "document" with three columns id, uuid, and content. Sphinx will create a search index based on the information stored in the "document" table.

$ sudo apt-get update

$ sudo apt-get install mysql-server

$ mysql -u root -p

Your MySQL connection id is 43

Server version: 5.5.40-0ubuntu0.14.04.1 (Ubuntu)



Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.



Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.



Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.



mysql> create database test;

Query OK, 1 row affected (0.00 sec)



mysql> use test;

Database changed

mysql> create table document (id int, uuid varchar(80), content text);

Query OK, 0 rows affected (0.01 sec)



mysql> insert into document (id, uuid, content) values (1, 1234567890, 'cloud computing');

Query OK, 1 row affected (0.00 sec)



mysql> insert into document (id, uuid, content) values (2, 1234567891, 'expensive cloud computing');

Query OK, 1 row affected (0.01 sec)



mysql> insert into document (id, uuid, content) values (3, 1234567892, 'green expensive cloud computing');

Query OK, 1 row affected (0.00 sec)



mysql> insert into document (id, uuid, content) values (4, 1234567893, 'scheduling algorithm green expensive cloud computing');

Query OK, 1 row affected (0.00 sec)



mysql> exit

Bye

Step 3 - Install Sphinx

$ sudo apt-get install sphinx search

$ cd /etc/sphinxsearch

$ sudo cp sphinx.conf.sample sphinx.conf

Edit /etc/sphinxsearch/sphinx.conf to reflect your MySQL username and password. Also, find the following SQL statement

       sql_query               = \

               SELECT id, group_id, UNIX_TIMESTAMP(date_added) AS date_added, title, content \

               FROM documents

and replace it with the following SQL statement (because in our test database, our "document" table only contains three columns: id, uuid, and content.

        sql_query               = \

                SELECT id, uuid, content FROM document

Also, comment out the following two lines in /etc/sphinx/sphinx.conf, because we do not have these two columns (group_id, date_added) in our test database:

# sql_attr_uint         = group_id

# sql_attr_timestamp    = date_added

Then, edit /etc/default/sphinxsearch, change "START=no" to "START=yes". Then start sphinx:

$ sudo indexer --all 

$ sudo service sphinxsearch start

STEP 4 - Install Apache and PHP

$ sudo apt-get install apache2 php5 php5-mysql

$ cd /var/www

$ sudo chown -R ubuntu:ubuntu html

Step 5 - A Quick Demo PHP Page

Create the following PHP page /var/www/html/sphinx.php, with the following content:

$conn = new mysqli('127.0.0.1', "", "", "", 9306);

if ($conn->connect_error) 

{

        throw new Exception('Connection Error: ['.$conn->connect_errno.'] '.$conn->connect_error, $conn->connect_errno);

}

 

$resource = $conn->query("select * from test1 where match('green computing')");

$results = array();

while ($row = $resource->fetch_assoc()) 

{

        $results[] = $row;

}

$resource->free_result();

 

var_dump($results);

?>

上一篇

下一篇