Group by MVA in SphinxQL
This is a just a quick note I’d like to share since this might be confusing how one should do “group by” mva attribute in SphinxQL. Starting v 2.0.1-beta there’s a compat_sphinxql_magics directive which makes SphinxQL similar to standard SQL although there’re still few things that differ especially when it comes to MVA which is a bit unusual thing for standard SQL. Anyway back to the problem: if you have MVA attribute ‘tags’ with some integers and want to group by ‘tags’ your first intuitively written command will be probably like 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 will help someone to save some time when he faces this situation.
Leave a comment
Your comment
Notify me of followup comments via e-mail. You can also subscribe without commenting.