Key Difference between MySQL 5.0 vs MySQL 5.1
There are two main difference are as follows:
1. Supports for Pluggable Storage Engine
2. Supports for Table Partition.
Pluggable Storage Engine:
storage engine can be processed dynamically by MySQL server 5.1.
storage engines to be loaded by INSTALL PLUGIN statement.
storage engines to be unloaded by UNINSTALL PLUGIN statement.
we can partition table when creating table. If needed we can also create sub partition too.
1.Horizontal Partitioning : Row based partition, ex: storing group of rows into one partition storing users info based on their age range,
because users search in matrimony based on age.
2.Vertical Partitioning : Column based partition ex: storing group of column into one partition usually columns hold huge data size as BLOB and TEXT Data Types
This partition can be done in 4 ways.
How Partition works using Range :
When creating table we have to mention the range of values to be stored in corresponding partition using the keyword less than(value) .
CREATE TABLE `users` ( `user_id` int(11) unsigned NOT NULL auto_increment, `user_email` varbinary(255) NOT NULL, `user_password` varchar(255) NOT NULL, PRIMARY KEY (`user_id`) ) PARTITION BY RANGE(user_id) ( PARTITION p0 VALUES LESS THAN(5), PARTITION p1 VALUES LESS THAN(4294967295) ); EXPLAIN partitions select * from users where user_id < 4; EXPLAIN partitions select * from users where user_id > 4;
using the explain partition ,we can easily know record stored in which partition.