Edit Question - KnowledgeHub

Title Body

What is 'correct' query to fetch a cumulative sum in MySQL? I've a table where I keep information about files, one column list contains the size of the files in bytes. (the actual files are kept on disk somewhere) I would like to get the cumulative file size like this: +------------+---------+--------+----------------+ | fileInfoId | groupId | size | cumulativeSize | +------------+---------+--------+----------------+ | 1 | 1 | 522120 | 522120 | | 2 | 2 | 316042 | 316042 | | 4 | 2 | 711084 | 1027126 | | 5 | 2 | 697002 | 1724128 | | 6 | 2 | 663425 | 2387553 | | 7 | 2 | 739553 | 3127106 | | 8 | 2 | 700938 | 3828044 | | 9 | 2 | 695614 | 4523658 | | 10 | 2 | 744204 | 5267862 | | 11 | 2 | 609022 | 5876884 | | ... | ... | ... | ... | +------------+---------+--------+----------------+ 20000 rows in set (19.2161 sec.) Right now, I use the following query to get the above results SELECT a.fileInfoId , a.groupId , a.size , SUM(b.size) AS cumulativeSize FROM fileInfo AS a LEFT JOIN fileInfo AS b USING(groupId) WHERE a.fileInfoId >= b.fileInfoId GROUP BY a.fileInfoId ORDER BY a.groupId, a.fileInfoId My solution is however, extremely slow. (around 19 seconds without cache). Explain gives the following execution details +----+--------------+-------+-------+-------------------+-----------+---------+----------------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+-------+-------+-------------------+-----------+---------+----------------+-------+-------------+ | 1 | SIMPLE | a | index |

Tags (comma-separated) Cancel