Answers.Body: <p>If logging is on then results will be retrieved from the log_xxx tables not civicm_log.</p>
  
  <p>Normally these tables use the archive engine - which is not indexable. We have in some cases selectively converted these to INNODB &amp; added indexes</p>
  
  <p>Archive tables are supposed to be quick to write to and slow to read from. They are also non-transactional - which means that if a rollback happens for a given transaction the archive table record will not be rolled back (on rare occasions this has helped us with forensics)</p>
  
  <p>EDIT: also take a look at this extension I wrote <a href="https://github.com/eileenmcnaughton/nz.co.fuzion.innodbtriggers" rel="noreferrer">https://github.com/eileenmcnaughton/nz.co.fuzion.innodbtriggers</a> which converts log tables to innodb automatically and this  blog post for more details: <a href="https://civicrm.org/blog/eileen/who-did-what-when" rel="noreferrer">https://civicrm.org/blog/eileen/who-did-what-when</a></p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2015-11-13T16:29:59.977Z
Answers.LastActivityDate: 2018-12-03T10:23:00.650Z
Answers.LastEditDate: 2018-12-03T10:23:00.650Z
Answers.UserDisplayName: eileen
Answers.Body: <p>For people googling, I significantly increased the speed of the Change Log by following Eileen's advice in her answer: convert some tables (activity, activity_contact, contact, group, group_contact) from ARCHIVE to INNODB, and adding indexes.</p>
  
  <p>The Change Log still takes ~20 seconds to load, but that's much better than the minutes it used to. We've taken a hit on disk space, and I suppose write speed (though I can't tell the difference), but definitely worth it!</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-01-12T17:38:51.323Z
Answers.LastActivityDate: 2016-01-12T17:38:51.323Z
Answers.UserDisplayName: Andrew West
Answers.Body: <p>Take a look at the <a href="https://github.com/eileenmcnaughton/nz.co.fuzion.innodbtriggers" rel="nofollow noreferrer">INNODB logging extension</a>. It converts the log table format from ARCHIVE to INNODB, adding indexes and speeding up as a result.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-12-03T10:12:09.133Z
Answers.LastActivityDate: 2018-12-03T10:12:09.133Z
Answers.UserDisplayName: michaelmcandrew
Answers.Body: <p>The log tables as originally implemented (I have checked for a few years if this is the case) also contain a large number of superfluous records due to writes that don't actually change the data. Improving the triggers to check for a difference between old and new values before the write to log would improve things for everyone.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-01-12T22:08:20.827Z
Answers.LastActivityDate: 2016-01-12T22:08:20.827Z
Answers.UserDisplayName: Joe Murray
Answers.Body: <p>I discussed this with Bjorn Endres, and his idea is to turn logging off, drop the log tables, and then turn logging back on. This is, I think, a good answer.  The reason our log tables are so big is because we have transferred data from a legacy system.  We don't really need 99% of the data in these logs so this is what we intend to do.  Thanks, Bjorn!</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-11-18T12:48:39.817Z
Answers.Text: I've got detailed logging turned on for a couple of current sites I'm working with, and these are set up to use a separate database (not the main civicrm db). Don't know if this might help?
Answers.UserDisplayName: Graham
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-11-19T13:13:08.770Z
Answers.Text: That's interesting, Graham.  I guess this involved changes in Civi?  What were they?
Answers.UserDisplayName: Andy Clark
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-11-20T10:32:40.163Z
Answers.Text: @andy-clarke If you look in the civicrm.settings.php file you'll see there is an option there to define a separate db for logging.
Answers.UserDisplayName: Graham
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-11-17T21:32:24.543Z
Answers.LastActivityDate: 2015-11-17T21:32:24.543Z
Answers.UserDisplayName: Andy Clark
Answers.Body: <p>Pretty sure an index would help indeed, the question is which one?</p>
  
  <p>Can you log slow queries on your database and see which queries are taking a lot of time, see where it can be added?</p>
  
  <p>finetune how many seconds before a query is slow (any query that takes more than 1 or 2 sec is slow is what I use)</p>
  
  <p><a href="http://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html</a></p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-11-13T08:35:37.960Z
Answers.Text: Thanks - I know how to reproduce the problem - just click on 'Change Log' tab in the summary screen for almost anyone. I just tried this for someone who has maybe only a very few transactions, and it's still running. So how do I find out which SQL query is running? Or, as you said, how do I log slow queries?
Answers.UserDisplayName: Andy Clark
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-11-13T09:12:41.097Z
Answers.Text: added a link to mysql doc on slow query log
Answers.UserDisplayName: Xavier
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-10-19T15:45:24.760Z
Answers.Text: Here's an example. Clicking on the Change Log was triggering this sql: SELECT group_id  FROM log_civicrm_group_contact  WHERE  log_date <= 20180828. Since that table has 2 million + records and it's an ARCHIVE without an index, it's bound to be slow. Other times, it seemed to be spending time sorting the results, so I suspect if you just add indices to the log_date timestamp field for all your big log_* tables, it might fix 90% of the issue.
Answers.UserDisplayName: Alan Dixon
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-11-13T07:22:08.500Z
Answers.LastActivityDate: 2015-11-13T09:12:26.477Z
Answers.LastEditDate: 2015-11-13T09:12:26.477Z
Answers.UserDisplayName: Xavier
Body: <p>Retrieving the change log for a contact takes ages for my client - maybe a response of 10 minutes to get just 79 records for one contact. The contact database has about 39K rows, but with about 950k rows in the civicrm_log table (there are lots of transactions). Logging is switched on, which at the time seemed a very attractive option.</p>
  
  <p>Would adding an index help?  I have defragged &amp; optimised the civicrm_log table, but to no effect.</p>
  
Tags: <4.6><performance><logging><db-logging><mysql-archive-format>
Title: How can I improve the performance of the change log table?
ThreadText: # How can I improve the performance of the change log table?
  
  (Asked by: username_0 on 2015-11-12)
  
  Retrieving the change log for a contact takes ages for my client - maybe a response of 10 minutes to get just 79 records for one contact. The contact database has about 39K rows, but with about 950k rows in the civicrm\_log table (there are lots of transactions). Logging is switched on, which at the time seemed a very attractive option.
  
  Would adding an index help? I have defragged & optimised the civicrm\_log table, but to no effect.
  
  **Tags:** 4.6, performance, logging, db-logging, mysql-archive-format
  
  
  ---
  
  ## 6 answers
  
  ### Answer by username_1 on 2015-11-13. Score: 11
  
  If logging is on then results will be retrieved from the log\_xxx tables not civicm\_log.
  
  Normally these tables use the archive engine - which is not indexable. We have in some cases selectively converted these to INNODB & added indexes
  
  Archive tables are supposed to be quick to write to and slow to read from. They are also non-transactional - which means that if a rollback happens for a given transaction the archive table record will not be rolled back (on rare occasions this has helped us with forensics)
  
  EDIT: also take a look at this extension I wrote <https://github.com/username_1mcnaughton/nz.co.fuzion.innodbtriggers> which converts log tables to innodb automatically and this blog post for more details: <https://civicrm.org/blog/username_1/who-did-what-when>
  
  
  ---
  
  ### Answer by username_2 on 2016-01-12. Score: 3
  
  For people googling, I significantly increased the speed of the Change Log by following Eileen's advice in her answer: convert some tables (activity, activity\_contact, contact, group, group\_contact) from ARCHIVE to INNODB, and adding indexes.
  
  The Change Log still takes ~20 seconds to load, but that's much better than the minutes it used to. We've taken a hit on disk space, and I suppose write speed (though I can't tell the difference), but definitely worth it!
  
  
  ---
  
  ### Answer by username_3 on 2018-12-03. Score: 2
  
  Take a look at the [INNODB logging extension](https://github.com/username_1mcnaughton/nz.co.fuzion.innodbtriggers). It converts the log table format from ARCHIVE to INNODB, adding indexes and speeding up as a result.
  
  
  ---
  
  ### Answer by username_4 on 2016-01-12. Score: 2
  
  The log tables as originally implemented (I have checked for a few years if this is the case) also contain a large number of superfluous records due to writes that don't actually change the data. Improving the triggers to check for a difference between old and new values before the write to log would improve things for everyone.
  
  
  ---
  
  ### Answer by username_0 on 2015-11-17. Score: 1
  
  I discussed this with Bjorn Endres, and his idea is to turn logging off, drop the log tables, and then turn logging back on. This is, I think, a good answer. The reason our log tables are so big is because we have transferred data from a legacy system. We don't really need 99% of the data in these logs so this is what we intend to do. Thanks, Bjorn!
  
  > **Comments:**
  > **username_5**: I've got detailed logging turned on for a couple of current sites I'm working with, and these are set up to use a separate database (not the main civicrm db). Don't know if this might help?  
  > **username_0**: That's interesting, username_5.  I guess this involved changes in Civi?  What were they?  
  > **username_5**: @andy-clarke If you look in the civicrm.settings.php file you'll see there is an option there to define a separate db for logging.  
  
  ---
  
  ### Answer by username_6 on 2015-11-13. Score: 1
  
  Pretty sure an index would help indeed, the question is which one?
  
  Can you log slow queries on your database and see which queries are taking a lot of time, see where it can be added?
  
  finetune how many seconds before a query is slow (any query that takes more than 1 or 2 sec is slow is what I use)
  
  <http://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html>
  
  > **Comments:**
  > **username_0**: Thanks - I know how to reproduce the problem - just click on 'Change Log' tab in the summary screen for almost anyone. I just tried this for someone who has maybe only a very few transactions, and it's still running. So how do I find out which SQL query is running? Or, as you said, how do I log slow queries?  
  > **username_6**: added a link to mysql doc on slow query log  
  > **username_7**: Here's an example. Clicking on the Change Log was triggering this sql: SELECT group_id  FROM log_civicrm_group_contact  WHERE  log_date <= 20180828. Since that table has 2 million + records and it's an ARCHIVE without an index, it's bound to be slow. Other times, it seemed to be spending time sorting the results, so I suspect if you just add indices to the log_date timestamp field for all your big log_* tables, it might fix 90% of the issue.  
  
  ---
  
  