MySQL Forums
Forum List  »  Data Warehouse

Re: Optimizing it even more
Posted by: Pepe Pardo
Date: May 30, 2011 08:57AM

Hi, here they are:

SHOW CREATE TABLE f_sessions;
CREATE TABLE `f_sessions` (
  `session_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `begin_timestamp` datetime NOT NULL,
  `bot_fk` smallint(5) unsigned NOT NULL,
  `end_timestamp` datetime DEFAULT NULL,
  `session_key` varchar(36) DEFAULT NULL,
  `transaction_count` smallint(5) unsigned DEFAULT NULL,
  `kb_load_time` datetime DEFAULT NULL,
  `begin_load` smallint(5) unsigned DEFAULT NULL,
  `end_load` smallint(5) unsigned DEFAULT NULL,
  `client_hostname` varchar(100) DEFAULT NULL,
  `client_ip` varchar(15) DEFAULT NULL,
  `agent_fk` int(10) unsigned DEFAULT NULL,
  `bot_name_fk` smallint(5) unsigned NOT NULL,
  `admin` tinyint(1) unsigned NOT NULL,
  `continued` tinyint(1) unsigned NOT NULL,
  `empty` tinyint(1) unsigned NOT NULL,
  `blocked` tinyint(1) unsigned NOT NULL,
  KEY `f_sessions_session_id` (`session_id`)
) ENGINE=InnoDB AUTO_INCREMENT=39562422 DEFAULT CHARSET=utf8
PARTITION BY RANGE COLUMNS(begin_timestamp)
SUBPARTITION BY HASH (`bot_fk`)
SUBPARTITIONS 3
(PARTITION partOld VALUES LESS THAN ('2003-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200301 VALUES LESS THAN ('2003-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200302 VALUES LESS THAN ('2003-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200303 VALUES LESS THAN ('2003-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200304 VALUES LESS THAN ('2003-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200305 VALUES LESS THAN ('2003-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200306 VALUES LESS THAN ('2003-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200307 VALUES LESS THAN ('2003-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200308 VALUES LESS THAN ('2003-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200309 VALUES LESS THAN ('2003-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200310 VALUES LESS THAN ('2003-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200311 VALUES LESS THAN ('2003-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200312 VALUES LESS THAN ('2004-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200401 VALUES LESS THAN ('2004-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200402 VALUES LESS THAN ('2004-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200403 VALUES LESS THAN ('2004-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200404 VALUES LESS THAN ('2004-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200405 VALUES LESS THAN ('2004-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200406 VALUES LESS THAN ('2004-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200407 VALUES LESS THAN ('2004-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200408 VALUES LESS THAN ('2004-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200409 VALUES LESS THAN ('2004-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200410 VALUES LESS THAN ('2004-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200411 VALUES LESS THAN ('2004-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200412 VALUES LESS THAN ('2005-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200501 VALUES LESS THAN ('2005-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200502 VALUES LESS THAN ('2005-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200503 VALUES LESS THAN ('2005-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200504 VALUES LESS THAN ('2005-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200505 VALUES LESS THAN ('2005-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200506 VALUES LESS THAN ('2005-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200507 VALUES LESS THAN ('2005-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200508 VALUES LESS THAN ('2005-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200509 VALUES LESS THAN ('2005-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200510 VALUES LESS THAN ('2005-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200511 VALUES LESS THAN ('2005-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200512 VALUES LESS THAN ('2006-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200601 VALUES LESS THAN ('2006-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200602 VALUES LESS THAN ('2006-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200603 VALUES LESS THAN ('2006-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200604 VALUES LESS THAN ('2006-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200605 VALUES LESS THAN ('2006-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200606 VALUES LESS THAN ('2006-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200607 VALUES LESS THAN ('2006-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200608 VALUES LESS THAN ('2006-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200609 VALUES LESS THAN ('2006-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200610 VALUES LESS THAN ('2006-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200611 VALUES LESS THAN ('2006-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200612 VALUES LESS THAN ('2007-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200701 VALUES LESS THAN ('2007-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200702 VALUES LESS THAN ('2007-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200703 VALUES LESS THAN ('2007-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200704 VALUES LESS THAN ('2007-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200705 VALUES LESS THAN ('2007-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200706 VALUES LESS THAN ('2007-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200707 VALUES LESS THAN ('2007-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200708 VALUES LESS THAN ('2007-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200709 VALUES LESS THAN ('2007-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200710 VALUES LESS THAN ('2007-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200711 VALUES LESS THAN ('2007-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200712 VALUES LESS THAN ('2008-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200801 VALUES LESS THAN ('2008-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200802 VALUES LESS THAN ('2008-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200803 VALUES LESS THAN ('2008-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200804 VALUES LESS THAN ('2008-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200805 VALUES LESS THAN ('2008-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200806 VALUES LESS THAN ('2008-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200807 VALUES LESS THAN ('2008-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200808 VALUES LESS THAN ('2008-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200809 VALUES LESS THAN ('2008-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200810 VALUES LESS THAN ('2008-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200811 VALUES LESS THAN ('2008-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200812 VALUES LESS THAN ('2009-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200901 VALUES LESS THAN ('2009-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200902 VALUES LESS THAN ('2009-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200903 VALUES LESS THAN ('2009-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200904 VALUES LESS THAN ('2009-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200905 VALUES LESS THAN ('2009-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200906 VALUES LESS THAN ('2009-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200907 VALUES LESS THAN ('2009-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200908 VALUES LESS THAN ('2009-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200909 VALUES LESS THAN ('2009-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200910 VALUES LESS THAN ('2009-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200911 VALUES LESS THAN ('2009-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION part200912 VALUES LESS THAN ('2010-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201001 VALUES LESS THAN ('2010-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201002 VALUES LESS THAN ('2010-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201003 VALUES LESS THAN ('2010-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201004 VALUES LESS THAN ('2010-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201005 VALUES LESS THAN ('2010-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201006 VALUES LESS THAN ('2010-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201007 VALUES LESS THAN ('2010-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201008 VALUES LESS THAN ('2010-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201009 VALUES LESS THAN ('2010-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201010 VALUES LESS THAN ('2010-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201011 VALUES LESS THAN ('2010-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201012 VALUES LESS THAN ('2011-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201101 VALUES LESS THAN ('2011-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201102 VALUES LESS THAN ('2011-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201103 VALUES LESS THAN ('2011-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201104 VALUES LESS THAN ('2011-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201105 VALUES LESS THAN ('2011-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201106 VALUES LESS THAN ('2011-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201107 VALUES LESS THAN ('2011-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201108 VALUES LESS THAN ('2011-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201109 VALUES LESS THAN ('2011-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201110 VALUES LESS THAN ('2011-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201111 VALUES LESS THAN ('2011-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201112 VALUES LESS THAN ('2012-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201201 VALUES LESS THAN ('2012-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201202 VALUES LESS THAN ('2012-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201203 VALUES LESS THAN ('2012-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201204 VALUES LESS THAN ('2012-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201205 VALUES LESS THAN ('2012-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201206 VALUES LESS THAN ('2012-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201207 VALUES LESS THAN ('2012-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201208 VALUES LESS THAN ('2012-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201209 VALUES LESS THAN ('2012-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201210 VALUES LESS THAN ('2012-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201211 VALUES LESS THAN ('2012-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201212 VALUES LESS THAN ('2013-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201301 VALUES LESS THAN ('2013-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201302 VALUES LESS THAN ('2013-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201303 VALUES LESS THAN ('2013-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201304 VALUES LESS THAN ('2013-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201305 VALUES LESS THAN ('2013-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201306 VALUES LESS THAN ('2013-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201307 VALUES LESS THAN ('2013-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201308 VALUES LESS THAN ('2013-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201309 VALUES LESS THAN ('2013-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201310 VALUES LESS THAN ('2013-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201311 VALUES LESS THAN ('2013-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201312 VALUES LESS THAN ('2014-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201401 VALUES LESS THAN ('2014-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201402 VALUES LESS THAN ('2014-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201403 VALUES LESS THAN ('2014-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201404 VALUES LESS THAN ('2014-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201405 VALUES LESS THAN ('2014-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201406 VALUES LESS THAN ('2014-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201407 VALUES LESS THAN ('2014-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201408 VALUES LESS THAN ('2014-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201409 VALUES LESS THAN ('2014-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201410 VALUES LESS THAN ('2014-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201411 VALUES LESS THAN ('2014-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201412 VALUES LESS THAN ('2015-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201501 VALUES LESS THAN ('2015-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201502 VALUES LESS THAN ('2015-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201503 VALUES LESS THAN ('2015-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201504 VALUES LESS THAN ('2015-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201505 VALUES LESS THAN ('2015-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201506 VALUES LESS THAN ('2015-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201507 VALUES LESS THAN ('2015-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201508 VALUES LESS THAN ('2015-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201509 VALUES LESS THAN ('2015-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201510 VALUES LESS THAN ('2015-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201511 VALUES LESS THAN ('2015-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201512 VALUES LESS THAN ('2016-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201601 VALUES LESS THAN ('2016-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201602 VALUES LESS THAN ('2016-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201603 VALUES LESS THAN ('2016-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201604 VALUES LESS THAN ('2016-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201605 VALUES LESS THAN ('2016-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201606 VALUES LESS THAN ('2016-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201607 VALUES LESS THAN ('2016-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201608 VALUES LESS THAN ('2016-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201609 VALUES LESS THAN ('2016-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201610 VALUES LESS THAN ('2016-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201611 VALUES LESS THAN ('2016-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201612 VALUES LESS THAN ('2017-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201701 VALUES LESS THAN ('2017-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201702 VALUES LESS THAN ('2017-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201703 VALUES LESS THAN ('2017-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201704 VALUES LESS THAN ('2017-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201705 VALUES LESS THAN ('2017-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201706 VALUES LESS THAN ('2017-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201707 VALUES LESS THAN ('2017-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201708 VALUES LESS THAN ('2017-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201709 VALUES LESS THAN ('2017-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201710 VALUES LESS THAN ('2017-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201711 VALUES LESS THAN ('2017-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201712 VALUES LESS THAN ('2018-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201801 VALUES LESS THAN ('2018-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201802 VALUES LESS THAN ('2018-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201803 VALUES LESS THAN ('2018-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201804 VALUES LESS THAN ('2018-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201805 VALUES LESS THAN ('2018-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201806 VALUES LESS THAN ('2018-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201807 VALUES LESS THAN ('2018-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201808 VALUES LESS THAN ('2018-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201809 VALUES LESS THAN ('2018-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201810 VALUES LESS THAN ('2018-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201811 VALUES LESS THAN ('2018-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201812 VALUES LESS THAN ('2019-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201901 VALUES LESS THAN ('2019-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201902 VALUES LESS THAN ('2019-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201903 VALUES LESS THAN ('2019-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201904 VALUES LESS THAN ('2019-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201905 VALUES LESS THAN ('2019-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201906 VALUES LESS THAN ('2019-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201907 VALUES LESS THAN ('2019-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201908 VALUES LESS THAN ('2019-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201909 VALUES LESS THAN ('2019-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201910 VALUES LESS THAN ('2019-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201911 VALUES LESS THAN ('2019-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION part201912 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202001 VALUES LESS THAN ('2020-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202002 VALUES LESS THAN ('2020-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202003 VALUES LESS THAN ('2020-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202004 VALUES LESS THAN ('2020-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202005 VALUES LESS THAN ('2020-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202006 VALUES LESS THAN ('2020-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202007 VALUES LESS THAN ('2020-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202008 VALUES LESS THAN ('2020-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202009 VALUES LESS THAN ('2020-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202010 VALUES LESS THAN ('2020-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202011 VALUES LESS THAN ('2020-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202012 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202101 VALUES LESS THAN ('2021-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202102 VALUES LESS THAN ('2021-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202103 VALUES LESS THAN ('2021-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202104 VALUES LESS THAN ('2021-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202105 VALUES LESS THAN ('2021-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202106 VALUES LESS THAN ('2021-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202107 VALUES LESS THAN ('2021-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202108 VALUES LESS THAN ('2021-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202109 VALUES LESS THAN ('2021-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202110 VALUES LESS THAN ('2021-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202111 VALUES LESS THAN ('2021-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202112 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202201 VALUES LESS THAN ('2022-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202202 VALUES LESS THAN ('2022-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202203 VALUES LESS THAN ('2022-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202204 VALUES LESS THAN ('2022-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202205 VALUES LESS THAN ('2022-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202206 VALUES LESS THAN ('2022-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202207 VALUES LESS THAN ('2022-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202208 VALUES LESS THAN ('2022-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202209 VALUES LESS THAN ('2022-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202210 VALUES LESS THAN ('2022-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202211 VALUES LESS THAN ('2022-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION part202212 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = InnoDB);

 
SHOW CREATE TABLE d_agents;
CREATE TABLE `d_agents` (
 `agent_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `agent` varchar(10000) NOT NULL,
 `os_fk` mediumint(8) unsigned NOT NULL,
 `browser_fk` mediumint(8) unsigned NOT NULL,
 PRIMARY KEY (`agent_id`),
 KEY `d_agents_agent` (`agent`(100))
) ENGINE=InnoDB AUTO_INCREMENT=1269050 DEFAULT CHARSET=utf8;


