Reputation: 5586
I have the following problem when attempting 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 letter 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 scenario something like once a day. I need for it to work, no really interested in making it fast. In case it matters.
Upvotes: 1
Views: 1133
Reputation: 142560
SELECT state_code,
COUNT(*) as cnt, -- assuming `date_code` is never NULL
date_code
FROM mytable
WHERE (date_code > 200801)
GROUP BY state_code, date_code -- note swap
ORDER BY state_code, date_code -- matching GROUP BY
Add INDEX(date_code, state_code)
.
Notes:
COUNT(*)
is the normal syntax for counting; COUNT(x)
checks x
for being not null before counting.GROUP BY
is probably not important, but is in ORDER BY
. Since you asked to order by state_code
, I changed GROUP BY
. Then, ...GROUP BY
and ORDER BY
are the same, there is no need for a second sort.If the "older" data is never changing, you can get a much faster query by building and maintaining a "summary table". More discussion: http://mysql.rjweb.org/doc.php/summarytables
Upvotes: 1