CiviCRM Community Forums (archive)

Hi there

I've enabled logging on our production site, but getting timeouts due to the length of times the queries take to return. 

To try and resolve it, I've converted all the table engines from ARCHIVE to InnoDB as recommended by Eileen here: http://forum.civicrm.org/index.php/topic,32225.msg137892.html#msg137892

I haven't added any indexes, as not sure what to add.

Any recommendations, suggestions, ideas on how to resolve this?

For example - this from the mySQL slow log:
Query_time: 657.225739
Lock_time: 0.000114 Rows_sent: 0  Rows_examined: 290549070

   
use civicrm;
SET timestamp=1419186263;
SELECT DISTINCT lt.id FROM `civicrm`.`log_civicrm_note` lt
WHERE log_conn_id = 60 AND
      log_date BETWEEN DATE_SUB('20141221181314', INTERVAL 10 SECOND)
AND DATE_ADD('20141221181314', INTERVAL 10 SECOND)
      AND ( entity_id = 110556 AND entity_table = 'civicrm_contact' )
OR (entity_id IN (SELECT note.id FROM civicrm.log_civicrm_note note
WHERE note.entity_id = 110556 AND note.entity_table = 'civicrm_contact')
AND entity_table = 'civicrm_note');