Posted By

prgrmmraben on 09/06/14


Tagged

mysql sql session engine cache memory


Versions (?)

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.

  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  

You need to login to post a comment.