MySQL sum query takes a long time to complete. Bottleneck search

Question

I run a simple MySQL query to find the total time spent on the game:

SELECT userId, SUM(time) AS totalGameTime FROM game_attempts WHERE userId = 19599 

EXPLAIN shows the following:

 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE game_attempts ref userId_gameId userId_gameId 4 const 26880 

PROFILER shows that most of the time spent on "Sending data":

 Sending data 1.786524 

Why does such a simple request take so long? Where to look for a bottleneck?

UPDATE. Time is INT (11), no transformations.

UPDATE A possible solution is to introduce an index (userId, time), which solves the problem by moving part of the data into the index tree. But this does not solve the more serious problem of why summing as many as 30,000 takes so long.

This question has no easy answer. The indices are correct; no lengthy conversions are required. It's just tuning a DB tuner - why does it take so long to put these 30,000 records and extract data?

It is important to say that the table uses the InnoDB engine and contains about 2 million records.

Answer 1

try making an index for userId, as this will solve your problem:

  ALTER TABLE game_attempts ADD INDEX (userId); 

Answer 2

It is assumed that you return a large number of rows to the client. Can you add

 GROUP BY userId 

so that you return only one row?

Answer 3

Make an index for userId. This restricts access to entries with a userId error.

Answer 4

In any other DBMS, your statement will be considered invalid SQL, because the selected part of your query contains an aggregate function, as well as a field that is not part of the GROUP BY clause - in fact, you do not have a GROUP BY clause.

Oracle, for example. will tell you:

ORA-00937: non-group group function

You will get something similar in MSSQL. I would suggest that MySQL here is to calculate the SUM path more often than necessary.

The following query will be a more standard SQL standard and will be faster:

 SELECT userId, SUM(time) AS totalGameTime FROM game_attempts WHERE userId = 19599 GROUP BY userId; 

Answer 5

Is the precision in the time column too high? What if you summarize

 SEC_TO_TIME(SUM(TIME_TO_SEC(time))) 

instead

Answer 6

Well, I mark this as anwser so that you cannot execute this error again.

Like MySQL 5.0.23, you can set ONLY_FULL_GROUP_BY with the SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY' command; ur configured the server correctly

 mysql> SELECT name, MAX(age) FROM t; ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause 

source ( http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_only_full_group_by )