There are 5 types of table partitions in mysql: range, list, hash, key and composite (or subpartitioning), i think the most commonly used type in the database world (at least in oracle) could be range partition, in this way a expression is evaluated and according to it different partitions are created with ranges of rows. The most common method to partition by range is using dates (by years,months,days) to group large amounts of rows from a table, mysql does not support partition by date type directly (oracle does), instead a function like year() or month() should be used to get an integer value.


Playing with this partition type, first i tried to create a table with a primary key on the id column and the partition column using insert_date, but MySQL generates an error if the column insert_date is not part of the primary key:

mysql> CREATE TABLE range_partition_tab (
 ->  id numeric …


CREATE TABLE `test_unique` (
`id` int(10) unsigned NOT NULL auto_increment,
`col` varchar(5) default NULL,
`col2` varchar(50) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `col` (`col`)
);


SELECT * FROM test_unique;
+----+------+------+
| id | col | col2 |
+----+------+------+
| 1 | | blah |
| 2 | | blah |
| 3 | | blah |
| 4 | blah | NULL |
+----+------+------+

UPDATE test_unique SET col=\N WHERE col='';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0

SELECT * FROM test_unique;
+----+------+------+
| id | col | col2 |
+----+------+------+
| 1 | NULL | blah |
| 2 | NULL | blah |
| 3 | NULL | blah |
| 4 | blah | NULL |
+----+------+------+
4 rows in set (0.00 sec)

…

Less than an hour until my OSCON talk on how we moved from closed to open source with MySQL Cluster.  Will be fun to talk about this.  If you are at OSCON, feel welcome to come and listen.  Hopefully there will be a little time for questions too.  Room E143-144 at 11:35…  See you

 Btw, tomorrow I give a more technical talk about MySQL Replication.  You are most welcome then too.

Consider this:

You create a piece of code that is truly valuable for MySQL. We give you a T-shirt. Does this feel right?

Of course it doesn’t. The CLA (Contributor License Agreement) just contains the T-shirt as a default so-called consideration and a token of appreciation. Several people rightly reacted to the imbalance of T-shirts versus valuable code in my original CLA post.

The legal concept of “consideration” in American or English law means more-or-less compensation, i.e. a countercommitment to make the contract not just balanced but enforceable. Wikipedia’s definition of consideration starts like this:

Consideration is defined as a bargain for …

At the MySQL reception last night at OSCON, which was an astounding success (> 140 people I believe), I ran into one of the LiveJournal crew who told me about a success story they have had with one of the pluggable storage engines that is usually skimmed over and rarely discussed: the ARCHIVE storage engine. According to the LiveJournal employee (I left his business card back at the hotel so I can't remember his name...) they switched from using MyISAM to ARCHIVE for their Apache server logging and noticed a 400% performance improvement (from a disk I/O perspective) and at least a 20% reduction in storage size.

This little story highlights the main point I try to get across during my tutorials and presentations on performance tuning: that you should take advantage of the MySQL storage engine architecture by using the storage engine best suited for the job. In this case, using the …

Hi all -

As many of you know, we have a new backup/restore project underway. I’ve just posted the first draft of the functional spec on mysql forge, and would love to get as many eyes on it as possible. Please take a look when you can and shoot me any feedback at rschumacher@mysql.com

Thanks!

By tim

Yesterday, I talked about the overall market trend in computer books versus last year. As always, though, the opportunities and interesting tidbits are in the category visualizations we do. Here's a treemap view of the quarter on quarter differences between Q2 of 2006 and the same period last year:

As I've previously described in Book Sales as a Technology Trend Indicator, in a Treemap visualization, the size of a square indicates the relative size of the category, and its color indicates the rate of change. A category that is bright green is up significantly. One that is bright red is heading strongly in the other direction. Colors …

I just discover MySQL Contributor License Agreement. I don’t know who has invented this:

If we accept and maintain your contribution, and it is deemed of material value to the Project, your benefit is that we relieve you of the burden of maintaining your contribution and will provide you attribution in the GA release notes unless you ask not to be mentioned. You also may select two of the following items: a MySQL Press book, a MySQL shirt, a US $100 rebate to a conference or training class, or a US $100 donation to the Free Software Foundation (FSF) by MySQL AB.

My personal opinion is that this will work against MySQL. This is not motivation, but de-motivation. I will not contribute, under this conditions, do you?

More info here.

In the previous article I discussed using Read Replication Clustering to scale out reads for a website. What I will now do is describe a refined approach to the problem of scaling by creating "Application Clusters with Replication".

A common approach to website design is that a web designer creates a website and decides that search is a feature that they want to implement. If they use the MyISAM engine this means that they can add fulltext indexes to their tables and then make use of them in queries. I will ignore the case where the developer decides that an unanchored LIKE clause is an appropriate solution, since this developer will quickly hit a wall on performance and will need to learn what a fulltext index is.

So the developer adds a fulltext and is good to go? Sounds like an easy solution?

If the site the developer has written begins to see significant traffic then one of three things will occur. …

This article explains how I replaced file-based methods to ensure only one running instance of a program with MySQL’s GET_LOCK function. The result is mutual exclusivity that works in a distributed environment, and it’s dead simple to implement. My current employer used to use a technique similar to the classic Perl ‘Highlander’ method to ensure only one instance of a certain program would run at any given time. The method was to create a file with a certain name and then get an exclusive, non-blocking lock on the file with the flock() call.