Guide to using Sphinx with PHP and MySQL

Question

I'm looking for a complete guide to using Sphinx with PHP and MySQL. I'd like one that's a bit simpler and easygoing than the one provided on the site.

I'm looking for a few concepts on how exactly it all works.

I have a server with PHP, HTML, other data and a MySQL database. How would I go about setting up Sphinx to power the search and results being returned?

I'd like to be able to pass my search terms to my PHP script and have it deal with Sphinx and return the data.

P.S. I'm also open to suggestion regarding any other alternatives to Sphinx.

Answer 1

I came across this post but didn't find an answer I wanted to see. So here is my Quick Start Guide:

1. Install Sphinx

On Mac with Homebrew:

brew install sphinx

On Amazon Linux (CentOS) with yum:

yum install sphinx

2. Create Sphinx config

Sphinx comes with config template. Look for sphinx.conf.dist in the configs directory:

On Mac installed with Homebrew:

/usr/local/Cellar/sphinx/<sphinx version>/etc

On Amazon Linux installed with yum:

/etc/sphinx

It is pretty straightforward but might contain too many settings for a newbie. In such case you can use this simple config:

source TestSource {
    type = mysql
    sql_host = <host>
    sql_user = <user>
    sql_pass = <password>
    sql_db = <db>

    sql_query_range = select min(id), max(id) from TestTable
    sql_range_step = 2048

    sql_query = select id, some_info from TestTable\
        where id >= $start and id <= $end
}

index TestIndex {
    source = TestSource
    path = /var/lib/sphinx/test-index
    min_word_len = 3
    min_infix_len = 3
}

searchd {
    log = /var/log/sphinx/searchd.log
    query_log = /var/log/sphinx/query.log
    pid_file = /var/run/searchd.pid

    max_matches = 200

    listen = localhost:9312
}

I added max_matches setting to this config because my first question after I got everything working was "Why do I always get only 20 search results?". With max_matches you can set the limit for search results number.

3. Create index using indexer

indexer --all

4. Run Sphinx daemon

sudo searchd -c /path/to/config/sphinx.conf

5. Install PHP Sphinx extension

On Mac with Homebrew:

brew install homebrew/php/php56-sphinx

On Amazon Linux with yum:

yum install libsphinxclient
pecl install sphinx

6. Query your index from PHP

$index = new SphinxClient();
$index->setServer("127.0.0.1", 9312);

$result = $index->query('some search term', 'TestIndex');

print_r($result);

In case of any errors you can get more information with the following method:

$index->getLastError();

7. Keep up to date index

To maintain an up to date index you can use two indices:

1. Main index, which is not updated often (once per week, month, etc)
2. And delta index, which updates often (every hour, 5 min, etc)

Every time delta index is re-indexed it is merged with the main index

Follow this link http://www.sphinxconsultant.com/sphinx-search-delta-indexing/ to read more about this approach.

Links I found useful:

• http://sphinxsearch.com/docs/current.html
• http://sphinxsearch.com/info/faq/
• http://atlchris.com/1996/working-with-sphinx-search-engine-on-a-lamp-linux-apache-mysql-and-php-stack-server/
• http://www.sphinxconsultant.com/sphinx-search-delta-indexing/
• https://github.com/schmittjoh/php-stubs/tree/master/res/php/sphinx