mysql – General error 1114 when querying a table only 4% of the sotrage capacity in an RDS Instance

I have the followign problem when attemptign to draw stats on a table located in an Amazon AWS instance.

The table is composed of a huge list of names, dates and state two leter codes among other things. The dates are represented by a 6 digit code where the first four digits are the year and the last two digits the month (yes, we don’t care about the day).

I do a query to check the table size and it returns: 4097 MB which sounds about right.

But I also need to run the following query:

SELECT state_code,COUNT(date_code) as cnt,date_code FROM mytable WHERE (date_code > 200801) GROUP BY date_code,state_code  ORDER BY  state_code ASC

Basically I want to know how many rows I have have for each date_code on each state.

My RDS instance is a db.m4.large running MySQL 8.0.23 with 1000 GiB of storage and a Maximum of 2000 GiB

I get this error:
Failed getting the number of obits by year and state. Reason: Select failure: SQLSTATE(HY000): General error: 1114 The table ‘/rdsdbdata/tmp/#sql7245_5bc3_1’ is full.

Now, there is a question very much like this one here: How to solve MySQL “The table is full” error 1114 with Amazon RDS?

However the solution is to simply increase the storage size, but 4 GB is only 4 % of the storage size. And this table will be ever increasing.

How could I solve this issue? Is the only answer to increase table size

Just FYI, this will be a very seldom query. Worst case scenrio something like once a day. I need for it to work, no really interested in making it fast. In case it matters.