WeberDev.com PHP and MySQL Code

LOG IN
BEGINNER GUIDESPHP CLASSESCODE SEARCHARTICLES SEARCHPHP FORUMSPHP MANUALPHP FUNCTIONS LISTWEB SITE TEMPLATES
Start typing to search for PHP and MySQL Code Snippets and Articles Search


Submit a code Example / Snippet Join us on FaceBook
Submit a code Example / Snippet Submit Your Code
Search Engine Optimization Monitor SEO Monitor
Web Site UpTime Monitor UpTime Monitor
Your Personal Examples List My Favorite Examples
Your Personal Articles List My Favorite Articles
Edit Account Info Update Your Profile
PHP Code Search
Web Development Forums
Learn MySQL Playing Trivia
PHPBB2 Templates
Web Development Resources
Web Development Content
PHPClasses
PHP Editor
PHP Jobs
Vision.To Design
Ajax Tutorials
PHP Programming Help
PHP/MySQL Programming
Webmaster Resources
Webmaster Forum
XML meta language
website builder



Go Back Add a Comment Send this Article to a friend Add this Article to your personal favoritest for easy future access to your favorite Code Examples and Articles. Submit a code example Print this code example.
BACK ADD A COMMENT SEND TO A FRIEND ADD TO MY FAVORITES SUBMIT AN ARTICLE PRINT
Title : A deep look at MySQL 5.5 partitioning enhancements
Categories : Databases, MySQL
MySQL.com
MySQL.com
Date : 2009-12-24
Grade : 1 of 5 (graded 1 times)
Viewed : 6825
Search : More Articles by MySQL.com
Action : Grade This Article
Tools : My Favotite Articles


Submit your own code examples 
 


The release of MySQL 5.5 has brought several enhancements. While most of the coverage went, understandably, to the semi-synchronous replication, the enhancements of partitioning were neglected, and sometimes there was some degree of misunderstanding on their true meaning. With this article, we want to explain these cool enhancements, especially the parts that were not fully understood.

The intuitive part: partition by non-integer columns
Anyone who has used partitions so far (see MySQL 5.1 partitions in practice) has experienced some frustration at the amount of problems to face when using non-integer columns. Partitions in 5.1 can only deal with integer values, meaning that if you want to do partitions on dates or strings, you had to convert these columns with a function.

The new additions work with RANGE and LIST partitioning. There is a new COLUMNS keyword that introduces the new functionality.

Let's assume a table like this one:


CREATE TABLE expenses (
expense_date DATE NOT NULL,
category VARCHAR(30),
amount DECIMAL (10,3)
);


If you want to partition by category in MySQL 5.1, you will have to convert categories into integers, with an additional lookup table. As of MySQL 5.5., you can simply do


ALTER TABLE expenses
PARTITION BY LIST COLUMNS (category)
(
PARTITION p01 VALUES IN ( 'lodging', 'food'),
PARTITION p02 VALUES IN ( 'flights', 'ground transportation'),
PARTITION p03 VALUES IN ( 'leisure', 'customer entertainment'),
PARTITION p04 VALUES IN ( 'communications'),
PARTITION p05 VALUES IN ( 'fees')
);




This statement, in addition to being clearly readable and to organizing the data into efficient chunks, has the beneficial side effect of ensuring that only the listed categories are accepted.

Another pain point in MySQL 5.1 is the handling of date columns. You can't use them directly, but you need to convert such columns using either YEAR or TO_DAYS, with situations like this one:


/* with MySQL 5.1*/
CREATE TABLE t2
(
dt DATE
)
PARTITION BY RANGE (TO_DAYS(dt))
(
PARTITION p01 VALUES LESS THAN (TO_DAYS('2007-01-01')),
PARTITION p02 VALUES LESS THAN (TO_DAYS('2008-01-01')),
PARTITION p03 VALUES LESS THAN (TO_DAYS('2009-01-01')),
PARTITION p04 VALUES LESS THAN (MAXVALUE));

SHOW CREATE TABLE t2 \G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`dt` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (TO_DAYS(dt))
(PARTITION p01 VALUES LESS THAN (733042) ENGINE = MyISAM,
PARTITION p02 VALUES LESS THAN (733407) ENGINE = MyISAM,
PARTITION p03 VALUES LESS THAN (733773) ENGINE = MyISAM,
PARTITION p04 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */


How dreadful. A real pain in the ... code. Of course, there were workarounds, but the trouble was quite a lot. Not to mention that it was really puzzling to define a partition using YEAR or TO_DAYS, and then having to query by bare column, as the queries by function did not kick the partition pruning.

Now it's a different story. Partitioning by date has become easy and immediate.


Read More









Beginners guide to PHP and MySQL
Categories : PHP, Beginner Guides, Databases, MySQL, Installation
Descriptions of Common Data Types
Categories : MySQL, Databases, PHP, PHP options/info, General
Access vs. MySQL
Categories : Databases, MySQL, MS Access
Creating an IE-Only Database Driven Menu System With PHP, MySQL and DHTML
Categories : PHP, MySQL, Databases, DHTML
Unicode and Other Funny Characters
Categories : Databases, MySQL, Unicode
Case Study: Handling MySQL Growth With a PHP Class
Categories : Databases, MySQL, PHP
MySQL Access Control System - Grant Tables
Categories : Databases, MySQL, Security
PHP, MySQL and Authentication 101
Categories : PHP, Databases, MySQL, Authentication
Building A Persistent Shopping Cart With PHP and MySQL
Categories : PHP, MySQL, Databases, Ecommerce
How Logs Work On MySQL With InnoDB Tables
Categories : Databases, MySQL, InnoDB
Practical Date and Time examples with PHP and MySQL
Categories : Databases, MySQL, PHP, Date/time
Managing Foreign Key Relationships In MySQL Using SQLyog
Categories : Databases, MySQL, SQLyog
Creating Users and Setting Permissions in MySQL
Categories : Databases, MySQL
Speaking SQL part 2
Categories : General SQL, Databases, MySQL
Start Using MySQL
Categories : MySQL, Databases, To MySQL, Beginner Guides