Sphinx , SphinxQL and PHP

In the previous post we saw how to install and configure sphinx. In this post, I will write about SphinxQL and how integration sphinx with PHP.

Starting with SphinxQL

SphinxQL is the part which is liked most about sphinx. What basically sphinx has done is implemented the mysql protocol, so using mysql client we can directly connect with sphinx.
So test this out, run below

mysql -P9306 --protocol=tcp --prompt='sphinxQL> '

After you run this command you get a sphinxQL> prompt. Here you can run sql queries called SphinxQL.
Sphinx mysql server runs on port 9306.
http://sphinxsearch.com/docs/2.2.2/sphinxql-reference.html Here is a list of all queries which you can run.

A basic query would look like

SELECT full_name,brand FROM products_indexed  WHERE cat_id=1 LIMIT 0,10;

Below is the output.
Screenshot 2014-05-03 12.27.57

If you want to run a full text search, the query is

SELECT * FROM products_indexed WHERE MATCH([email protected]_name hello')

There are many options in the select query for which details are given here http://sphinxsearch.com/docs/2.2.2/sphinxql-select.html

Using PHP to Search Sphinx

First to install php sphinx extension run this command

sudo pear install pecl/sphinx

After doing this restart apache server using

sudo /etc/init.d/apache2 restart

check if sphinx is install or not using
php -i | grep sphinx

If sphinx doesn’t show up, you need to add the extension.so file to php configuration files.

For PHP < 5.5 [code] echo "extension=sphinx.so" | sudo tee /etc/php5/conf.d/sphinx.ini [/code] For PHP > 5.5

echo "extension=sphinx.so" | sudo tee /etc/php5/mods-available/sphinx.ini
// Load Sphinx in Apache2 PHP:
sudo ln -s /etc/php5/mods-available/sphinx.ini /etc/php5/apache2/conf.d/20-sphinx.ini
// Load Sphinx in php5-cli:
sudo ln -s /etc/php5/mods-available/sphinx.ini /etc/php5/cli/conf.d/20-sphinx.ini

Again restart apache and check it should be installed now.

If you are getting error
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib/php5/20121212+lfs/sphinx.so’ – /usr/lib/php5/20121212+lfs/sphinx.so: cannot open shared object file: No such file or directory in Unknown on line 0

Solution
1. sudo pear install sphinx
if above doesn’t work then download source file from here http://pecl.php.net/package/sphinx

then execute the commands below

phpize --clean
phpize
./configure
make
make install

If during configure you get an error
configure: error: Cannot find libsphinxclient headers

ERROR: `/tmp/pear/temp/sphinx/configure –with-sphinx’ failed

then do to fix it
sudo apt-get install libsphinxclient-dev
sudo apt-get install libsphinxclient-0.0.1

Once php is installed, then see below

I used library http://foolcode.github.io/SphinxQL-Query-Builder/ to run SphinxQL with PHP.
You can also use normal mysql or mysqli libraries of php. Just execute SphinxQL just like a normal sql query in php.

Download the above package zip file and then write code as below.

Another common error during running sphinxql through php is

mysql_connect(): Server sent charset (0) unknown to the

The solution to this to complie sphinx from source, as given above. Only this time you need to change

Change line 15083 of searchd.cpp from
        "\x00" // server language
to
        "\x21" // server language

\x21 means utf8 and utf8_general_ci

the file searchd.cpp is located in src/ folder.

    require_once (__FILE__).'/sphinx/Foolz/SphinxQL/SphinxQL.php';
    require_once (__FILE__).'/sphinx/Foolz/SphinxQL/Connection.php';
    use Foolz\SphinxQL\SphinxQL;
    use Foolz\SphinxQL\Connection;

    $connSQL = new Connection();
    $connSQL->setConnectionParams('localhost', '9306');
    SphinxQL::forge($connSQL);
            
            
            $sqlQueryString = "SELECT WEIGHT() as w,* FROM category_data WHERE MATCH([email protected] Apple iPhone 5S') AND cat_id=1 LIMIT 10 OPTION ranker = sph04;";
            $result = $connSQL->query($sqlQueryString) ;
            
            echo '<pre>';
            print_r($result);

This will give a desired output.

Manipulate Search Result Priority Using Weight

In many cases you want manipulate the search result order based on the your data rather than just plan simple FTS ordering.
For e.g In an ecommerce website if a user search “Apple Iphone” you would want, in the search result to first return all apple iphone mobiles, rather then apple iphone accessories. You products database would have mobiles, chargers, batteries, covers etc but you would want to give higher priority to mobiles. To do this you need to manipulate weights.

Lets assume a mysql table with columns

product_name
product_category
category_priority

for the column category priority we would give
priority = 1 for mobile,
2 for covers,
3 for chargers

you can change your sql query as

$sqlQueryString = "SELECT WEIGHT() + (100-category_priority)*10 as w,* FROM table_name WHERE MATCH([email protected]_name Apple iPhone 5S') order by w desc LIMIT 10 OPTION ranker = sph04;";

Now this query will ensure that mobiles have higher weight than covers etc.

Performance, Speed and Matching

In my project, i had a table with 5million records. In my experience sphinx worked very well. The indexing process would get over within 30 seconds and index around 5 million records.

Search is also very fast and accurate. Search results are returned in few milli seconds and are very accurate. Unlike MYSQL FTS, sphinx doesn’t return bogus data. It only returns search results with accurate matching or it doesn’t return any data. With mysql fts what i have seen is, it tried to return result even if there is a very inaccurate match. This is not the case with sphinx, which is a very big plus point.

Issues with Searching

Sphinx search has an issue with space matching, e.g

If you table has indexed MotoG 8GB and you are searching “Moto G”. Sphinx doesn’t retun a match.

Also if your table has indexed “Funskool Rubik Cube 3×3” and your search “Funskool Rubiks Cube 3 x 3 – Stickerless” it doesn’t return a match.

This an issue which i faced for which i couldn’t find a solution yet.

Hope you enjoyed my blog on sphinx and found it useful.
http://excellencemagentoblog.com/sphinx-installation-configuration
http://excellencemagentoblog.com/sphinx-sphinxql-php