Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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. –  Derek Downey Nov 13 '12 at 14:15
    
added, let me know if you'd like anymore info. –  Jarede Nov 13 '12 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. –  Derek Downey Nov 13 '12 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 Nov 13 '12 at 15:06
add comment

1 Answer

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.

share|improve this answer
    
Very nice dive into indexing suggestions considering you haven't seen their queries but gave good hints. +1 !!! –  RolandoMySQLDBA Nov 13 '12 at 16:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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