How can I improve the performance of the change log table?
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 &amp; 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/eileenmcnaughton/nz.co.fuzion.innodbtriggers which converts log tables to innodb automatically and this  blog post for more details: https://civicrm.org/blog/eileen/who-did-what-when