title: Why mariadb partitioning doesn't give me better performance?
body: <p>Greeting.</p>
  
  <p>Let me show my table scheme first:</p>
  
  <pre><code>CREATE TABLE `log_table` (
  `rid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `dataId` int(10) unsigned NOT NULL DEFAULT '0',
  `memberId` int(10) unsigned NOT NULL DEFAULT '0',
  `clientId` int(10) unsigned NOT NULL DEFAULT '0',
  `qty` int(11) NOT NULL DEFAULT '0',
  `timestamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `typeA` tinyint(2) DEFAULT NULL,
  `typeB` int(11) DEFAULT '0',
  PRIMARY KEY (`rid`,`timestamp`),
  KEY `idx_report1` (`timestamp`,`memberId`,`dataId`),
  KEY `idx_report2` (`memberId`,`timestamp`),
  KEY `idx_report3` (`dataId`,`timestamp`,`rid`),
  KEY `idx_report4` (`timestamp`,`typeB`,`typeA`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  PARTITION BY RANGE (year(`timestamp`))
  (PARTITION p2014 VALUES LESS THAN (2015),
   PARTITION p2015 VALUES LESS THAN (2016)
  );
  </code></pre>
  
  <p>I'm using MariaDB 5.5 and this table contains 25 million records, so I decided to make partitions in the table for preventing performance issue may occur in the near future.
  You may see it's time serial, log data, and having 4 views. For example, one of the views uses following query:</p>
  
  <pre><code>select typeB, typeA, count(*) as number from log_table where timestamp between '2015-1-1' and '2015-2-1' group by typeB, typeA;
  </code></pre>
  
  <p>AFAIK, this query loads the data from p2015 only by partition pruning. But I saw there is not much difference between original table and partition-version in query execution time. (avg 1.94 sec vs 1.95 sec)</p>
  
  <p>Hm, I thought it's might influenced by number of rows in each partition. then how about smaller size of partition? to_days()?</p>
  
  <pre><code>PARTITION BY RANGE (to_days(`timestamp`))
  (
  ...
  PARTITION p_2015_01 VALUES LESS THAN (to_days('2015-2-1')),
  PARTITION p_2015_02 VALUES LESS THAN (to_days('2015-3-1'))
  ...
  )
  </code></pre>
  
  <p>Well, there's no effect. Could you let me know what's my missing piece?</p>
  
  <hr>
  
  <p>EDIT: sorry for my error in the query.. btw, EXPLAIN PARTITION doesn't help me.</p>
  
  <p>and result of explain both tables are : </p>
  
  <pre><code>// original
  +------+-------------+-----------+-------+-------------------------+-------------+---------+------+---------+-----------------------------------------------------------+
  | id   | select_type | table     | type  | possible_keys           | key         | key_len | ref  | rows    | Extra                                                     |
  +------+-------------+-----------+-------+-------------------------+-------------+---------+------+---------+-----------------------------------------------------------+
  |    1 | SIMPLE      | org_table | range | idx_report1,idx_report4 | idx_report4 | 8       | NULL | 8828000 | Using where; Using index; Using temporary; Using filesort |
  +------+-------------+-----------+-------+-------------------------+-------------+---------+------+---------+-----------------------------------------------------------+
  
  
  //partition
  +------+-------------+-----------+-------+-------------------------+-------------+---------+------+---------+-----------------------------------------------------------+
  | id   | select_type | table     | type  | possible_keys           | key         | key_len | ref  | rows    | Extra                                                     |
  +------+-------------+-----------+-------+-------------------------+-------------+---------+------+---------+-----------------------------------------------------------+
  |    1 | SIMPLE      | log_table | range | idx_report1,idx_report4 | idx_report4 | 8       | NULL | 7902646 | Using where; Using index; Using temporary; Using filesort |
  +------+-------------+-----------+-------+-------------------------+-------------+---------+------+---------+-----------------------------------------------------------+
  </code></pre>