Answers.Body: <p>There's a number of possible reasons:</p>
  <ul>
  <li>Do you regularly delete activity records? The table size on disk won't shrink unless you manually take steps to do this, e.g. OPTIMIZE TABLE. So you might not have many records, but all the original space is still reserved but empty.</li>
  <li>You have some additional indexes. Compare to a fresh install.</li>
  <li>utf8mb4 can be larger than utf8mb3 (aka utf8), if you are using utf8mb4 and comparing to an older database.</li>
  <li>Are the details fields in the records large? i.e. are they typically emails or thank-you letters with lots of text?
  <ul>
  <li><code>select id, length(details) from civicrm_activity order by length(details) desc limit 4;</code> will show e.g. the top 4 by length.</li>
  </ul>
  </li>
  <li>You can get the breakdown of data vs index usage with <code>SHOW TABLE STATUS LIKE 'civicrm_activity'</code> which might help narrow it down.</li>
  </ul>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-10-29T18:32:26.530Z
Answers.Text: Thanks Demerit. I just deleted a load of old records because the table was previously even larger. This brought it down to 1GB. Then I optimized it to remove the empty records and it grew to 1.1 GB (!). I can't see any large amounts of text in the detail field. Mainly very brief notes. Charset seems to be plain utf.
  
  How would I compare the indexes to a fresh install?
Answers.UserDisplayName: Jern
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-10-29T18:44:54.510Z
Answers.Text: Just checked a different install where the _activity table has 70k records, and it has the same number of indexes (12). The table size is 28M - much more what I would expect.
Answers.UserDisplayName: Jern
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-10-29T23:13:19.313Z
Answers.Text: Hmm.  `SHOW TABLE STATUS LIKE 'civicrm_activity'` will at least tell you data length and index length so you might be able to narrow it down to one or the other (or neither?). Otherwise generic internet searches about db table size might have an idea.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-10-30T08:28:07.603Z
Answers.Text: Thanks. That was a really great tip. Indeed, the data length is almost all of the 1.1G. So it's 'just' a matter of identifying which records are taking up the space.
  
  Please could you post this as an answer, and I'll accept it as such. Thanks again.
Answers.UserDisplayName: Jern
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-10-30T14:46:06.120Z
Answers.Text: Ok I have updated. Also with a query to help find long details fields.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-10-31T09:55:20.870Z
Answers.Text: That sorting query is really helpful. Thanks very much.
Answers.UserDisplayName: Jern
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-10-31T10:10:59.353Z
Answers.Text: Out of 140000, there are a dozen or so details fields with over 100k, then a load at 65k and 50000 with 0 length. So roughly 10M tops in total. I guess the mystery 1.1GB must be elsewhere. Hmm. Now I know how to do it, I'll go through each field and find the culprit. Watch this space...
Answers.UserDisplayName: Jern
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-10-31T18:59:58.340Z
Answers.Text: Nothing in the other fields. It seems we have a case of dark matter in the table. It's not in the index, and it's not visible using the length() operator. Where else could it hide?
Answers.UserDisplayName: Jern
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-10-31T22:17:03.800Z
Answers.Text: Hehe dark matter. You could try a complete db dump and reload if you have those permissions and are getting desperate. Perhaps OPTIMIZE TABLE didn't really optimize for some reason. My only guess right now is that it's space that used to be used before by deleted records, and hasn't been reclaimed yet.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-10-29T14:43:21.250Z
Answers.LastActivityDate: 2022-10-30T14:45:43.773Z
Answers.LastEditDate: 2022-10-30T14:45:43.773Z
Answers.UserDisplayName: Demerit
Body: <p>Our civicrm_activity table is large - about 1 GB, but there are only 170,000 rows. Is this normal? I'm surprised that this many rows should need such a large table. Thanks.</p>
  
Tags: <activity>
Title: large civicrm_activity table
ThreadText: # large civicrm_activity table
  
  (Asked by: username_0 on 2022-10-29)
  
  Our civicrm\_activity table is large - about 1 GB, but there are only 170,000 rows. Is this normal? I'm surprised that this many rows should need such a large table. Thanks.
  
  **Tags:** activity
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2022-10-29. Score: 2 | Accepted answer
  
  There's a number of possible reasons:
  
  * Do you regularly delete activity records? The table size on disk won't shrink unless you manually take steps to do this, e.g. OPTIMIZE TABLE. So you might not have many records, but all the original space is still reserved but empty.
  * You have some additional indexes. Compare to a fresh install.
  * utf8mb4 can be larger than utf8mb3 (aka utf8), if you are using utf8mb4 and comparing to an older database.
  * Are the details fields in the records large? i.e. are they typically emails or thank-you letters with lots of text?
    + `select id, length(details) from civicrm_activity order by length(details) desc limit 4;` will show e.g. the top 4 by length.
  * You can get the breakdown of data vs index usage with `SHOW TABLE STATUS LIKE 'civicrm_activity'` which might help narrow it down.
  
  > **Comments:**
  > **username_0**: Thanks username_1. I just deleted a load of old records because the table was previously even larger. This brought it down to 1GB. Then I optimized it to remove the empty records and it grew to 1.1 GB (!). I can't see any large amounts of text in the detail field. Mainly very brief notes. Charset seems to be plain utf.  How would I compare the indexes to a fresh install?  
  > **username_0**: Just checked a different install where the _activity table has 70k records, and it has the same number of indexes (12). The table size is 28M - much more what I would expect.  
  > **username_1**: Hmm.  `SHOW TABLE STATUS LIKE 'civicrm_activity'` will at least tell you data length and index length so you might be able to narrow it down to one or the other (or neither?). Otherwise generic internet searches about db table size might have an idea.  
  > **username_0**: Thanks. That was a really great tip. Indeed, the data length is almost all of the 1.1G. So it's 'just' a matter of identifying which records are taking up the space.  Please could you post this as an answer, and I'll accept it as such. Thanks again.  
  > **username_1**: Ok I have updated. Also with a query to help find long details fields.  
  > **username_0**: That sorting query is really helpful. Thanks very much.  
  > **username_0**: Out of 140000, there are a dozen or so details fields with over 100k, then a load at 65k and 50000 with 0 length. So roughly 10M tops in total. I guess the mystery 1.1GB must be elsewhere. Hmm. Now I know how to do it, I'll go through each field and find the culprit. Watch this space...  
  > **username_0**: Nothing in the other fields. It seems we have a case of dark matter in the table. It's not in the index, and it's not visible using the length() operator. Where else could it hide?  
  > **username_1**: Hehe dark matter. You could try a complete db dump and reload if you have those permissions and are getting desperate. Perhaps OPTIMIZE TABLE didn't really optimize for some reason. My only guess right now is that it's space that used to be used before by deleted records, and hasn't been reclaimed yet.  
  
  ---
  
  