SHOW TABLE STATUS LIKE 'f_sessions';
'f_sessions', 'InnoDB', '10', 'Compact', '39293238', '131', '5165842432', '0', '882147328', '1338840711168', '39562422', NULL, NULL, NULL, 'utf8_general_ci', NULL, 'partitioned', ''


SHOW TABLE STATUS LIKE 'd_agents';
'd_agents', 'InnoDB', '10', 'Compact', '1254567', '238', '299646976', '0', '229638144', '1851785216', '1269050', '2011-05-03 10:13:08', NULL, NULL, 'utf8_general_ci', NULL, '', ''


EXPLAIN SELECT DISTINCT a.* FROM d_agents a INNER JOIN f_sessions s ON a.agent_id = s.agent_fk AND s.begin_timestamp >= '2010-01-01 00:00:00' AND s.begin_timestamp <= '2011-05-25 23:59:59' AND s.bot_fk IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32);
'1', 'SIMPLE', 's', 'ALL', NULL, NULL, NULL, NULL, '39614573', 'Using where; Using temporary'
'1', 'SIMPLE', 'a', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'dw20.s.agent_fk', '1', ''


EXPLAIN PARTITIONS SELECT DISTINCT a.* FROM d_agents a INNER JOIN f_sessions s ON a.agent_id = s.agent_fk AND s.begin_timestamp >= '2010-01-01 00:00:00' AND s.begin_timestamp <= '2011-05-25 23:59:59' AND s.bot_fk IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32);
'1', 'SIMPLE', 's', 'part201001_part201001sp0,part201001_part201001sp1,part201001_part201001sp2,part201002_part201002sp0,part201002_part201002sp1,part201002_part201002sp2,part201003_part201003sp0,part201003_part201003sp1,part201003_part201003sp2,part201004_part201004sp0,part201004_part201004sp1,part201004_part201004sp2,part201005_part201005sp0,part201005_part201005sp1,part201005_part201005sp2,part201006_part201006sp0,part201006_part201006sp1,part201006_part201006sp2,part201007_part201007sp0,part201007_part201007sp1,part201007_part201007sp2,part201008_part201008sp0,part201008_part201008sp1,part201008_part201008sp2,part201009_part201009sp0,part201009_part201009sp1,part201009_part201009sp2,part201010_part201010sp0,part201010_part201010sp1,part201010_part201010sp2,part201011_part201011sp0,part201011_part201011sp1,part201011_part201011sp2,part201012_part201012sp0,part201012_part201012sp1,part201012_part201012sp2,part201101_part201101sp0,part201101_part201101sp1,part201101_part201101sp2,part201102_part201102sp0,part201102_part201102sp1,part201102_part201102sp2,part201103_part201103sp0,part201103_part201103sp1,part201103_part201103sp2,part201104_part201104sp0,part201104_part201104sp1,part201104_part201104sp2,part201105_part201105sp0,part201105_part201105sp1,part201105_part201105sp2', 'ALL', NULL, NULL, NULL, NULL, '39614573', 'Using where; Using temporary'
'1', 'SIMPLE', 'a', NULL, 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'dw20.s.agent_fk', '1', ''


