Home  //  Play

Manticore Faceting

Difficulty: Beginner
Estimated Time: 10 minutes

Manticoresearch - Faceted searching

In this tutorial you will learn how to do faceted search in Manticore Search.

The dataset used in this course is https://data.world/data-society/imdb-5000-movie-dataset (the same as in the CSV import course - https://play.manticoresearch.com/csv/ ).

Manticore Faceting

Step 1 of 4


Faceted search is an augmentation of traditional search that allows user to narrow down search results by applying multiple filters. Once a search is made, the user gets the results, but also a list of facets that allows him to narrow the results. As the user selects one (or more values) of a facet, a new filter is applied to the original query. The facets themselves are in essence aggregations on a specific attribute or expression to extract groups of values. In short, someone can run multiple times a base query to which a group is added. This, of course, may not be efficient because the total query time is multiplied by the numbers of facets.

In Manticore Search there is an optimization that retains the result set of the original query and reuses it for each facet calculation. As the aggregations are applied to already calculated subset of documents they are fast and the total execution time can be in many cases only marginally bigger than the initial query.

You can of course just send all the queries you want in a single multi-query (read more here https://docs.manticoresearch.com/latest/html/searching/multi-queries.html), but if you're looking for a more elegant solution or your client doesn't allow you to do multi-queries there's FACET clause.

A SELECT with one or more FACET clauses will return a multiple result sets, where the first result set is for the main query and the following are the facets.


The FACET clause can be added at the very end of a SELECT statement (including after OPTION clause).

'FACET {expr_list} [BY {expr_list}] [ORDER BY {expr | FACET()} {ASC | DESC}] [LIMIT [offset,] count]'

Each facet can accept an expression - which can be just an attribute or an expression involving attributes (see in next steps). In case a facet needs to be applied to an expression, but the facet should return another expression, the 'BY' clause can be used, where 'BY {expr_list}' is the expression to which the facets will apply.

Facets can also be sorted by an expression or by special function FACET(), which returns the result of the facet expression.

Lastly, it's possible to use LIMIT clause for each facet.

Let's connect to Manticore and try some simple facets on the 'movies' index from the autocomplete course:

mysql -P9306 -h0

SELECT * FROM movies WHERE MATCH('robert de niro') LIMIT 10 FACET title_year FACET content_rating FACET director_name;

By default facets results are not sorted and are also limited to 20 rows.

When performing facets we should have in mind how many groups a facet can have and set a reasonable limit for those that can exceed default limit if we want to retrieve them all.

SELECT * FROM movies WHERE MATCH('robert de niro') LIMIT 0 FACET title_year LIMIT 40 FACET content_rating FACET director_name ORDER BY COUNT(*) DESC;

Expressions in FACETS

Sometimes facets must not only show values of attributes. For example performing a facet on price of products will bring up a lot of groups. Another case is for movies where we have an IMDB score, which is a float number. In these situations we can use instead ranges of values.

SELECT * FROM movies WHERE MATCH('robert de niro') LIMIT 0 FACET title_year FACET content_rating FACET director_name FACET INTERVAL(imdb_score,1.0,2.0,3.0,4.0,5.0,6.0,7.0,8.0,9.0) AS score ORDER BY score DESC;

For our movies collection we use interval to get values within integer range for the IMDB score.

Facet by one, display another

Facets can also be grouped on a specific attribute or expression, but at display and sorting to use another attribute.

In the below example we group by director_name, but show also director_facebook_likes and sort the facet result by director_facebook_likes:

SELECT * FROM movies WHERE MATCH('robert de niro') LIMIT 10 FACET director_facebook_likes,director_name BY director_name ORDER BY director_facebook_likes DESC;