Sphinx search engine and PHP (Part 1 – Installation and Indexing)

In the two part series we will see how to install sphinx, prepare the index and then search the index from our php scripts.

Shpinx is a free open source SQL full-text search engine and for those who might confuse it with the Great Sphinx of Giza,  Sphinx is the acronym for SQL Phrase Index. Some key features of this search engine are (from official site):

  • high indexing speed (upto 10 MB/sec on modern CPUs)
  • high search speed (avg query is under 0.1 sec on 2-4 GB text collections)
  • high scalability (upto 100 GB of text, upto 100 M documents on a single CPU)
  • supports distributed searching (since v.0.9.6)
  • supports MySQL natively (MyISAM and InnoDB tables are both supported)
  • supports phrase searching
  • supports phrase proximity ranking, providing good relevance
  • supports English and Russian stemming
  • supports any number of document fields (weights can be changed on the fly)
  • supports document groups
  • supports stopwords
  • supports different search modes ("match all", "match phrase" and "match any" as of v.0.9.5)
  • generic XML interface which greatly simplifies custom integration
  • pure-PHP (ie. NO module compiling etc) search client API

The instructions given should work with most systems. I have used the following:

  • Sphinx 0.9.9
  • Kubuntu 9.10 (Any *nix OS should work)
  • PHP 5.2.10
  • MySQL 5.1.37

Installation

  1. As with any other application, you first need to download and extract the latest sphinx tar ball.
  2. Go to the root directory of sphinx and issue the ./configure command. I used --prefix=/usr/local/sphinx option to keep all sphinx related files in a single directory. Other important option is --with-mysql which specifies the directory to look for MySQL include and library files. Use this only if auto detection fails.
  3. Build and install by issuing make followed by make install command.
  4. When you are done, all the binaries should be in /usr/local/sphinx/bin directory.

Indexing

Shpinx uses special data structures called as index to facilitate full-text search queries. To build index we can use the indexer utility. We need a configuration file to tell indexer what and how to index. Lets see how to do all of this using a real world example.

Problem:

I have two tables members and addresses and Each member can have many addresses. Lets create an index to hold member names and all addresses so that full text search can be performed on any of those fields. The SQL for the tables is..

PLAIN TEXT
SQL:
  1. CREATE TABLE IF NOT EXISTS `members` (
  2. `id` int(11) NOT NULL AUTO_INCREMENT,
  3. `name` varchar(50) NOT NULL,
  4. `email` varchar(100) NOT NULL,
  5. `phone` int(10) NOT NULL,
  6. `join_date` date NOT NULL,
  7. PRIMARY KEY (`id`)
  8. ) ;
  9.  
  10. CREATE TABLE IF NOT EXISTS `addresses` (
  11. `id` int(11) NOT NULL AUTO_INCREMENT,
  12. `member_id` int(11) NOT NULL,
  13. `address` varchar(255) NOT NULL,
  14. `city` varchar(50) NOT NULL,
  15. `zipcode` varchar(15) NOT NULL,
  16. PRIMARY KEY (`id`)
  17. ) ;

Now create a sphinx configuration file /usr/local/sphinx/etc/sphinx.conf. I have put inline comments to explain each setting.

Note: The example below assumes that you have installed sphinx with --prefix=/usr/local/shpinx. If not then change the paths accordingly.

PLAIN TEXT
CODE:
  1. # Define the source for the data.
  2. source src1
  3. {
  4.     # We will use the xmlpipe2 datasource
  5.     type  = xmlpipe2
  6.    
  7.     # Command which should be executed to get the xml. The following php script outputs the required xml to stdout
  8.     xmlpipe_command  = php /path/to/makeindex.php
  9. }
  10.  
  11. # Define the index.
  12. index members
  13. {
  14.     # Which source to use
  15.     source = src1
  16.    
  17.     # Path where to store the index data
  18.     path = /usr/local/sphinx/var/data/members
  19.    
  20.     # Charset of the data
  21.     charset_type = utf-8
  22.    
  23.     # Minimum lenght of a word to be indexed.
  24.     min_word_len = 3
  25. }
  26.  
  27. # Indexer definition
  28. indexer
  29. {
  30.     # Memory limit for the indexer
  31.     mem_limit = 32M
  32. }

Note: For a full list of options that can be specified in sphinx.conf, please see this

