First, from looking at your existing indexes and comparing them to your example query, you are missing an index on just accountid. The way MySQL handles indexes is left-most, meaning you can have a composite index like this:
KEY `contactaccounts` (`contactid`,`accountid`,`journalcode`) USING BTREE,
and run a query that looks for contactid and the contactaccounts index would be a potential index*. However doing a query on accountid will not utilize the index, because accountid is not the left-most column.
If you never search for contactid without an accountid, I would create the index like this:
DROP INDEX `contactaccounts` ON `tbl_sessions_2012`;
CREATE INDEX `accountscontact` ON `tbl_sessions_2012` (`accountid`,`contactid`,`journalcode`);
Now, analyzing your other indexes, the first of each set is redundant using the left-most rule, and can be dropped in favor of the second:
KEY `contactaccounts` (`contactid`,`accountid`,`journalcode`) USING BTREE
KEY `contactaccountloginmonths` (`contactid`,`accountid`,`journalcode`,`logintypeid`,`actionTime`) USING BTREE
KEY `contactaccountcollections` (`contactid`,`accountcollectionid`,`journalcode`) USING BTREE
KEY `contactaccountcollectionloginmonths` (`contactid`,`accountcollectionid`,`journalcode`,`logintypeid`,`actionTime`) USING BTREE
KEY `organisationaccounts` (`organisationid`,`accountid`) USING BTREE
KEY `organisationaccountloginmonths` (`organisationid`,`accountid`,`journalcode`,`logintypeid`,`actionTime`) USING BTREE
KEY `organisationaccountcollection` (`organisationid`,`accountcollectionid`,`journalcode`) USING BTREE
KEY `organisationaccountcollectionsloginmonths` (`organisationid`,`accountcollectionid`,`journalcode`,`logintypeid`,`actionTime`) USING BTREE
By having the duplicates you are using up a lot of space on indexes.
* I say potential because there is still the possibility that your index will not be used due to the amount of rows MySQL has to scan through. As your account_id IN () statement grows, MySQL will determine that it's faster just to do a full table scan regardless of the index.
Best Answer
MySQL has "General Query Log". This logs everything that is going on MySQL server: users connecting, disconnecting, queries etc.
This query log is a file on your filesystem or (from 5.1.6 versions) table
Control the general query log at server startup as follows:
Before
5.1.6, the general query log destination is always a file. To enable the log, start mysqld with the--log[=file_name]or-l [file_name]option.As of MySQL
5.1.6, the destination can be a file or a table, or both. Start mysqld with the--log[=file_name]or-l [file_name]option to enable the general query log, and optionally use--log-outputto specify the log destinationAs of MySQL
5.1.12, as an alternative to--logor-l, use--general_log[={0|1}]to specify the initial general query log state. In this case, the default general query log file name is used. With no argument or an argument of 1,--general_logenables the log. With an argument of 0, this option disables the log.As of MySQL
5.1.29, use--general_log[={0|1}]to enable or disable the general query log, and optionally--general_log_file=file_nameto specify a log file name. The--logand-loptions are deprecated.After starting query log, investigate the file (or table) for further information.
MySQL Query Log Documentation: http://dev.mysql.com/doc/refman/5.1/en/query-log.html