Description:
Per email from PeterG:
Hi,
The partitioning chapter has a few obsolete details and typos.
"
It is possible to create partitioned temporary tables; however, the
lifetime of such tables is only as long as the current MySQL session.
This is the same as for non-partitioned temporary tables.
"
No. You get an error if you try to create a temporary
partitioned table. See:
http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html
("As of MySQL 5.1.8, temporary tables cannot be partitioned.")
"
This particular feature is currently nonfunctional due to Bug#13250; we
should have this fixed by the time the first 5.1 binaries are made
available.
"
Bug#13250 has nothing to do with partitions. And 5.1 binaries
have available for some time, unless I've misunderstood what
binaries are.
"
These features are not currently implemented in MySQL Partitioning, but
are high on our list of priorities; we hope to include them in the 5.1
production release.
"
Abandon hope.
"
For example, the following CREATE TABLE statement fails as shown: ...
ERROR 1488 (HY000): All partitions must have unique names in the table
"
The error message has changed. Now it looks like:
ERROR 1504 (HY000): Duplicate partition name mypart
"
It is for this reason that the hired, separated, job_code, and store_id
columns of the employees table have been defined as NOT NULL.
"
Ah, but now they can be NULL. So "LESS THAN (NULL)" is still disallowed,
but the columns of employee didn't need to be defined as NOT NULL.
"
MySQL can quickly determine that only partition p2 needs to be scanned
because the remaining partitions cannot contain any records satisfying
the WHERE clause.
Note: This optimization has not yet been enabled in the MySQL 5.1
sources; however, work is in progress.
"
I don't know of a work that's "in progress" now.
"
All rows relating to employees working at stores in that region can be
deleted with the query ALTER TABLE employees DROP PARTITION pWest;,
which can be executed much more efficiently than the equivalent DELETE
query DELETE FROM employees WHERE store_id IN (4,12,13,14,18);
"
FYI (there's no problem here, I merely pretend it's interesting) ...
I tested the above statements with a 100,000-row table.
DROP takes 0.04 seconds, DELETE takes 7.63 seconds.
Not a surprise to anyone, of course.
"
Exception: For NDB Cluster tables, the default number of partitions is
the same as the number of cluster data nodes ...
"
This is on the page about HASH partitioning.
There won't be any HASH partitioning with NDB Cluster.
Log of my IRC chat with Mikael today:
pgulutzan mikael: good morning. i'm looking at calvin's recent statement
"NDB will support partitioning by key, but not other forms (by range,
by list)". i need a clarification -- what about hash?
and what about linear key?
mikael pgulutzan: key and linear key AFAIK, hash would be nice I think
but to my knowledge not supported
"
You may use any function or other expression for expr that is valid in
MySQL, so long as it returns a non-constant, non-random integer value.
"
That's not true any more. Only certain functions are legal.
"
Changing the value of int_col by a given amount can produce by widely
different changes in the value of the expression.
"
Remove "by"?
"
In theory, pruning is also possible for expressions involving more than
column value, but determining which of these are suitable can be quite
difficult and time-consuming. For this reason, the use of hashing
expressions involving multiple columns is not particularly recommended.
"
Since I see "multiple columns" in the second sentence, I suppose
that "one column value" was supposed to be in the first sentence.
"
Where no column name is specified as the partitioning key, the table's
primary key is used.
"
It seems that I can use a UNIQUE key too, a new discovery.
"
This is not an issue for MySQL CLuster tables ...
"
Just check the spelling of CLuster.
"
Subpartitioning — also known as composite partitioning ...
"
I don't think that's true. I thought that composite partitioning
is the combination of partitioning and subpartitioning.
"NULL is handled some what differently for tables partitioned ..."
Should be "somewhat"?
"
Note that the number of rows dropped from the table as a result of ALTER
TABLE ... DROP PARTITION is not reported by the server as it would be by
the equivalent DELETE query.
"
There is no such thing as a DELETE query.
There are only DELETE statements.
"
Dropping LIST partitions uses exactly the same ALTER TABLE ... DROP
PARTITION syntax as use for dropping RANGE partitions.
"
Should be "used"?
"
To add a new range or list partition to a previously partitioned table,
use the ALTER TABLE ... ADD PARTITION statement. For tables which are
partitioned by RANGE, this can be used to add a new range to the
beginning or end of the list of existing partitions.
"
To the beginning? Is there an example of that?
"
Note: In MySQL 5.1 5.1.12-beta, ALTER TABLE ... PARTITION BY ... is not
yet implemented.
"
It seems to be implemented.
"
To increase the number of partitions for the clients table from 12 to
18. use ALTER TABLE ... ADD PARTITION as shown here:
ALTER TABLE clients ADD PARTITION PARTITIONS 18;
"
No. If there are 12 partitions and you say ADD ... PARTITIONS 18,
then there will be 30 partitions.
This COALESCE instruction is proving to be hard to understand.
"
Note: ALTER TABLE ... REORGANIZE PARTITION cannot be used with
tables that are partitioned by HASH or KEY.
"
Hmm. There must be something wrong with this error message then:
"ERROR 1498 (HY000): REORGANISE PARTITION without parameters can
only be used on auto-partitioned tables using HASH PARTITIONs"
"
By doing so, it is possible to expend much time and effort in finding
matching rows than it is to scan all partitions in the table.
"
Maybe "much" should be "more"? I don't know what the intent was.
"
Partitioning by KEY is the only type of partitioning supported for the
NDBCluster storage engine.
"
Well, as you can see from the IRC conversation that I quoted above,
Mikael isn't 100% sure yet about HASH. (I don't test with NDBCluster.)
And I suppose that when the manual says KEY that includes LINEAR KEY?
"
The one exception to this restriction occurs when partitioning by KEY,
where it is possible to use columns of the CHAR, VARCHAR, FLOAT, and
DECIMAL column types as partitioning keys, because MySQL's internal
key-hashing function produces the correct datatype from these.
"
The standard term is "data type".
I know that there are other data types besides CHAR, VARCHAR, FLOAT
and DECIMAL that seem to work fine. The list here is too small.
-- Peter Gulutzan, Senior Software Architect MySQL AB, www.mysql.com Office: +1 780 472-6838 Mobile: +1 780 904-0297 VoIP: +1 408 213-6654
How to repeat:
See Description
Suggested fix:
Update partitioning info as suggested.
