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` (
  `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'',
  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

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.

  • Hi, can you add the DDL (SHOW CREATE TABLE) for one of the session tables. Also, give a few of the queries you're running that you notice slowdown with. Commented Nov 13, 2012 at 14:15
  • added, let me know if you'd like anymore info.
    – Jarede
    Commented Nov 13, 2012 at 14:35
  • You can add EXPLAIN before the select: EXPLAIN SELECT count(id).. to get an idea of what MySQL is doing to process your query. Unless there is a very good reason for it, I wouldn't search for accountid IN (unlimited id list). See my answer footnote for why. Commented Nov 13, 2012 at 15:03
  • you're right thats not using an index on that query. we're using that on a paginated page, so generally there will be only 40 accountids at a time (untill we allow users to decide how many accounts they want per page).
    – Jarede
    Commented Nov 13, 2012 at 15:06

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

  • Very nice dive into indexing suggestions considering you haven't seen their queries but gave good hints. +1 !!! Commented Nov 13, 2012 at 16:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.