Full Text Search: Sphinx on Ubuntu 14.04

qyjohn 2014-12-05 04:08

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:

<?php
$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);
?>

[返回] [原文链接]