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.

---

Need help with search infrastructure? Talk to our engineering team — we've built and maintained search systems for over 15 years.

Frequently Asked Questions

How do you maintain Sphinx result ordering in MySQL?

Use MySQL's ORDER BY FIELD(id, id1, id2, ...) clause when fetching full records from the database. Sphinx returns document IDs ordered by relevance, but a standard WHERE id IN (...) query returns results in primary key order. ORDER BY FIELD forces MySQL to match the exact sequence Sphinx returned, preserving relevance ranking.

Why does MySQL change the order of Sphinx search results?

When you pass Sphinx result IDs to a MySQL WHERE id IN (...) query, MySQL returns rows in primary key order by default — not the order you specified. Sphinx ranks results by relevance weight, so this reordering destroys your search quality. The fix is adding ORDER BY FIELD(id, ...) to your SQL query.

What is SphinxQL and how does it differ from the Sphinx API?

SphinxQL is a SQL-like interface for querying Sphinx directly on port 9306 using standard MySQL protocol. It replaced the older native API for most use cases. SphinxQL supports SELECT, INSERT, REPLACE, and DELETE operations, letting you query Sphinx indexes with familiar SQL syntax through any MySQL client library.

Related Reading

• Using Sphinx search engine with Chinese, Japanese, and Korean language documents
• Announcing DokuWiki Sphinx Search Plugin
• High performance BuildExcerpts() with Sphinx Search