We are using xmlpipe2 datasource type. For this we will create a php script, makeindex.php, which will output the well-formed xml as required by the indexer to stdout. We will be specifying the schema (i.e. the set of fields and attributes) in the xml itself.

We will get the data from members and addresses table and will create the required xml. We will be using PHP 5's native xmlWriter class for this purpose. For brevity i am using mysql_* functions without any error trapping. In your actual code you should put all the error checks. Also the following code is just for illustration purposes.

PLAIN TEXT
PHP:
  1. <?php
  2. // File: /path/to/makeindex.php
  3. $dbh = mysql_connect('localhost', 'user', 'password');
  4. mysql_select_db('dbname');
  5.  
  6. $result = mysql_query("SELECT * FROM members");
  7.  
  8. while($member = mysql_fetch_assoc($result)) {
  9.     $data[$member['id']]['member'] = $member;
  10.     $addressResult = mysql_query("SELECT * FROM addresses WHERE member_id = '{$member['id']}'");
  11.     $data[$member['id']]['addresses'] = array();
  12.     while($address = mysql_fetch_assoc($addressResult)) {
  13.         $data[$member['id']]['addresses'][] = $address;
  14.     }
  15. }
  16.  
  17. $xmlwriter = new xmlWriter;
  18. $xmlwriter->openMemory();
  19. $xmlwriter->setIndent(true);
  20. $xmlwriter->startDocument('1.0', 'UTF-8');
  21. $xmlwriter->startElement('sphinx:docset');
  22.  
  23. $xmlwriter->startElement('sphinx:schema');
  24.  
  25. $xmlwriter->startElement('sphinx:field');
  26. $xmlwriter->writeAttribute("name", "name");
  27. $xmlwriter->endElement();
  28.  
  29. $xmlwriter->startElement('sphinx:field');
  30. $xmlwriter->writeAttribute("name", "addresses");
  31. $xmlwriter->endElement();
  32.  
  33. $xmlwriter->endElement();
  34.  
  35. foreach($data as $id => $member) {
  36.     $xmlwriter->startElement('sphinx:document');
  37.     $xmlwriter->writeAttribute("id", $id);
  38.    
  39.     $xmlwriter->startElement('name');
  40.     $xmlwriter->text($member['member']['name']);
  41.     $xmlwriter->endElement();
  42.    
  43.    
  44.     $xmlwriter->startElement('addresses');
  45.     $buffer = '';
  46.     foreach($member['addresses'] as $address) {
  47.         $buffer .= $address['address'] . ' ' . $address['city'] . ' ' . $address['zipcode'] . ' ';
  48.     }
  49.     $xmlwriter->text($buffer);
  50.     $xmlwriter->endElement();
  51.    
  52.     $xmlwriter->endElement();
  53. }
  54. $xmlwriter->endElement();
  55.  
  56. print $xmlwriter->flush();

Above php script should output xml similar to this.. (you will get different set of data)

PLAIN TEXT
XML:
  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <sphinx:docset>
  3.  <sphinx:schema>
  4.   <sphinx:field name="name"/>
  5.   <sphinx:field name="addresses"/>
  6.  </sphinx:schema>
  7.  <sphinx:document id="1">
  8.   <name>Abbas Ali</name>
  9.   <addresses>Shantinagar Nagpur 440002 Itwari Arcade Nagpur 440001 </addresses>
  10.  </sphinx:document>
  11.  <sphinx:document id="2">
  12.   <name>Amit Badkas</name>
  13.   <addresses>Shivajinagar Dharampeth Nagpur 440024 wadi amravati 440021 </addresses>
  14.  </sphinx:document>
  15. </sphinx:docset>

Now to run the indexer issue the following command

/usr/local/sphinx/bin/indexer --all

This will create indexes for all the index defined in sphinx configuration file.

To test whether the index got created or not issue the following command

/usr/local/sphinx/bin/search searchterm

Replace searchterm with actual term and it should output the results.

In the next post, we will see how to search the index from a php script.

Update: Part 2 – Searching from PHP

About this entry

You’re currently reading “ Sphinx search engine and PHP (Part 1 – Installation and Indexing) ,” an entry on SANIsoft – PHP for E Biz

Published:
1.11.10 / 10:33am
Category:
HowTo, PHP
Author:
Abbas Ali
RSS for this Author