Group by MVA in SphinxQL
Sergey Nikolaev··Updated When you GROUP BY a multi-value attribute (MVA) in SphinxQL, the column you select still shows all values from a representative row in each group — not the value Sphinx actually grouped by. To see the real grouping key, add the @groupby virtual column to your select list. This is the only way to confirm which MVA value defined each bucket.
This is a quick note since the behavior can be confusing. Starting v 2.0.1-beta there's a compat_sphinxql_magics directive which makes SphinxQL similar to standard SQL — but MVA grouping still differs from what you'd expect. If you have an MVA attribute tags with some integers and want to group by it, your first instinct is probably this:
mysql> select tags, count(*) c from idx where match('word') group by tags order by c desc limit 10;
+--------------+------+
| tags | c |
+--------------+------+
| 210,348 | 366 |
| 204 | 116 |
| 206 | 73 |
| 132,348 | 71 |
| 210,348 | 40 |
| 29 | 36 |
| 25,29,270 | 30 |
| 208 | 28 |
| 180 | 24 |
| 25,348 | 23 |
+--------------+------+
10 rows in set (0.00 sec)
But as you can see the first column contains few values instead of one that you want. To see the needed value used by Sphinx for grouping you need to use special @groupby word:
mysql> select @groupby, tags, count(*) c from idx where match('word') group by tags order by c desc limit 10;
+----------+--------------+------+
| @groupby | tags | c |
+----------+--------------+------+
| 348 | 210,348 | 366 |
| 204 | 204 | 116 |
| 206 | 206 | 73 |
| 132 | 132,348 | 71 |
| 210 | 210,348 | 40 |
| 29 | 29 | 36 |
| 270 | 25,29,270 | 30 |
| 208 | 208 | 28 |
| 180 | 180 | 24 |
| 25 | 25,348 | 23 |
+----------+--------------+------+
10 rows in set (0.00 sec)
Now it's clear that the 'tags' was not changed during grouping at all, this is just what you have in one record our of the group while the value which the dataset was grouped by can be seen in the @groupby column.
Hope this saves you some time when you hit this.
Need help tuning Sphinx or migrating off it to a modern search backend? See our development services.
Frequently Asked Questions
Why does GROUP BY on an MVA attribute return multiple values in the same row?
Sphinx stores MVA attributes as arrays, so the column you select shows the full array for one document in each group — not the value used for grouping. The actual grouping key is hidden in the internal @groupby column. This is intentional behavior, not a bug.
How do I see the value Sphinx grouped by when using GROUP BY on an MVA?
Add @groupby to your SELECT list: SELECT @groupby, tags, COUNT(*) FROM idx WHERE MATCH('word') GROUP BY tags. The @groupby column will show the integer value Sphinx used to define each bucket, while tags still shows the multi-value array from a representative document.
Does compat_sphinxql_magics fix the MVA GROUP BY behavior?
No. The compat_sphinxql_magics directive (introduced in 2.0.1-beta) makes SphinxQL closer to standard SQL for many edge cases, but MVA grouping still requires the @groupby virtual column. MVAs are a Sphinx-specific concept and standard SQL has no equivalent.
Can I use GROUP BY on multiple MVA attributes at once?
Sphinx supports grouping by a single attribute per query. To analyze combinations across multiple MVAs, you typically run separate queries and combine the results in application code, or use facets in newer Sphinx/Manticore versions which let you pivot multiple groupings in one request.
Related Reading
Related posts
Massive Indexing with Sphinx
Imagine the following situation: multiple Sphinx daemons are serving huge amount of data running on numerous hardware servers.
Sphinx: building 1M docs index having no one real doc
Just want to share an interesting trick on how to easily index something with Sphinx without need of populating database with a lot of data or doing smth...
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:**