The GROUP BY clause in a query in MySQL crashes my connection only in some cases

Question

I have 2 tables, one of them contains BLOBs.

psh table
................................
ID
playerID
stateID
sceneID


ps table
..................................
ID
playerID
playerState

What i need to get is a count of the IDs from ps table grouped by sceneID (from psh table). So I'm joining the 2 tables and grouping. The problem is in this specific query the group by clause crashes the database and i get error 2013: lost connection during query.

If i try other queries, in which i use group by, it will work just fine.

this is my query, please help out:

select count(ps.ID) from innodb.player_spin as ps
JOIN innodb.player_state_history as psh  ON
psh.playerId = ps.playerId
WHERE psh.sceneId IN (9, 19, 29, 30)
group by psh.sceneId;

one table is 90k rows (includes BLOBS) and the other one is super small (30 rows). — Theorema Lu' Chibritu, Sep 08 '17 at 11:03
the blobs are of no interest to my query, i dont need the blob column (which is why i didnt mention it, its part of table 1) — Theorema Lu' Chibritu, Sep 08 '17 at 11:06
Are your `playerId` columns indexed for both tables? if not add they, also add index to `psh.sceneId` — Oto Shavadze, Sep 08 '17 at 11:07
Also check this: https://stackoverflow.com/questions/10563619/error-code-2013-lost-connection-to-mysql-server-during-query — Oto Shavadze, Sep 08 '17 at 11:12
yes, the playerId are indexed for both tables, the sceneID is not and i cannot index it as i dont have rights :( — Theorema Lu' Chibritu, Sep 08 '17 at 11:16
i have managed to index the sceneID but i don't think the results are reliable, the good part is the query works now!! p.s. @strawberry, i added a second column (sceneID) so that i know what count corresponds to which scene... — Theorema Lu' Chibritu, Sep 08 '17 at 11:40
still need help, as the query does not generate the correct values... — Theorema Lu' Chibritu, Sep 08 '17 at 12:20

score 0 · Answer 1 · answered Sep 08 '17 at 23:31

The GROUP BY is from table psh. It should be the driving table and join with the non group by table:

select count(ps.ID) 
FROM player_state_history as psh
JOIN player_spin as ps ON
  psh.playerId = ps.playerId
WHERE psh.sceneId IN (9, 19, 29, 30)
GROUP BY psh.sceneId

This should speed things quite a bit if properly indexed on psh.sceneId

The GROUP BY clause in a query in MySQL crashes my connection only in some cases

1 Answers1

Linked