MySQL Forums - Partitioning

IP vs Date Partition (2 replies)

Tim Linden — Wed, 15 May 2013 16:06:42 +0000

Hey Guys

I've started to use parititons and am seeing the speed benefits, but I'm unsure if I'm doing it the best way.

I'm tracking data on the last time an IP took an action. Right now to save space it simply updates the last time field and other data each time they do the action again. I'm then dumping any IPs that have been over 30 days.

My partition is currently based on IP address. Which has improved the speed in tracking, but I'm not benefiting from faster drop offs over that 30 days.

I'm hesistent to try partitioning based on date because then it's 30 partitions on each update to find that IP, and if it's been a few days and I update the record to today's date it's gotta move that record which adds more latency.

So your thoughts? Or am I overestimating how long it takes to check each partition or move data between them?

Thanks!

Tim

How should I partition this table? (9 replies)

Ron Bergin — Fri, 03 May 2013 15:32:19 +0000

This is my first experience with partitioning as well as managing a db at this level.

Breif background. We recently migrated our pervasive db to mysql. In that process we (I should say others in our dept) partitioned it by hash on the date field. innodb_file_per_table was not enabled, so all of the tablespace data is in ibdata1. The table has over 1 billion rows and ibdata1 is 800GB.

I'm currently in the process of copying the db to a test server to test different partitioning schemes and have enabled innodb_file_per_table. The structure of the table is as follows.

INVITMD | CREATE TABLE `INVITMD` (
`Division` smallint(5) unsigned DEFAULT NULL,
`InvNum` int(10) unsigned DEFAULT NULL,
`InvLine` smallint(5) unsigned DEFAULT NULL,
`PLU` int(10) unsigned DEFAULT NULL,
`UPC` varchar(14) DEFAULT NULL,
`Qty` smallint(6) DEFAULT NULL,
`ItemPrice` double DEFAULT NULL,
`PriceCode` char(1) DEFAULT NULL,
`NetPrice` double DEFAULT NULL,
`DiscountFlag` char(1) DEFAULT NULL,
`Taxable` char(1) DEFAULT NULL,
`ComShr` char(1) DEFAULT NULL,
`QotNum` int(10) unsigned DEFAULT NULL,
`QotVer` smallint(5) unsigned DEFAULT NULL,
`QotLine` smallint(5) unsigned DEFAULT NULL,
`PrimaryAscID` int(10) unsigned DEFAULT NULL,
`ComSplDate` date DEFAULT NULL,
`ComSplTime` time DEFAULT NULL,
`SplitAscID` int(10) unsigned DEFAULT NULL,
`ItemCost` double DEFAULT NULL,
`InvDate` date DEFAULT NULL,
`Expansion` text,
UNIQUE KEY `Div_date_Line` (`Division`,`InvDate`,`InvNum`,`InvLine`),
KEY `PluNum` (`Division`,`PLU`),
KEY `Tax` (`Division`,`Taxable`),
KEY `UPC` (`Division`,`UPC`),
KEY `PrimaryAsc` (`Division`,`PrimaryAscID`),
KEY `SplitAsc` (`Division`,`SplitAscID`),
KEY `Div_Plu_inv` (`Division`,`PLU`,`InvNum`),
KEY `Div_Date_Plu` (`Division`,`InvDate`,`PLU`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (YEAR(invdate))
PARTITIONS 10 */

My initial plan is to partition by range or list on the Division field giving me between 36 and 40 partitions. I'm also considering sub partitioning on the InvDate field. I'd like to do that by range as well, but from my reading subpartitions don't support "by range". Is that correct?

Does my plan sound like the best approach, or can you recommend something better?

Event add partition works but not the drop partition (1 reply)

Frwa Onto — Fri, 05 Apr 2013 10:13:57 +0000

We have set an event as below. What we notice is that the add partition is working well as we can see on a daily basis the partition list is growing but the drop partition is not work any reason for this? I have checked nothing in the log showing any error regarding to this event.

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.PARTITIONS 
  WHERE TABLE_NAME = 'testPart1' AND PARTITION_NAME = 
  CONCAT(' p'
    ,   DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL 14 DAY ), '%Y%m%d' ))) THEN  
    SET @stmt := CONCAT(
        'ALTER TABLE testPart1 DROP PARTITION '
    ,   ' p'
    ,   DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL 14 DAY ), '%Y%m%d' ) 
    );
    PREPARE stmt FROM @stmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;  
  END IF; 

  IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.PARTITIONS 
  WHERE TABLE_NAME = 'testPart1' AND PARTITION_NAME = 
  CONCAT(' p'
    ,   DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL 2 DAY ), '%Y%m%d' ))) THEN   
  SET @stmt := CONCAT(
        'ALTER TABLE testPart1 ADD PARTITION ('
    ,   'PARTITION p'
    ,   DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL 2 DAY ), '%Y%m%d' )
    ,   ' VALUES LESS THAN ('
    ,   TO_DAYS( CURDATE() ) + 2 
    ,   '))'
    );
    PREPARE stmt FROM @stmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt; 
  END IF;

Performance doubt (1 reply)

Xavier Borrut — Thu, 28 Mar 2013 06:34:14 +0000

Hello,

I'm developing an app where we'll have a lot of data and I think data partition will help to make the queries run faster. But we are not sure what type of partition to use.
We want to part our table by a year-month basis, because the queries will always query data within the same month of a given year.

To ease the process, we are going to create an int field named "yearmonth" and create an index and the partition with it. The field values will be the year and month concatenated yyyymm; for example, for the date 18/01/2013 the value will be 201301.

We want to have a partition for every month, and we are not sure what's best.

OPTION 1: Range partition

PARTITION BY RANGE ( yearmonth ) (
PARTITION p0 VALUES LESS THAN (201301),
PARTITION p1 VALUES LESS THAN (201302),
PARTITION p2 VALUES LESS THAN (201303),
...
PARTITION pN VALUES LESS THAN MAXVALUE
);

Pros: We have the exact partition number we need.
Cons: We'll have to add a new partition set every time we approach the last yearmonth.

OPTION 2: Key partition

PARTITION BY KEY( yearmonth )
PARTITIONS 1024;

Pros: No need of any maintenance for the next 85 years.
Cons: Lots of empty partitions.

Note: 1024 it's an limit case, we can use something like 120 to store 10 years of data.