SHOW VARIABLES LIKE '%buffer%';
'bulk_insert_buffer_size', '8388608'
'innodb_buffer_pool_instances', '1'
'innodb_buffer_pool_size', '15032385536'
'innodb_change_buffering', 'all'
'innodb_log_buffer_size', '14680064'
'join_buffer_size', '131072'
'key_buffer_size', '334495744'
'myisam_sort_buffer_size', '429916160'
'net_buffer_length', '16384'
'preload_buffer_size', '32768'
'read_buffer_size', '65536'
'read_rnd_buffer_size', '262144'
'sort_buffer_size', '262144'
'sql_buffer_result', 'OFF'

We have an index in the d_agents in order to be used by ETL when it search whether a dimension is created or has to be created, at insert time.

Most of the queries use only one bot, and then mysql only search in one subpartition not in all of them, but the problem is with queries for multiple bots.

The system is a development server with 54 GB RAM and a lot of apps, I estimated that we will need a system with at least 16 GBytes of RAM only for MySQL server in production.

Thanks in advance.

Options: ReplyQuote


Subject
Views
Written By
Posted
6205
May 27, 2011 06:14AM
3037
May 28, 2011 10:49PM
Re: Optimizing it even more
2567
May 30, 2011 08:57AM
2854
May 30, 2011 07:15PM
2899
May 31, 2011 10:09AM
2861
May 31, 2011 08:53PM
2924
June 01, 2011 08:42AM
3099
June 01, 2011 09:15AM
2955
June 02, 2011 02:13AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.