Shantanu's Blog

Since I am using archive table in order to compress the data, I can't use any keys and still make the select query fast by taking advantage of the partitions.
You can create regular MyISAM or InnoDB tables with regular keys and use both, keys + partitions.
Sounds intereting, right?

drop table tr;

CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE) engine=archive
PARTITION BY RANGE( YEAR(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (2005)
);

INSERT INTO tr VALUES
(1, 'desk organiser', '2003-10-15'),
(2, 'CD player', '1993-11-05'),
(3, 'TV set', '1996-03-10'),
(4, 'bookcase', '1982-01-10'),
(5, 'exercise bike', '2004-05-09'),
(6, 'sofa', '1987-06-05'),
(7, 'popcorn maker', '2001-11-22'),
(8, 'aquarium', '1992-08-04'),
(9, 'study desk', '1984-09-16'),
(10, 'lava lamp', '1998-12-25');

SELECT * FROM tr WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';

mysql> explain partitions SELECT * FROM tr WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tr
partitions: p2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10
Extra: Using where

mysql> SELECT * FROM tr WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';
+------+-----------+------------+
| id | name | purchased |
+------+-----------+------------+
| 3 | TV set | 1996-03-10 |
| 10 | lava lamp | 1998-12-25 |
+------+-----------+------------+

If you have a key on purchased column and the table type is MyISAM or InnoDB then the explain plan will look like this...

mysql> explain partitions SELECT purchased FROM tr WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tr
partitions: p2
type: range
possible_keys: purchased
key: purchased
key_len: 4
ref: NULL
rows: 1
Extra: Using where; Using index

The following query will let me know about the details of partitions from information schema...

mysql> SELECT TABLE_SCHEMA AS db, TABLE_NAME AS tb, PARTITION_NAME AS pName, TABLE_ROWS AS rows, PARTITION_EXPRESSION AS criterion, PARTITION_DESCRIPTION as ulimit, PARTITION_METHOD as method, CREATE_TIME as ctime, UPDATE_TIME as mtime FROM information_schema.partitions WHERE table_name = 'tr';
+-----------+----+-------+------+------------------+--------+--------+---------------------+---------------------+
| db | tb | pName | rows | criterion | ulimit | method | ctime | mtime |
+-----------+----+-------+------+------------------+--------+--------+---------------------+---------------------+
| shantanuo | tr | p0 | 3 | YEAR(purchased) | 1990 | RANGE | 2008-10-12 13:55:23 | 2008-10-12 13:55:24 |
| shantanuo | tr | p1 | 2 | YEAR(purchased) | 1995 | RANGE | 2008-10-12 13:55:23 | 2008-10-12 13:55:24 |
| shantanuo | tr | p2 | 2 | YEAR(purchased) | 2000 | RANGE | 2008-10-12 13:55:23 | 2008-10-12 13:55:24 |
| shantanuo | tr | p3 | 3 | YEAR(purchased) | 2005 | RANGE | 2008-10-12 13:55:23 | 2008-10-12 13:55:24 |
+-----------+----+-------+------+------------------+--------+--------+---------------------+---------------------+
4 rows in set (56.70 sec)

I tried it on a big test table and found that one should use partitions especially with archive table types. Since archive does not support indexes, some of the queries are very slow. But now I can take advantage of partitions and I do not need indexes because MySQL will fetch the data from a relatively small file. See the example given below.

The MyISAM table with basic indexes consumed 20 GB to store 72,88,953 rows.

-rw-r----- 1 mysql mysql 11K Oct 14 17:17 Feedback.frm
-rw-r----- 1 mysql mysql 20G Oct 14 17:48 Feedback.MYD
-rw-r----- 1 mysql mysql 567M Oct 14 17:49 Feedback.MYI

The archive table is less than 2 GB (<90%) and partitioning broke it up to 9 files of about 300 MB each.

-rw-rw---- 1 mysql mysql 11K Oct 15 17:46 Feedback_archive2.frm
-rw-rw---- 1 mysql mysql 76 Oct 15 17:46 Feedback_archive2.par
-rw-rw---- 1 mysql mysql 88 Oct 15 17:46 Feedback_archive2#P#p803.ARZ
-rw-rw---- 1 mysql mysql 23M Oct 16 03:30 Feedback_archive2#P#p804.ARZ
-rw-rw---- 1 mysql mysql 108M Oct 16 03:30 Feedback_archive2#P#p805.ARZ
-rw-rw---- 1 mysql mysql 352M Oct 16 03:30 Feedback_archive2#P#p806.ARZ
-rw-rw---- 1 mysql mysql 308M Oct 16 03:30 Feedback_archive2#P#p807.ARZ
-rw-rw---- 1 mysql mysql 305M Oct 16 03:30 Feedback_archive2#P#p808.ARZ
-rw-rw---- 1 mysql mysql 375M Oct 16 03:30 Feedback_archive2#P#p809.ARZ
-rw-rw---- 1 mysql mysql 366M Oct 16 03:30 Feedback_archive2#P#p810.ARZ
-rw-rw---- 1 mysql mysql 134M Oct 16 03:30 Feedback_archive2#P#p811.ARZ

If I remove some of the unnecessary columns from the table then the size of the table is again reduced by 1GB as shown below.

-rw-rw---- 1 mysql mysql 15K Oct 14 18:41 Feedback_archive.frm
-rw-rw---- 1 mysql mysql 76 Oct 14 18:41 Feedback_archive.par
-rw-rw---- 1 mysql mysql 88 Oct 14 18:41 Feedback_archive#P#p803.ARZ
-rw-rw---- 1 mysql mysql 11M Oct 15 03:30 Feedback_archive#P#p804.ARZ
-rw-rw---- 1 mysql mysql 47M Oct 15 03:30 Feedback_archive#P#p805.ARZ
-rw-rw---- 1 mysql mysql 155M Oct 15 03:30 Feedback_archive#P#p806.ARZ
-rw-rw---- 1 mysql mysql 146M Oct 15 03:30 Feedback_archive#P#p807.ARZ
-rw-rw---- 1 mysql mysql 149M Oct 15 03:30 Feedback_archive#P#p808.ARZ
-rw-rw---- 1 mysql mysql 169M Oct 15 03:30 Feedback_archive#P#p809.ARZ
-rw-rw---- 1 mysql mysql 154M Oct 15 03:30 Feedback_archive#P#p810.ARZ
-rw-rw---- 1 mysql mysql 58M Oct 15 03:30 Feedback_archive#P#p811.ARZ

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'Feedback_archive';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p803 | 0 |
| p804 | 85679 |
| p805 | 390157 |
| p806 | 1319141 |
| p807 | 1229578 |
| p808 | 1276819 |
| p809 | 1287831 |
| p810 | 1164843 |
| p811 | 534905 |
+----------------+------------+
9 rows in set (0.00 sec)

And the relevant extract of the create table statement looks like this...

) ENGINE=ARCHIVE PARTITION BY RANGE (extract(year_month from (FeedDate)))
(PARTITION p803 VALUES LESS THAN (200803),
PARTITION p804 VALUES LESS THAN (200804),
PARTITION p805 VALUES LESS THAN (200805),
PARTITION p806 VALUES LESS THAN (200806),
PARTITION p807 VALUES LESS THAN (200807),
PARTITION p808 VALUES LESS THAN (200808),
PARTITION p809 VALUES LESS THAN (200809),
PARTITION p810 VALUES LESS THAN (200810),
PARTITION p811 VALUES LESS THAN (200811))

Labels: mysql 5.1, mysql case study