Our doubt is wether to use key or range partition, mainly because of the "unused" partitions. Will this extra partitions slow down the queries of affect any other processes? Just FYI, we know that we will have to use the yearmonth field as the first WHERE condition in order to select the right partition number but again, still not know if the empty partitions can affect us in a wrong way.

Many thanks in advance,

Xavier Borrut

Should I partition? (3 replies)

Jacob Steinberger — Fri, 05 Apr 2013 05:35:29 +0000

I'm looking to have a database that processes 20G worth of CDRs per day, with a revolving window of a month. I was thinking about setting up partitioning based on day, and then compressing each partition after that day passes in order to speed up searching and limit the amount of physical storage is required. Before the day comes back to current, I'd truncate the table.

From what I can find, compressing partitions isn't an option.

I then thought about doing an Archive storage type, but that sounds like it will help with storage space but can (greatly) increase SELECT times due to the lack of indexing.

At this point, I'm wondering if I'm going to have to do something more non-standard in order to insert into separate tables, compress those tables when I'm done inserting, then write a front-end that would select from multiple tables (if needed). Then more junk to handle truncation.

Any suggestions (from a generic level) to manage this kind of task? Can you compress a single partition out of a set (and I'm just not finding the documentation)?

Edit: I should add that the biggest limitation I have right now is drive space (call it lack of funding for a pet project).

Partitioned table problem after upgrade to 5.5 (4 replies)

Ming Wang — Fri, 08 Mar 2013 08:20:17 +0000

hi All
i have a hash partitioned table in MySQL 5.1.
and there is a compound unique index on this table.
i stop mysql 5.1 service and copy all the data to the mysql 5.5 server.
and all other tables are working well except this partitioned table.
i try to use a string to query the record in this partitioned table, it returns to me no record. The string column is one of the unique index column.
but definately there is a record there because i can query from the ID.
anyone met this problem?
and anyone know how to solve this?
thanks.
Jack

If you use a partition table in mysql5.5, Contact function (1 reply)

kyung gi lee — Thu, 28 Feb 2013 15:34:50 +0000

I have been using mysql5.5. I'm trying to use the partition table.
Out with this partition function is not allowed to try to use the syntax PARTITION BY LIST (i% s')) DAYOFYEAR (STR_TO_DATE (logintime, "% Y% m% d% H%).
I think it is what you need to check?
Thank you.

How to (temporary) disable one partition in the partitioned table (2 replies)

First Bell — Thu, 28 Feb 2013 11:51:03 +0000

Dear MySQL Guru,
Is it possible to disable ('turn off') one partition in the partitioned table (just like it is possible with Oracle)? How can I do this? (and how to 'turn it on' after that).

That is wanted to simulate fail of a part of the system.

FREE! MySQL Health Check (no replies)

Edwin Desouza — Fri, 08 Feb 2013 04:18:44 +0000

FREE! MySQL Health Check
https://www.mysql.com/news-and-events/health-check/

MySQL Partitioning showing low performance (2 replies)

Manosh Manohar — Fri, 08 Feb 2013 06:39:19 +0000

I was trying to check whether implementing MySQL database partitioning is beneficial for our application or not. I have heard a lot about the benefits of using partitioning for large number of records.
But surprisingly, the response time of the application got reduced by 3 times when doing the load testing after partitioning was implemented. Could someone please help with the reason why this may happen?

Let me explain in detail:

Below is the DDL of the table when partitioning was ‘not’ in place.

CREATE TABLE `myTable` (
`column1` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`column2` char(3) NOT NULL,
`column3` char(3) NOT NULL,
`column4` char(2) NOT NULL,
`column5` smallint(4) unsigned NOT NULL,
`column6` date NOT NULL,
`column7` varchar(2) NOT NULL,
`column8` tinyint(3) unsigned NOT NULL COMMENT 'Seat Count Ranges from 0-9.',
`column9` varchar(2) NOT NULL,
`column10` varchar(4) NOT NULL,
`column11` char(2) NOT NULL,
`column12` datetime NOT NULL,
`column13` datetime DEFAULT NULL,
PRIMARY KEY (`column1`),
KEY `index1` (`column2`,`column3`,`column4`,`column5`,`column7`,`column6`),
KEY `index2` (`column2`,`column3`,`column6`,`column4`)
) ENGINE=InnoDB AUTO_INCREMENT=342024674 DEFAULT CHARSET=latin1;

And below is the DDL of the same table after implementing ‘Range’ partitioning based on a date field.

CREATE TABLE `myTable` (
`column1` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`column2` char(3) NOT NULL,
`column3` char(3) NOT NULL,
`column4` char(2) NOT NULL,
`column5` smallint(4) unsigned NOT NULL,
`column6` date NOT NULL,
`column7` varchar(2) NOT NULL,
`column8` tinyint(3) unsigned NOT NULL COMMENT 'Seat Count Ranges from 0-9.',
`column9` varchar(2) NOT NULL,
`column10` varchar(4) NOT NULL,
`column11` char(2) NOT NULL,
`column12` datetime NOT NULL,
`column13` datetime DEFAULT NULL,
PRIMARY KEY (`column1`,`column6`),
KEY `index1` (`column2`,`column3`,`column4`,`column5`,`column7`,`column6`),
KEY `index2` (`column2`,`column3`,`column6`,`column4`)
) ENGINE=InnoDB AUTO_INCREMENT=342024674 DEFAULT CHARSET=latin1
PARTITION BY RANGE COLUMNS(`column6`)
(PARTITION date_jul_11 VALUES LESS THAN ('2011-08-01') ENGINE = InnoDB,
PARTITION date_aug_11 VALUES LESS THAN ('2011-09-01') ENGINE = InnoDB,
PARTITION date_sep_11 VALUES LESS THAN ('2011-10-01') ENGINE = InnoDB,
PARTITION date_oct_11 VALUES LESS THAN ('2011-11-01') ENGINE = InnoDB,
PARTITION date_nov_11 VALUES LESS THAN ('2011-12-01') ENGINE = InnoDB,
PARTITION date_dec_11 VALUES LESS THAN ('2012-01-01') ENGINE = InnoDB,
PARTITION date_jan_12 VALUES LESS THAN ('2012-02-01') ENGINE = InnoDB,
PARTITION date_feb_12 VALUES LESS THAN ('2012-03-01') ENGINE = InnoDB,
PARTITION date_mar_12 VALUES LESS THAN ('2012-04-01') ENGINE = InnoDB,
PARTITION date_apr_12 VALUES LESS THAN ('2012-05-01') ENGINE = InnoDB,
PARTITION date_may_12 VALUES LESS THAN ('2012-06-01') ENGINE = InnoDB,
PARTITION date_jun_12 VALUES LESS THAN ('2012-07-01') ENGINE = InnoDB,
PARTITION date_jul_12 VALUES LESS THAN ('2012-08-01') ENGINE = InnoDB,
PARTITION date_aug_12 VALUES LESS THAN ('2012-09-01') ENGINE = InnoDB,
PARTITION date_sep_12 VALUES LESS THAN ('2012-10-01') ENGINE = InnoDB,
PARTITION date_oct_12 VALUES LESS THAN ('2012-11-01') ENGINE = InnoDB,
PARTITION date_nov_12 VALUES LESS THAN ('2012-12-01') ENGINE = InnoDB,
PARTITION date_dec_12 VALUES LESS THAN ('2013-01-01') ENGINE = InnoDB,
PARTITION date_jan_13 VALUES LESS THAN ('2013-02-01') ENGINE = InnoDB,
PARTITION date_feb_13 VALUES LESS THAN ('2013-03-01') ENGINE = InnoDB,
PARTITION date_mar_13 VALUES LESS THAN ('2013-04-01') ENGINE = InnoDB,
PARTITION date_apr_13 VALUES LESS THAN ('2013-05-01') ENGINE = InnoDB,
PARTITION date_may_13 VALUES LESS THAN ('2013-06-01') ENGINE = InnoDB,
PARTITION date_jun_13 VALUES LESS THAN ('2013-07-01') ENGINE = InnoDB,
PARTITION date_oth VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);

Below is a sample query which was used for doing the load testing to test the performance.

SELECT column8, column9 FROM myTable WHERE column2 = ? AND column3 = ? AND column4 =? AND column5 = ? AND column7 = ? AND column6 = ? LIMIT 1;

The ‘?’ above were replaced with real values present in the database for testing.

Please note that the number of records in ‘myTable’ table is around 342 million, and the number of test data used for doing the performance testing is about 2 million.

However, as I said, the performance after implementing partitioning was reduced by a shocking 3 times. Any idea what may have caused this?

Also, please let me know if doing any further change in the table structure or indexing may help resolve this issue.

Index with partitioning (6 replies)

Hélio Hélio — Thu, 14 Feb 2013 18:26:18 +0000

Hi,

I have mysql 5.5.15 version and I am using partitioning successfully. I have a 15 million records table by now.

There is a small performance issue on index enable after table load though.
It is a data warehouse project, and before loading records on the fact table, I disable the index for better insert performance. After inserting, I enable the index again, but this process of enabling index takes 5 minutes, I know it´s not that bad but I am concerned about that.

Am I doing this right? Is there any thing I should do to get more performance?

How to let the query run on one partition (1 reply)

Alax James — Fri, 11 Jan 2013 02:45:00 +0000

Hello,

I am using NDBCluster 7.1 and try to enhance system performance as high as possible.

I have one table TA as below,

create table TA(
USER_ALIAS varchar(100),
USER_ID varchar(100),
NAME varchar(50),
primary(USER_ALIAS)
) ENGINE=NDB;

USER_ALIAS and USER_ID is N:1, that is many USER_ALIAS maping to one USER_ID, e.g.
HAPPY-Boy --> 101010
North-Boy --> 101010
Locky-Boy --> 101010

The query ' select * from TA where USER_ID='101010' ' will run on all partitions, as the table is partitioned by USER_ALIAS which is the primary key.

What I want to do is to partition the table by USER_ID, so I do it as below,
create table TA(
USER_ALIAS varchar(100),
USER_ID varchar(100),
NAME varchar(50),
primary key (USER_ALIAS, USER_ID)
) ENGINE=NDB
partition by key(USER_ID);

then the query like
- select * from TA where USER_ID='101010'
- select * from TA where USER_ALIAS='HAPPY-Boy'
can run on just one partition.

However, MYSQL won't do that. To the query 'select * from TA where USER_ALIAS='HAPPY-Boy' would run on all partitions.

So, how can i do?

Thanks.

Alax

How to partition by City \ State (1 reply)

Noah Goodrich — Fri, 21 Dec 2012 04:00:39 +0000

I have a table with ~20M rows and every query against it will include state or city or a combination so I was thinking of partitioning the table using a combo of the two fields like so:

ALTER TABLE `test`.`test`
PARTITION BY RANGE COLUMNS (city, state) (
PARTITION p0 VALUES LESS THAN ('a','AE'),
PARTITION p1 VALUES LESS THAN ('c','AE'),
PARTITION p2 VALUES LESS THAN ('e','AE'),
PARTITION p3 VALUES LESS THAN ('g','AE'),
PARTITION p4 VALUES LESS THAN ('i','AE'),
PARTITION p5 VALUES LESS THAN ('k','AE'),
PARTITION p6 VALUES LESS THAN ('m','AE'),
PARTITION p7 VALUES LESS THAN ('o','AE'),
PARTITION p8 VALUES LESS THAN ('q','AE'),
PARTITION p9 VALUES LESS THAN ('s','AE'),
PARTITION p10 VALUES LESS THAN ('u','AE'),
PARTITION p11 VALUES LESS THAN ('w','AE'),
PARTITION p12 VALUES LESS THAN ('y','AE'),
PARTITION p13 VALUES LESS THAN ('a','AK'),
PARTITION p14 VALUES LESS THAN ('c','AK'),
PARTITION p15 VALUES LESS THAN ('e','AK'),
PARTITION p16 VALUES LESS THAN ('g','AK'),
PARTITION p17 VALUES LESS THAN ('i','AK'),
PARTITION p18 VALUES LESS THAN ('k','AK'),
PARTITION p19 VALUES LESS THAN ('m','AK'),
PARTITION p20 VALUES LESS THAN ('o','AK'),
PARTITION p21 VALUES LESS THAN ('q','AK'),
PARTITION p22 VALUES LESS THAN ('s','AK'),
PARTITION p23 VALUES LESS THAN ('u','AK'),
PARTITION p24 VALUES LESS THAN ('w','AK'),
PARTITION p25 VALUES LESS THAN ('y','AK'),
PARTITION p26 VALUES LESS THAN ('a','AL'),
PARTITION p27 VALUES LESS THAN ('c','AL'),
PARTITION p28 VALUES LESS THAN ('e','AL'),
PARTITION p29 VALUES LESS THAN ('g','AL'),
PARTITION p30 VALUES LESS THAN ('i','AL'),
PARTITION p31 VALUES LESS THAN ('k','AL'),
PARTITION p32 VALUES LESS THAN ('m','AL'),
PARTITION p33 VALUES LESS THAN ('o','AL'),
PARTITION p34 VALUES LESS THAN ('q','AL'),
PARTITION p35 VALUES LESS THAN ('s','AL'),
PARTITION p36 VALUES LESS THAN ('u','AL'),
PARTITION p37 VALUES LESS THAN ('w','AL'),
PARTITION p38 VALUES LESS THAN ('y','AL'),
PARTITION p39 VALUES LESS THAN ('a','AR'),
PARTITION p40 VALUES LESS THAN ('c','AR'),
PARTITION p41 VALUES LESS THAN ('e','AR'),
PARTITION p42 VALUES LESS THAN ('g','AR'),
PARTITION p43 VALUES LESS THAN ('i','AR'),
PARTITION p44 VALUES LESS THAN ('k','AR'),
PARTITION p45 VALUES LESS THAN ('m','AR'),
PARTITION p46 VALUES LESS THAN ('o','AR'),
PARTITION p47 VALUES LESS THAN ('q','AR'),
PARTITION p48 VALUES LESS THAN ('s','AR'),
PARTITION p49 VALUES LESS THAN ('u','AR'),
PARTITION p50 VALUES LESS THAN ('w','AR').....

However, this fails to work with this error:

ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

What would be a valid solution for partitioning this table?

Partition behaiour seems abnormal in range partition (1 reply)

Rahul K K — Thu, 20 Dec 2012 20:56:13 +0000

I have created a table using mysql partition using range and have inserted millions of data.

CREATE TABLE `PART_SAMPLE ` (
`TRANSACTION_ID` bigint(25) NOT NULL AUTO_INCREMENT,
`TASK_ID` int(11) DEFAULT NULL,
`STATUS_CODE` int(10) DEFAULT NULL,
`FIELD10` int(5) DEFAULT NULL,
KEY `TXN_ID` (`TRANSACTION_ID`),
KEY `TASK_IDX` (`TASK_ID`),
KEY `id_idx_task_status` (`TASK_ID`,`STATUS_CODE`),
KEY `IDX_STATUS` (`STATUS_CODE`),
KEY `Fld_idx` (`FIELD10`)
) ENGINE=MyISAM AUTO_INCREMENT=12249932 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (FIELD10)
(PARTITION p0 VALUES LESS THAN (0) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (1) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (2) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (3) ENGINE = MyISAM,
PARTITION p4 VALUES LESS THAN (4) ENGINE = MyISAM,
PARTITION p5 VALUES LESS THAN (5) ENGINE = MyISAM,
PARTITION p6 VALUES LESS THAN (6) ENGINE = MyISAM,
PARTITION p7 VALUES LESS THAN (7) ENGINE = MyISAM,
PARTITION p8 VALUES LESS THAN (8) ENGINE = MyISAM,
PARTITION p9 VALUES LESS THAN (9) ENGINE = MyISAM,
PARTITION p10 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */

**Each Field10(0-10) value is having 3 million data each. But when am executing a select query as this**

select TASK_ID,STATUS_CODE,count(*) from PART_SAMPLE where FIELD10=X group by TASK_ID,STATUS_CODE;

x can be any value in the partition

for x value 0,2,5,8 it is taking only 10 seconds to retrive result but for rest it is taking abount 50s to rerive the result. As per my understating since data is same for all Fields almost same time has to be taken for any Field10 value. Why this time difference is coming. Is it because the way i have used partitioning is wrong or some thing else?

partition by range on decimal type (5 replies)

Greg K. — Sat, 24 Nov 2012 03:03:55 +0000

hi experts,

I need your help in getting my partitioning working.
I have a problem with paritioning on DECIMAL column (in fact this should be DATE but I have no chance to change it).
So I use floor() to return integer. Column 'starttime' used in partitioning is used in primary key, so the requirement is met.

this is my code:
===============

mysql> desc bts_hour;
+-----------------------------------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------------------------------+---------------+------+-----+---------+-------+
| bts_gid | decimal(20,0) | NO | PRI | NULL | |
| starttime | decimal(14,0) | NO | PRI | NULL | | <-------- this is it
| bsc_gid | decimal(20,0) | NO | | NULL | |
| BSC_NAME | varchar(80) | YES | | NULL | |
| BTS_NAME | varchar(80) | YES | MUL | NULL | |
...

#added index
alter table test.bts_hour
add PRIMARY KEY (`bts_gid`,`starttime`),
add KEY `starttime` (`starttime`),
add KEY `bts_gid` (`bts_gid`),
add KEY `bts_name` (`BTS_NAME`);

select count(*) from test.bts_hour;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+

select min(starttime), max(starttime) from test.bts_hour;
+----------------+----------------+
| min(starttime) | max(starttime) |
+----------------+----------------+
| 2011033000 | 2011063018 |
+----------------+----------------+

# add partitiong
alter table test.bts_hour
partition by RANGE (floor(starttime)) (
partition p00 values less than (2011040100),
partition p01 values less than (2011050100),
partition p02 values less than (MAXVALUE)
);

#check ditribution of data among partitions
select partition_name part, partition_expression expr, partition_description val, partition_method meth, table_rows
from information_schema.partitions
where table_name = 'bts_hour'
and TABLE_SCHEMA = 'test';

+------+------------------+------------+-------+------------+
| part | expr | val | meth | table_rows |
+------+------------------+------------+-------+------------+
| p00 | floor(starttime) | 2011040100 | RANGE | 20352 |
| p01 | floor(starttime) | 2011050100 | RANGE | 297084 |
| p02 | floor(starttime) | MAXVALUE | RANGE | 682564 |
+------+------------------+------------+-------+------------+

===================

now, when I run 'explain', all the partitions are used instead of p01 that has the set of values I want!

explain partitions
SELECT bts_name, str_to_date(convert(STARTTIME, CHAR(10)), '%Y%m%d%H') as Date
FROM test.bts_hour
WHERE STARTTIME between 2011040200 and 2011040523
ORDER BY bts_name, starttime
\G

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: bts_hour
partitions: p00,p01,p02 <--------- here it is, instead of p01 only
type: range
possible_keys: starttime,starttime_bts_gid,starttime_bts_name
key: starttime_bts_name
key_len: 7
ref: NULL
rows: 26021
Extra: Using where; Using index; Using filesort
1 row in set (0.00 sec)

What's wrong with this approach?

Fortnight partitions (4 replies)

Srikanth Mamidi — Fri, 23 Nov 2012 06:04:30 +0000

Hi,

In our application we need to store users info with attributes like name,description,location etc. along with friends count and user message count.
We want to maintain the history of the friends count and messages count for a given user per fortnight. We would need upto around 6 months data(about 12 fortnights) of friends count and message count. Data older than 6 months should be deleted/truncated. Would partitioning be a good solution for this and if so what kind of partitioning on what column(s) would be useful?

Table definition is:

CREATE TABLE `user_data` (
`userID` int(10) unsigned NOT NULL,
`userName` varchar(45) DEFAULT NULL,
`location` varchar(60) DEFAULT NULL,
`friends_cnt` int(10) unsigned NOT NULL,
`messages_cnt` int(10) unsigned NOT NULL,
`geo_code` int(10) unsigned DEFAULT NULL,
`lastupd` bigint(20) unsigned DEFAULT '0',
PRIMARY KEY (`userID`),
KEY `userName_idx` (`userName`) USING BTREE,
KEY `geo_idx` (`geo_code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

--------------------------------------------------
2. InnoDB specific variables:
--------------------------------------------------
have_innodb YES
ignore_builtin_innodb OFF
innodb_adaptive_hash_index ON
innodb_additional_mem_pool_size 1048576
innodb_autoextend_increment 8
innodb_autoinc_lock_mode 1
innodb_buffer_pool_size 1073741824
innodb_checksums ON
innodb_commit_concurrency 0
innodb_concurrency_tickets 500
innodb_data_file_path ibdata1:10M:autoextend
innodb_data_home_dir
innodb_doublewrite ON
innodb_fast_shutdown 1
innodb_file_io_threads 4
innodb_file_per_table OFF
innodb_flush_log_at_trx_commit 0
innodb_flush_method
innodb_force_recovery 0
innodb_lock_wait_timeout 50
innodb_locks_unsafe_for_binlog OFF
innodb_log_buffer_size 1048576
innodb_log_file_size 50485760
innodb_log_files_in_group 2
innodb_log_group_home_dir ./
innodb_max_dirty_pages_pct 90
innodb_max_purge_lag 0
innodb_mirrored_log_groups 1
innodb_open_files 300
innodb_rollback_on_timeout OFF
innodb_stats_method nulls_equal
innodb_stats_on_metadata ON
innodb_support_xa ON
innodb_sync_spin_loops 20
innodb_table_locks ON
innodb_thread_concurrency 8
innodb_thread_sleep_delay 10000
innodb_use_legacy_cardinality_algorithm ON
--------------------------------------------------------

3. my.cnf content :
---------------------------------------------------------
[mysqld]
#symbolic-links=0
key_buffer_size=33554432
innodb_buffer_pool_size=1G
innodb_log_file_size=50485760
innodb_flush_log_at_trx_commit=0
tmp_table_size=100M
max_heap_table_size=70M
max_allowed_packet=32M
max_connections=400
#open_files_limit=8192
#table_cache=256
#innodb_file_per_table=ON

The SELECTS are straightforward where only the indexed columns are used in the where clause.

select * from user_data where user_name in(,...);
select * from user_data where userID =;

The table does not have any foreign key relationships and no joins are needed too for our SELECT queries.Above are the only indexes/keys. We have high freq updates/inserts into the above table around 2500 per minute.

We are using batch INSERTs of 1000 and we can't use LOAD DATA as we are using INSERT ... ON DUPLICATE KEY and LOAD DATA does not have similar ON DUPLICATE KEY functionality.

There is no replication involved and we do have couple of INSERT/UPDATE triggers.

milliseconds in mysql equivalent to java for Partitioning (2 replies)

Manjunath C — Wed, 14 Nov 2012 10:17:13 +0000

Hi,
We need to partition few of the tables growing larger. We have designed identifier (Through application)to have the 64 bit primary key with milliseconds(41 bits), 1 bit for signed and 22 bits sequence value to support partitioning. we would like to partition based on the primary key RANGE mainly to archive old data.
Here is our one of the ddl.
CREATE TABLE OB_ORDER
(
CREATED_DATE TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
CREATED_BY INT UNSIGNED NOT NULL,
LAST_MODIFIED_DATE TIMESTAMP NOT NULL,
LAST_MODIFIED_BY INT UNSIGNED NOT NULL,
ID BIGINT UNSIGNED NOT NULL ,
NAME VARCHAR(512),
ACC_ID INT UNSIGNED,
ORDER_NUMBER VARCHAR(32),
ORDER_DATE TIMESTAMP NULL DEFAULT NULL,
FULLFILL_DATE TIMESTAMP NULL DEFAULT NULL,
STATE INT,
PARTY_ROLE_ID INT UNSIGNED,
PRICE_EFFECTIVE_DATE TIMESTAMP NULL DEFAULT NULL,
TERM DECIMAL(38,10),
CONTRACT_ID INT UNSIGNED,
CONTRACT_VERSION VARCHAR(32),
ACC_CONTACT_ID INT UNSIGNED,
CONSTRAINT ORDER_PK
PRIMARY KEY(ID),
CONSTRAINT OB_ORDER_FK
FOREIGN KEY (CONTRACT_ID)
REFERENCES OB_CONTRACT(ID),
CONSTRAINT ORDER_NUMBER_UC
UNIQUE(ORDER_NUMBER)
) ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_general_ci;

Please provide me the function to get milliseconds equivaalent to what we get in java using (Calendar lCDateTime = Calendar.getInstance();
date = lCDateTime.getTimeInMillis();)

I tried with SELECT UNIX_TIMESTAMP(); but when cross verified it with FROM_UNIXTIME function it was showing me only date and time and not milliseconds.

Thanks well in advance.

Does partitioning make sense for this application? (7 replies)

Paul Schmehl — Tue, 30 Oct 2012 18:15:46 +0000

I'm designing an applicaton for internal use. I'm a senior security analyst, not a DBA and not a programmer. Such is life at an edu. :-)

I've written the basic application in php (5.4.7) to use mysql (5.5.2) as the backend. The purpose of the application is to speed up searches for data stored in binary form. The average day is about 90GB of data. The database will contain the critical elements we search on. One day of data creates a 9GB table with over 120 million rows. The indexes are almost 2GB.

Here's the schema:
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| seq | int(10) unsigned | YES | | NULL | |
| stime | double(18,6) unsigned | NO | MUL | NULL | |
| saddr | varchar(64) | NO | MUL | NULL | |
| sport | varchar(10) | NO | | NULL | |
| daddr | varchar(64) | NO | MUL | NULL | |
| dport | varchar(10) | NO | | NULL | |
| pkts | bigint(20) | YES | | NULL | |
| bytes | bigint(20) | YES | | NULL | |
| state | varchar(32) | YES | | NULL | |
| proto | varchar(16) | NO | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
10 rows in set (0.00 sec)

stime, saddr and daddr are indexed, because those are the elements we normally search for.

Here's what the data looks like:

+-----------+-------------------+----------------+-------+---------------+-------+------+-------+-------+-------+
| seq | stime | saddr | sport | daddr | dport | pkts | bytes | state | proto |
+-----------+-------------------+----------------+-------+---------------+-------+------+-------+-------+-------+
| 105807958 | 1349121595.971430 | 10.174.37.162 | 63460 | 68.71.220.62 | 443 | 2 | 120 | A_A | tcp |
| 105807959 | 1349121595.971592 | 10.21.16.47 | 53600 | 64.208.241.65 | 80 | 75 | 68566 | PA_PA | tcp |
| 105807960 | 1349121595.971961 | 129.110.31.40 | 39605 | 217.70.185.0 | 53 | 1 | 87 | INT | udp |
| 105807961 | 1349121595.972484 | 74.125.225.134 | 80 | 10.21.1.146 | 61692 | 1 | 60 | _RA | tcp |
| 105807962 | 1349121595.973084 | 10.21.16.178 | 53433 | 74.125.225.99 | 80 | 5 | 2519 | PA_PA | tcp |
| 105807963 | 1349121595.974383 | 10.190.104.195 | 59254 | 66.94.240.25 | 80 | 2 | 120 | A_FA | tcp |
| 105807964 | 1349121595.974762 | 10.190.101.245 | 62995 | 75.102.13.140 | 80 | 89 | 48592 | A_PA | tcp |
| 105807965 | 1349121595.974790 | 10.110.143.140 | 54014 | 64.4.44.48 | 1863 | 3 | 182 | PA_PA | tcp |
| 105807966 | 1349121595.974874 | 10.21.21.50 | 15326 | 121.9.201.100 | 17788 | 2 | 241 | INT | udp |
| 105807967 | 1349121595.975586 | 10.170.40.197 | 49993 | 70.20.201.40 | 18879 | 8 | 668 | A_PA | tcp |
+-----------+-------------------+----------------+-------+---------------+-------+------+-------+-------+-------+
10 rows in set (0.08 sec)

The stime column is the unixtime date/time plus six digits representing the microseconds.

Eventually the data will be streamed in realtime into the db. So each day another 120 million rows will be added to the database.

Most of the queries I've written return a result in less than a second. Some can take up to 9 seconds. I would assume that adding a second day would double these query times.

So I'm looking in to partitioning. For my application, the most logical way to partition would be by the day. Generally, when we do searches, they are for discrete time intervals within a 24 hour period, so most searches would only traverse one partition.

Does this make sense?

Since the stime column is indexed, it would make sense to partition on that (if I'm reading the docs correctly). I believe those times are unique, so this could be made a primary key.

The problem is, I would need to either create hundreds of partitions in advance somehow or, more optimally, create a partition on the fly during the day for the next day's data.

Is it possible to do this? Can anyone give me some hints on how to do this?

EDIT: Unfortunately, stime has duplicate values, so it cannot be a primary key. Only seq is unique, but we don't search on that.

EDIT2: I should have mentioned that the inserts will be done by the same program that creates the log files. It has the capability of creating a new table each day. It also assigns the data type to each field but does not do any indexing. So I would have to write something to create the indexes later (which is not a big deal.)

My concern is that with uniquely named daily tables, my code has to be a lot more sophisticated. Searching over multiple days would require lots of joins, whereas a paritioned table would not.

I could use the program to create a new table each month and partition that, which would mean 12 tables per year with no more than 31 partitions in each table. I'm not sure how much data we will be keeping. Right now we keep about 25 days on a 1TB drive. That consumes about 950GB of data. The same time frame in the db would be 95GB.

Programatic Partitioning (no replies)

Jardel Jardel — Mon, 22 Oct 2012 10:44:10 +0000

I would like to know if somebody hear about programatic partitioning...

Some solution based in programatic partitioning like a function in application that change the partition.

Some peaple ask me about this and I never listened nothing about.

Thanks for wile

Jardel

Partitioning not working need primary key? (18 replies)

newbie Shai — Sat, 03 Nov 2012 17:37:07 +0000

Dear All,
We are trying some partitioning as below.

ALTER TABLE tblreceipt PARTITION BY RANGE (MONTH(receiptDate))
(
PARTITION p1 VALUES LESS THAN (TO_DAYS('2012-10-20')),
PARTITION p2 VALUES LESS THAN (TO_DAYS('2012-10-21')),
PARTITION p3 VALUES LESS THAN (TO_DAYS('2012-10-22'))
);

but end up with this Error Code: 1503
A PRIMARY KEY must include all columns in the table's partitioning function

Row movement for MySQL partitionned tables (1 reply)

jongse park — Thu, 11 Oct 2012 11:32:23 +0000

I am currently considering partitionning tables on my company's application and i was unable to find anywhere in the documentation anything about row movement.

How does MySQL partitionned tables handle updates on records that would move the record from one partition to another?

Exemple : table1 is a InnoDB table partitionned on the value of col1 which is an integer value.

The value of col1 for each record of table one will increase from 0 to 6 at different point of the time through UPDATE queries.
What happends when a record is updated to have its col1 value change from 0 to 1 ? Does it trigger an error? Does MySQL delete the record from p0 and inserts a new one into p1?

Thanks!

columns partitioning row inserted in wrong partition (1 reply)

Martino Merci — Wed, 10 Oct 2012 17:15:31 +0000

Hello,
Let t be the following table:
CREATE TABLE t(
`a` tinyint(3) unsigned NOT NULL,
`b` tinyint(3) unsigned NOT NULL
) ENGINE=MyISAM
PARTITION BY RANGE COLUMNS(a,b)
(PARTITION p1 VALUES LESS THAN (54,54) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (54,106) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (54,164) ENGINE = MyISAM,
PARTITION p4 VALUES LESS THAN (54,204) ENGINE = MyISAM,
PARTITION p5 VALUES LESS THAN (54,MAXVALUE) ENGINE = MyISAM,
PARTITION p6 VALUES LESS THAN (106,54) ENGINE = MyISAM,
PARTITION p7 VALUES LESS THAN (164,54) ENGINE = MyISAM,
PARTITION p8 VALUES LESS THAN (204,54) ENGINE = MyISAM,
PARTITION p9 VALUES LESS THAN (MAXVALUE,54) ENGINE = MyISAM);

the row (1,1) is correctly inserted in p1.
Why the row (1,100) is inserted in p1 and not in p2, as I expected?
Thanks,
Martino

Get partition name for specific date (3 replies)

Jan Bromberger — Fri, 12 Oct 2012 01:12:39 +0000

Hi,

for an InnoDB table historical_offers with a field
`valid_at` date NOT NULL DEFAULT '0000-00-00'
and the partitioning
PARTITION BY KEY(valid_at) PARTITIONS 92

I want to get the partition name for a specific date, so that I can truncate that partition, e.g.
ALTER TABLE historical_offers TRUNCATE PARTITION date_to_partition_name('2012-09-28')
where date_to_partition_name is what I am looking for.

I tried calculating modulo 92 the number of days since 0000-01-01 and 1970-01-01, but I got wrong numbers.

Thanks, Jan

Regarding optimizing hugh data with mysql (1 reply)

Santosh Bhabal — Wed, 10 Oct 2012 05:23:22 +0000

Hi All,

I have around more than 26 crores of data in my mysql table.

I am using myIsam engine.

I need to optimize this table for better performance.

I have 5 rows in my table.

Everyday there is insert, update, select happening on this table.

Someone has suggested me to use mysql partitioning.

Please advice me how to use/steps mysql partitioning

Regards
Santosh

Prunning partitions with range by date (2 replies)

Diogo Domanski — Thu, 27 Sep 2012 21:00:33 +0000

Hi all,

I created the following test table:

CREATE TABLE `test` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`ins_date` datetime NOT NULL,
PRIMARY KEY (`id`, `ins_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE ( TO_DAYS(`ins_date`) ) (
PARTITION pre_year VALUES LESS THAN (TO_DAYS('2012-01-01')),
PARTITION cur_year_jan VALUES LESS THAN (TO_DAYS('2012-02-01')),
PARTITION cur_year_feb VALUES LESS THAN (TO_DAYS('2012-03-01')),
PARTITION cur_year_mar VALUES LESS THAN (TO_DAYS('2012-04-01')),
PARTITION cur_year_may VALUES LESS THAN (TO_DAYS('2012-05-01')),
PARTITION cur_year_apr VALUES LESS THAN (TO_DAYS('2012-06-01')),
PARTITION cur_year_jun VALUES LESS THAN (TO_DAYS('2012-07-01')),
PARTITION cur_year_jul VALUES LESS THAN (TO_DAYS('2012-08-01')),
PARTITION cur_year_aug VALUES LESS THAN (TO_DAYS('2012-09-01')),
PARTITION cur_year_sep VALUES LESS THAN (TO_DAYS('2012-10-01')),
PARTITION cur_year_oct VALUES LESS THAN (TO_DAYS('2012-11-01')),
PARTITION cur_year_nov VALUES LESS THAN (TO_DAYS('2012-12-01')),
PARTITION cur_year_dec VALUES LESS THAN (TO_DAYS('2013-01-01')),
PARTITION nex_year VALUES LESS THAN (MAXVALUE)
);

The table has these partitions:
- one partition for all records prior to 2012-01-01
- 12 partions for year 2012 (one for each month)
- one partition for all records after 2102-12-31

The objective was to test prunning behaivor. To achieve this, I inserted some records into this table:

-- Records for 2011
insert into test set ins_date = '2011-01-01';
insert into test set ins_date = '2011-02-02';
insert into test set ins_date = '2011-03-03';
insert into test set ins_date = '2011-04-04';
insert into test set ins_date = '2011-05-05';
insert into test set ins_date = '2011-06-06';
insert into test set ins_date = '2011-07-07';
insert into test set ins_date = '2011-08-08';
insert into test set ins_date = '2011-09-09';
insert into test set ins_date = '2011-10-10';
insert into test set ins_date = '2011-11-11';
insert into test set ins_date = '2011-12-12';

-- Records for 2012
insert into test set ins_date = '2012-01-01';
insert into test set ins_date = '2012-02-02';
insert into test set ins_date = '2012-03-03';
insert into test set ins_date = '2012-04-04';
insert into test set ins_date = '2012-05-05';
insert into test set ins_date = '2012-06-06';
insert into test set ins_date = '2012-07-07';
insert into test set ins_date = '2012-08-08';
insert into test set ins_date = '2012-09-09';
insert into test set ins_date = '2012-10-10';
insert into test set ins_date = '2012-11-11';
insert into test set ins_date = '2012-12-12';

-- Records for 2013
insert into test set ins_date = '2013-01-01';
insert into test set ins_date = '2013-02-02';
insert into test set ins_date = '2013-03-03';
insert into test set ins_date = '2013-04-04';
insert into test set ins_date = '2013-05-05';
insert into test set ins_date = '2013-06-06';
insert into test set ins_date = '2013-07-07';
insert into test set ins_date = '2013-08-08';
insert into test set ins_date = '2013-09-09';
insert into test set ins_date = '2013-10-10';
insert into test set ins_date = '2013-11-11';
insert into test set ins_date = '2013-12-12';

So I executed this select statement

EXPLAIN PARTITIONS SELECT * FROM test WHERE ins_date >= '2012-09-01' AND ins_date <= '2012-09-30';

expecting that the list of prunned partitions would contain only "cur_year_sep", but the result was "pre_year,cur_year_sep".

I would like to know if is this correct or I might consider it a bug?

If I change the WHERE clause to "WHERE ins_date = '2012-09-09'" (using the "equals" operator) the explain statement shows me only "cur_year_sep". However, I need to make use of "greater than" and "lower than" operators.

Thanks in advance

Diogo

Partitioning help (3 replies)

Naresh Kumar Kondeboina — Tue, 02 Oct 2012 05:54:41 +0000

Hi,

I am new to MySql partitioning. I need some help on partitioning my tables.

My db is having 2 tables:

Table T1:
Schema: (C1 int auto_incr PK, C2 int not null,c3....)
Total Records: 2.5Million
Partitioning col: C2(Range partition with 6 partitions)

Table T2;
Schema: (D1 int auto_incr PK, C1 FK to T1, D2,D3,...)
TotalRecords: >6Million

Question here is, Can I make partitions for T2 based on C2 as they are more co related.

Partitioning by year/quarter (2 replies)

Ravi Malghan — Wed, 03 Oct 2012 03:15:38 +0000

Hi: I have a datetime column in my table and I want to partition by quarter of every year for the next 5 years. The table and partitions seems to have created fine. Not sure if this will work since quarter function returns an integer without the year. Can someone comment?

CREATE TABLE STATUS (
id NUMERIC(16) NOT NULL,
FIRSTOCCURRENCE DATETIME NOT NULL,
PRIMARY KEY ( ID,FIRSTOCCURRENCE )
)

PARTITION BY RANGE (QUARTER(FIRSTOCCURRENCE))
(
        PARTITION p2012_3 values less than (20123),
        PARTITION p2012_4 values less than (20124),
        PARTITION p2013_1 values less than (20131),
        PARTITION p2013_2 values less than (20132),
        PARTITION p2013_3 values less than (20133),
        PARTITION p2013_4 values less than (20134),
        PARTITION p2014_1 values less than (20141),
        PARTITION p2014_2 values less than (20142),
        PARTITION p2014_3 values less than (20143),
        PARTITION p2014_4 values less than (20144),
        PARTITION p2015_1 values less than (20151),
        PARTITION p2015_2 values less than (20152),
        PARTITION p2015_3 values less than (20153),
        PARTITION p2015_4 values less than (20154),
        PARTITION p2016_1 values less than (20161),
        PARTITION p2016_2 values less than (20162),
        PARTITION p2016_3 values less than (20163),
        PARTITION p2016_4 values less than (20164),
        PARTITION p2017_1 values less than (20171),
        PARTITION p2017_2 values less than (20172),
        PARTITION p2017_3 values less than (20173),
        PARTITION p2017_4 values less than (20174),
        PARTITION p2018_1 values less than (MAXVALUE)
);

What Method is Best (3 replies)

Larry Adams — Sat, 15 Sep 2012 05:11:23 +0000

Hi forum. I have a partitioned table that receives queries based upon a range between two different date fields. Those date fields are:

startTime TIMESTAMP
endTime TIMESTAMP

I would like to deploy RANGE type partitioning based upon endTime, that much is a no-brainer. However, when I perform a query as follows:

WHERE startTime BETWEEN 'A' AND 'B' OR endTime BETWEEN 'B' AND 'C'

I would like the partition to know about the startTime ranges in my various partitions, so that the pruning algorythim does not have to perform a full scan of all partitions.

I don't think the answer is sub partitions. Please let me know your thoughts.

TheWitness

timestamp, datetime as default value as current_timestamp [PARTITION] (14 replies)

Mannoj Kumar — Fri, 07 Sep 2012 11:49:42 +0000

I have a table column called 'recorddate' where it has timestamp datatype to default CURRENT_TIMESTAMP. Now I have few questions regarding maintenance part.

I should partition this table keeping recorddate as rangekey. But now it wouldn't allow partition key for timezone-dependent expressions in (sub)partitioning function.

If I change the timestamp datatype to datetime for recorddate the partition is created but it won't allow default CURRENT_TIMESTAMP database level.

2.1 . When I see forums stating MySQL 5.6.5 has datetime datatype can have CURRENT_TIMESTAMP, now(). Would it allow rangebykey for partition ? [Another thought that clings on my head is, would it be good to pick a distribution that is still under developement]

Any suggestions or work around, I really don't like the concept of triggers having to be on recordate upon insert if null. As it seeds records almost 5hundered thousands perday and peak time goes to 8 transactions per second on that table. That is the intent of falling on to partitions.

Thanks in advance!! Mannoj

particular field type not allowed to this type of partitioning (3 replies)

dhileepan mahendran — Fri, 24 Aug 2012 10:15:20 +0000

I need to create the table in the below format. But is returning the error in the partitioning area that is

Error Code: 1659.Field 'fldassigndate' is of a not allowed type for this type of partitioning. How to resolve this error and make partitioning ?

CREATE TABLE tblattendancesetup (
fldattendanceid int(11) NOT NULL AUTO_INCREMENT,
flddept varchar(100) DEFAULT NULL,
fldemployee varchar(100) DEFAULT NULL,
fldintime varchar(20) DEFAULT NULL,
fldouttime varchar(20) DEFAULT NULL,
fldlateafter varchar(20) DEFAULT NULL,
fldearlybefore varchar(20) DEFAULT NULL,
fldweekoff varchar(20) DEFAULT NULL,
fldshiftname varchar(20) DEFAULT NULL,
fldassigndate varchar(20) DEFAULT NULL,
fldfromdate varchar(20) DEFAULT NULL,
fldtodate varchar(20) DEFAULT NULL,
fldrefid varchar(20) DEFAULT NULL,
UNIQUE KEY fldattendanceid (fldattendanceid),
KEY in_attendancesetup (fldemployee,fldintime,fldouttime,fldlateafter,fldearlybefore,fldfromdate,fldtodate,fldattendanceid),
KEY i_emp_tmp (fldemployee),
KEY i_emp_attendance (fldemployee)
)
PARTITION BY RANGE (fldassigndate)
(PARTITION p_Apr VALUES LESS THAN (TO_DAYS('2012-05-01')),
PARTITION p_May VALUES LESS THAN (TO_DAYS('2012-06-01')),
PARTITION p_Nov VALUES LESS THAN MAXVALUE );