mysql session logging tables

Question

I've been building a session logging system for our sites. We get around ~15m hits a year and at the moment we're really only interested in stats about people who logon to our sites.

Our table design looks like this:

db_sessions.tbl_sessions_2012 (2012 being current year, 2013 next year).

This is an InnoDB table, and has 9 indexes, 13 columns of generally varchar(varying) and INT, and 2 TEXT columns that record request URL and referrer URL. it looks like this:

CREATE TABLE `tbl_sessions_2012` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sessionid` varchar(255) NOT NULL,
  `ipaddress` varchar(255) NOT NULL,
  `geocode` varchar(255) NOT NULL DEFAULT '''',
  `journalcode` varchar(255) NOT NULL,
  `logintypeid` int(1) NOT NULL DEFAULT ''0'',
  `accountid` int(11) NOT NULL DEFAULT ''0'',
  `contactid` int(11) NOT NULL DEFAULT ''0'',
  `organisationid` int(11) NOT NULL DEFAULT ''0'',
  `accountcollectionid` int(11) NOT NULL DEFAULT ''0'',
  `request` text,
  `referer` text,
  `isSSL` tinyint(1) NOT NULL DEFAULT ''0'',
  `isLogout` tinyint(1) NOT NULL DEFAULT ''0'',
  `actionTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `contactaccounts` (`contactid`,`accountid`,`journalcode`) USING BTREE,
  KEY `contactaccountcollections` (`contactid`,`accountcollectionid`,`journalcode`) USING BTREE,
  KEY `organisationaccounts` (`organisationid`,`accountid`) USING BTREE,
  KEY `organisationaccountcollections` (`organisationid`,`accountcollectionid`,`journalcode`) USING BTREE,
  KEY `contactaccountloginmonths` (`contactid`,`accountid`,`journalcode`,`logintypeid`,`actionTime`) USING BTREE,
  KEY `contactaccountcollectionloginmonths` (`contactid`,`accountcollectionid`,`journalcode`,`logintypeid`,`actionTime`) USING BTREE,
  KEY `organisationaccountloginmonths` (`organisationid`,`accountid`,`journalcode`,`logintypeid`,`actionTime`) USING BTREE,
  KEY `organisationaccountcollectionsloginmonths` (`organisationid`,`accountcollectionid`,`journalcode`,`logintypeid`,`actionTime`) USING BTREE,
  KEY `accountcollection` (`accountcollectionid`,`journalcode`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3161119 DEFAULT CHARSET=utf8

We have two MySQL servers, one is running MySQL 5.5.24, and the other running 5.5.11 which stores all our other content. We are currently writing the session logging to our 5.5.24 which also acts as our dev database.

So at the moment, this single table is getting written to on every hit of the website, ~1.2m a month. We also want to be able to run queries on it, So have 2 views on the table depending on the login type. We only ever run queries on the views.

With this, we are getting slowdowns on the admin section of our sites, where we wish to display stats about who is logging in. None of us are DBAs, but we really need to improve the speed and efficency on this. Are there any recommended practices for writing to a session logging table and reading from it at the same time.

Could the table design be better. I went froma split monthly design to a single yearly design.

We run a query like this:

SELECT count(id) as total, accountid FROM vw_ContactAccounts_2012 s 
WHERE 1 AND accountid IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) 
GROUP BY accountid

on our admin pages to show whether someone has session statistics available.

Answer 1

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.}