Correct ordering of search result set using Sphinx

Once you get results from Sphinx, it is important to maintain the way they are ordered. The typical search process used by Sphinx looks like the following:

Searching API

  • Searches in Sphinx index
  • gets id’s of matching records
  • puts ids into MySQL query to get the rest of the information.



In order to demonstrate this, I have used the following table as my development environment:

`some_table` (
`id` int(10) unsigned NOT NULL auto_increment,
`some_text` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The table was filled by:

mysql> select * from some_table;
+----+----------------+
| id | some_text |
+----+----------------+
| 2 | test text |
| 4 | text test |
| 6 | something else |
| 8 | new row |
| 10 | new text |
| 12 | empty field |
| 14 | old text |
| 16 | result row |
| 18 | another row |
+----+----------------+

The Sphinx config file looks like:

source min
{
type = mysql
sql_host = localhost
sql_user =
sql_pass =
sql_db =
sql_query = select * from some_table;
}

index idx_min
{
path = idx/idx
source = min
}

searchd
{
listen = 39306:mysql41
log = logs/sphinx.log
pid_file = sphinx.pid
}

So, we get the results by using SphinxQL:

mysql> select * from idx_min where match ('text | new') ;
+------+--------+
| id | weight |
+------+--------+
| 10 | 1588 |
| 8 | 1568 |
| 2 | 1520 |
| 4 | 1520 |
| 14 | 1520 |
+------+--------+
5 rows in set (0.00 sec)

If those ids are put directly into the query, the results won’t keep the same order from the source (in this case relevance):

mysql> select * from some_table where id in (10,8,2,4,14);
+----+-----------+
| id | some_text |
+----+-----------+
| 2 | test text |
| 4 | text test |
| 8 | new row |
| 10 | new text |
| 14 | old text |
+----+-----------+

In this situation, the MySQL condition ‘ORDER BY FIELD’ can be helpful:

mysql> select * from some_table where id in (10,8,2,4,14) ORDER BY FIELD(id, 10,8,2,4,14);
+----+-----------+
| id | some_text |
+----+-----------+
| 10 | new text |
| 8 | new row |
| 2 | test text |
| 4 | text test |
| 14 | old text |
+----+-----------+

By using the method outlined in this brief tutorial, you can keep the order of the results.

Leave a comment

Your comment

Notify me of followup comments via e-mail. You can also subscribe without commenting.