sphinx get MVA attribute count for each value group by the same MVA attribute

Question

I am trying to get the count of a MVA on sphinx 2.2.10 the problem is that I have 2 queries on the same table that gives me different counts when trying to run them here are the queries :

note that lang_skill_lvl is the MVA, and table_name is the same table for both queries.

SELECT count(*) FROM table_name WHERE lang_skill_lvl IN (8001, 8002, 8003, 8004);
+----------+
| count(*) |
+----------+
|   896941 |
+----------+

SELECT 
    groupby(), count(*) as count
FROM 
    table_name 
GROUP BY lang_skill_lvl 
ORDER BY count DESC LIMIT 0, 1000;

+-----------+---------+
| groupby() | mycount |
+-----------+---------+
|      8001 |  112485 |
|      8002 |   90656 |
|      8003 |  694194 |
|      1001 |  146812 |
|     48003 |  139820 |
|      8004 |      71 |
...

if you try to get the sum of values 8001 ,8002 ,8003 ,8004 you will find that it is 897406‬.

I think the issue might be with the groupby() it self since it is a MVA is there anything I am missing please let me know.

Thank you,

Answer 1

I suspect you have documents that contain multiple values in the MVA. Eg a document with 8001 AND 8002 (just an example, lots of other possible combinations!)

The first query will only count each document once, even if multiple matches in the MVA. It's effectively count(distinct id)

Whereas the second query, can count such documents multiple times - once for each MVA match, the single document would be counted in the 8001 count, and the 8002 count.