Preserving the order of search result set from Manticore Search

Between the Manticore query and the final result to the user there can be additional processing. As in most cases, the interest is to sort by a relevance score, it's important to not lose this sorting.

A typical workflow using Manticore Search along with a database looks like this:

  • perform Manticore query
  • from result set get the list of document ids
  • perform a database query using the document ids to get final result for output

Getting the corresponding records from the database can be made in a single query using the  IN(ids) predicate.


mysql> SELECT * FROM mytable WHERE id IN(20,10,3,4,6)

This type of query is  very fast, but the result set may not be in the order we would expect. Since there is no ordering set, the database engine will simply add the records to the result set as soon as it finds them while searching them in the table. This means we need to add explicit ordering to the query to preserve the same order we received from the Manticore query.

In MySQL we can use FIELD()  function to sort the set the way we want:

mysql> SELECT * FROM mytable WHERE id IN(20,10,3,4,6) ORDER BY FIELD(id,20,10,3,4,6).

Similar methods exists in other databases. In PostgreSQL it can be done in several ways, like using  ORDER BY CASE, WITH ORDINALITY (9.4+) or array_position() (9.6+).

As alternative, keeping the order from the Manticore result can also be made in the application code. A simplified code can look like this:


$ids = [];
foreach($ms_conn($manticore_query as $row)
{
   $ids[] = $row['id'];
}
$tmpdocs = [];
foreach($db_conn->query($database_query) as $row)
{
   $tmpdocs[$row['id']] = $row
}
$final_result = [];
foreach($ids as $id)
{
   $final_result[] = $tmpdocs[$id];
}

As conclusion, if you retrieve data from a repository using ids from a Manticore query make sure to not lose the order provided by the Manticore query result set.

 The article is based on"Correct ordering of search result set using Sphinx" by Nikita https://www.ivinco.com/blog/correct-ordering-of-search-result-set-using-sphinx/ and publication is authorized by the owner

Leave a Reply

Training

Personal and team training will maximize them performance. 

Custom development

Need cone custom or individual features?

Fill the form and don’t forget to make the description of what you need.

Free config review

There are often optimizations that can be made to a Sphinx / Manticore setup by changing some simple directives in the configuration or making quick changes to an index definition.

Some common mistakes and issues can include:

  • doing main+delta without kill-lists, even if the delta does include updated records found in the main
  • using wildcarding with very short prefix/infix which can hammer performance in some cases
  • disabled (unintentional) seamless rotates and getting stalls on index rotations
  • adding texts as string attributes even if they are not using for any kind of operation (filtering, grouping, sorting) or mandatory to be present in results
  • using deprecated settings 

Having a quick look on the configuration can show issues or potential issues, this is why we want to offer a gift to our growing community!

When uploading your configuration file, we recommend to remove any database credentials first.

We suggest also you give as many possible details about your setup: how big is the data you have, how typical queries look and what issues you experience.

Contact us