TheFACETFeature_MySQL

FACET

You may have readthis article about faceted search with Sphinx. Forget about it. Things have changed. We’ve implemented aFACETkeyword which will help you write very short facet queries. If you’re not sure what faceted search is, go check out eBay (or, see the example we link to at the bottom of this post). It provides an excellent example of faceted search– your search is supplemented with categories that allow you to drill down into specific “facets” of the result set.

Old vs. New

Previously, with Sphinx, doing something like this required several requests with a specific groupby in each request. Sphinx optimized those using the multi-queries feature.

The new feature is similar to the ol’ multi-queries technique, but uses a new keyword with its own expressions, aliases (i.e. it’s possible to writeFACET x AS y) and it returns multiple result sets per single request (as a bonus you don’t have to mess with delimiters, i.e. you had to write “mysql --delimiter=# -P 9306 -e "select ...; select ...; select ...;” to get working multi-queries, otherwise the MySQL client sends 3 separate statements).

So the whole thing becomes essentially ONE single compact query which optimizes search automatically and fetches all result sets in a single pass.

Old syntax (3 result sets):

SELECT * from facetdemo limit 0,10; SELECT GROUPBY(), COUNT(*) FROM facetdemo GROUP BY categories; SELECT GROUPBY(), COUNT(*) FROM facetdemo GROUP BY brand_id;

New syntax (3 result sets):

SELECT * FROM facetdemo LIMIT 0,10 FACET categories FACET brand_id;

And, here’s a quick example with PHP demonstrating how FACET queries can be run via the standard PDO or the mysqli multi-query API:

$query = "SELECT * FROM facetdemo FACET categories FACET brand_id"; $pdo = new PDO('mysql:host=127.0.0.1;port=9306');$stmt = $pdo->query($query);do {$rowset = $stmt->fetchAll();print_r ($rowset);} while ($stmt->nextRowset()); $mysqli = new mysqli('127.0.0.1', 'test', '', '', 9306);if ($mysqli->multi_query($query)){	do	{		if ($result = $mysqli->store_result())		{			while ($row = $result->fetch_row())				print_r($row);			$result->free();		}	}	while ($mysqli->more_results() && $mysqli->next_result());}

Faceted search in action

Take a look atAdrian’s faceting example to get a feel for how it works. He provides examples of both the old and new styles. Then, go take a look atthe codeused to produce this example. Adrian has it on github.

Here’s a preview of the example:

Give it a try! Grab 2.2.3-betahere. If you need some help,contact us– we provide support. And, if you have questions, please ask them.

Happy Sphinxing!

Tags:faceted search,featured,PHP