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.