Posted By

prgrmmraben on 09/06/14

Tagged

Versions (?)

Last Edited at 09/06/14 07:35am

Statistics

Viewed 352 times

Favorited by 0 user(s)

Related snippets

A light implementation of Session table in Mysql 5.6 using memory engine

/ Published in: SQL

caching session variables in Mysql using memory engine for fast client response.
note: please test before use, then use at your own risk.

Highlighted TextExpand

/** @author prgrmmr.aben [at] gmail (dot) com
* http://fivesnippets.blogspot.com/2014/08/a-very-light-implementation-of-session.html
* please give back a small donation if you find
* this little educational snippet of code useful 
**/
CREATE TABLE IF NOT EXISTS `session` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  /*`refUser` int(11) DEFAULT NULL COMMENT 'references user number',*/
  `IP` int(39) DEFAULT NULL,
  `creation` datetime NOT NULL TIMESTAMP DEFAULT CURRENT_TIMESTAMP  COMMENT 'creation time',
  `expiry` datetime DEFAULT NULL  COMMENT 'expiry time',
  `secretToken` varchar(10) NOT NULL,
  `type` enum('guest','client') NOT NULL DEFAULT 'guest',
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='don''t need to be a real session in memory' AUTO_INCREMENT=1 ;
CREATE TABLE cachedSession ENGINE=MEMORY SELECT * FROM session;
CREATE DEFINER=`root`@`localhost` PROCEDURE `cacheSessions`()
    NO SQL
BEGIN
delete from cachedsession where 1;
insert into cachedsession select * from session where `expiry`<NOW();
select row_count();
end$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `cleanSession`()
    NO SQL
    COMMENT 'clean sessions expired @hours ago'
BEGIN
DELETE FROM `session` WHERE  TIMESTAMPDIFF(MINUTE, expiry, NOW())>0;
select row_count();
end$$

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.