Re: Partition Pruning Issue while using Day() function in Range
First of all Thanks for the reply and sorry for the late response
After going through the above two emails i had modified the table structure following
CREATE TABLE Messages_New (
id VARCHAR(2047),
time DATETIME NOT NULL,
sender VARCHAR(2047),
mvno TinyInt Unsigned,
protocol TinyInt Unsigned,
filter_list TinyInt Unsigned,
subject Text,
searchable_body Text,
active_filters VARCHAR(100),
num_attachments TinyInt Unsigned,
result Bit,
sender_imsi VARCHAR(2047),
#Can be modified to store 2 letter country code replacing entire name
originating_country VARCHAR(100),
destination_country VARCHAR(100),
#Can be modified to store unique foreign key identity of network name
originating_network VARCHAR(200),
destination_network VARCHAR(200),
map_smsc VARCHAR(10),
#Additional Fields
preview Text,
sms_message_type VARCHAR(10),
sender_entity VARCHAR(100),
recipient_entity VARCHAR(100),
submission_delivery_result Bit,
filter_ids VARCHAR(100),
direction VARCHAR(100),
KEY `IDX_EVENT_ID` (`id`),
KEY `IDX_EVENT_TIME` (`time`),
KEY `IDX_SENDER` (`sender`)
) ENGINE=InnoDB
PARTITION BY RANGE ( TO_DAYS(`time`) )
(
PARTITION P_Day_0 VALUES LESS THAN (0),
PARTITION P_Day_0901 VALUES LESS THAN (TO_DAYS('2011-09-01')),
PARTITION P_Day_0902 VALUES LESS THAN (TO_DAYS('2011-09-02')),
....
PARTITION P_Day_0929 VALUES LESS THAN (TO_DAYS('2011-09-29')),
PARTITION P_Day_0930 VALUES LESS THAN (TO_DAYS('2011-09-30'))
);
CREATE TABLE Messages_Recipients_New (
id VARCHAR(2047),
time DATETIME NOT NULL,
recipient VARCHAR(2047),
recipient_imsi VARCHAR(2047),
KEY `IDX_EVENT_ID` (`id`),
KEY `IDX_RECIPIENT` (`recipient`)
) ENGINE=InnoDB
PARTITION BY RANGE ( TO_DAYS(`time`) )
(
PARTITION P_Day_0 VALUES LESS THAN (0),
PARTITION P_Day_0901 VALUES LESS THAN (TO_DAYS('2011-09-01')),
PARTITION P_Day_0902 VALUES LESS THAN (TO_DAYS('2011-09-02')),
....
PARTITION P_Day_0929 VALUES LESS THAN (TO_DAYS('2011-09-29')),
PARTITION P_Day_0930 VALUES LESS THAN (TO_DAYS('2011-09-30'))
);
Following is the requirement
1. On an average 65Million records will be inserted per day.
2. The data needs to be stored for period of 7 days minimum and maximum of 60days (based on the configuration the old data needs to be purged). This was my main reason for choosing partition.
3. Insertion, querying and partitioning dropping are the only operations performed, no updating or deleting a single row or rows is never performed.
4. Following are the query fields Time, Sender, Recipients, flist, mvno and subject
1.Time and Sender
2.Time and Recipients
3.Time and Sender and Recipients
4.Other combinations using Time with filst or mvno
5.Time and Subject (mostly part of the subject)
5. Mostly option 1 and 2 will be used on given a day data.
6. Acceptable response time should be less than 10 seconds
Queries:
1. Is my indexes right? Or should I use (time,sender) and (time,Recipients) pairs ..
2. Should I use innodb or myisam or archive databases (as there is no need to transactional management)
3. How to optimize partial value search on subject or searchable_body column
4. Compress additional columns data compressed into single column which will result in less storage area. is this a good option ?