Full Text Search: Sphinx on Ubuntu 14.04
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); ?>