Advanced Search

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

/ Published in: SQL
Save to your folder(s)
caching session variables in Mysql using memory engine for fast client response.
note: please test before use, then use at your own risk.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. /** @author prgrmmr.aben [at] gmail (dot) com
  2. * http://fivesnippets.blogspot.com/2014/08/a-very-light-implementation-of-session.html
  3. * please give back a small donation if you find
  4. * this little educational snippet of code useful 
  5. **/
  6. CREATE TABLE IF NOT EXISTS `session` (
  7.   `id` INT(11) NOT NULL AUTO_INCREMENT,
  8.   /*`refUser` int(11) DEFAULT NULL COMMENT 'references user number',*/
  9.   `IP` INT(39) DEFAULT NULL,
  10.   `creation` datetime NOT NULL TIMESTAMP DEFAULT CURRENT_TIMESTAMP  COMMENT 'creation time',
  11.   `expiry` datetime DEFAULT NULL  COMMENT 'expiry time',
  12.   `secretToken` VARCHAR(10) NOT NULL,
  13.   `type` enum('guest','client') NOT NULL DEFAULT 'guest',
  14.   PRIMARY KEY (`id`),
  15. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='don''t need to be a real session in memory' AUTO_INCREMENT=1 ;
  16. CREATE TABLE cachedSession ENGINE=MEMORY SELECT * FROM SESSION;
  17. CREATE DEFINER=`root`@`localhost` PROCEDURE `cacheSessions`()
  18.     NO SQL
  19. BEGIN
  20. DELETE FROM cachedsession WHERE 1;
  21. INSERT INTO cachedsession SELECT * FROM SESSION WHERE `expiry`<NOW();
  22. SELECT ROW_COUNT();
  23. END$$
  24.  
  25. CREATE DEFINER=`root`@`localhost` PROCEDURE `cleanSession`()
  26.     NO SQL
  27.     COMMENT 'clean sessions expired @hours ago'
  28. BEGIN
  29. DELETE FROM `session` WHERE  TIMESTAMPDIFF(MINUTE, expiry, NOW())>0;
  30. SELECT ROW_COUNT();
  31. END$$

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.