| List: | Commits | « Previous MessageNext Message » | |
| From: | jon | Date: | June 22 2006 2:45pm |
| Subject: | svn commit - mysqldoc@docsrva: r2470 - in trunk: refman-5.1 refman-common | ||
| View as plain text | |||
Author: jstephens Date: 2006-06-22 14:45:55 +0200 (Thu, 22 Jun 2006) New Revision: 2470 Log: Partitioning chapter - fixes for Docs Bug #20494 (PeterG) + reformat 5.1 changelog - misc fixes (some of these needed because somebody resaved using charset other than utf-8 ... No Soup For You!) Modified: trunk/refman-5.1/partitioning.xml trunk/refman-common/news-5.1.xml Modified: trunk/refman-5.1/partitioning.xml =================================================================== --- trunk/refman-5.1/partitioning.xml 2006-06-22 10:01:10 UTC (rev 2469) +++ trunk/refman-5.1/partitioning.xml 2006-06-22 12:45:55 UTC (rev 2470) @@ -27,13 +27,12 @@ concepts may be found in <xref linkend="partitioning-overview"/>. MySQL ¤t-series; supports several types of partitioning, which are discussed in <xref linkend="partitioning-types"/>, as well as - subpartitioning (also known sometimes as composite partitioning), - which is described in <xref linkend="partitioning-subpartitions"/>. - Methods of adding, removing, and altering partitions in existing - partitioned tables are covered in - <xref linkend="partitioning-management"/>. Table maintenance - commands for use with partitioned tables are discussed in - <xref linkend="partitioning-maintenance"/>. + subpartitioning, which is described in + <xref linkend="partitioning-subpartitions"/>. Methods of adding, + removing, and altering partitions in existing partitioned tables are + covered in <xref linkend="partitioning-management"/>. Table + maintenance commands for use with partitioned tables are discussed + in <xref linkend="partitioning-maintenance"/>. </para> <para> @@ -57,10 +56,11 @@ <para> The partitioning implementation in MySQL 5.1 is still undergoing - development. For known issues with MySQL partitioning, see - <xref linkend="partitioning-limitations"/>, where we have noted these. + development. For known issues with MySQL partitioning, see + <xref linkend="partitioning-limitations"/>, where we have noted + these. </para> - + <para> You may also find the following resources to be useful when working with partitioned tables. @@ -302,12 +302,6 @@ </para> <para> - 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. - </para> - - <para> <emphasis role="bold">Note</emphasis>: Partitioning applies to all data and indexes of a table; you cannot partition only the data and not the indexes, or <foreignphrase>vice versa</foreignphrase>, @@ -324,10 +318,7 @@ used to determine the maximum and minimum numbers of rows, respectively, that can be stored in each partition. See <xref linkend="partitioning-management"/>, for more information on - these options. <emphasis role="bold">Note</emphasis>: 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. + these options. </para> <indexterm> @@ -379,7 +370,7 @@ Other benefits usually associated with partitioning include those in the following list. 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. + priorities. </para> <remark role="todo"> @@ -517,7 +508,7 @@ -> <userinput>PARTITION mypart VALUES IN (1,3,5),</userinput> -> <userinput>PARTITION MyPart VALUES IN (2,4,6)</userinput> -> <userinput>);</userinput> -ERROR 1488 (HY000): All partitions must have unique names in the table +ERROR 1488 (HY000): Duplicate partition name mypart </programlisting> <para> @@ -689,15 +680,9 @@ <para> It is also possible to use an expression in <literal>VALUES LESS - THAN</literal> clauses. The most noteworthy restriction here is - that MySQL must be able to evaluate the expression's return - value as part of a <literal>LESS THAN</literal> - (<literal><</literal>) comparison; thus, the expression's - value cannot be <literal>NULL</literal>. It is for this reason - that the <literal>hired</literal>, <literal>separated</literal>, - <literal>job_code</literal>, and <literal>store_id</literal> - columns of the <literal>employees</literal> table have been - defined as <literal>NOT NULL</literal>. + THAN</literal> clauses. However, MySQL must be able to evaluate + the expression's return value as part of a <literal>LESS + THAN</literal> (<literal><</literal>) comparison. </para> <para> @@ -768,11 +753,6 @@ </listitem> <listitem> - <remark role="note"> - [js] Update this item when this optimisation has actually - been implemented. - </remark> - <para> You frequently run queries that depend directly on the column used for partitioning the table. For example, when @@ -781,10 +761,9 @@ store_id;</literal>, MySQL can quickly determine that only partition <literal>p2</literal> needs to be scanned because the remaining partitions cannot contain any records - satisfying the <literal>WHERE</literal> clause. - <emphasis role="bold">Note</emphasis>: This optimization has - not yet been enabled in the MySQL 5.1 sources; however, work - is in progress. + satisfying the <literal>WHERE</literal> clause. See + <xref linkend="partitioning-pruning"/>, for more information + about how this is accomplished. </para> </listitem> @@ -924,8 +903,8 @@ All rows relating to employees working at stores in that region can be deleted with the query <literal>ALTER TABLE employees DROP PARTITION pWest;</literal>, which can be executed much more - efficiently than the equivalent <literal>DELETE</literal> query - <literal>DELETE FROM employees WHERE store_id IN + efficiently than the equivalent <literal>DELETE</literal> + statement <literal>DELETE FROM employees WHERE store_id IN (4,12,13,14,18);</literal>. </para> @@ -1029,12 +1008,6 @@ <para> If you do not include a <literal>PARTITIONS</literal> clause, the number of partitions defaults to <literal>1</literal>. - <emphasis role="bold">Exception</emphasis>: For <literal>NDB - Cluster</literal> tables, the default number of partitions is - the same as the number of cluster data nodes, possibly modified - to take into account any <literal>MAX_ROWS</literal> setting in - order to ensure that all rows can fit into the partitions. (See - <xref linkend="ndbcluster"/>.) </para> <para> @@ -1140,10 +1113,10 @@ <para> 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. + more than one column value, but determining which of such + expressions are suitable can be quite difficult and + time-consuming. For this reason, the use of hashing expressions + involving multiple columns is not particularly recommended. </para> <para> @@ -1427,8 +1400,9 @@ Beginning with MySQL 5.1.6, <literal>KEY</literal> takes a list of zero or more column names. Where no column name is specified as the partitioning key, the table's primary key - is used. For example, the following <literal>CREATE - TABLE</literal> statement is valid in MySQL 5.1.6 or later: + is used, if there is one. For example, the following + <literal>CREATE TABLE</literal> statement is valid in MySQL + 5.1.6 or later: </para> <programlisting> @@ -1441,7 +1415,28 @@ </programlisting> <para> - In this case, the partitioning key is the + If there is no primary key but there is a unique key, then + the unique key is used for the partitioning key: + </para> + +<programlisting> +CREATE TABLE k1 ( + id INT NOT NULL, + name VARCHAR(20), + UNIQUE KEY (id) +) +PARTITION BY KEY() +PARTITIONS 2; +</programlisting> + + <para> + However, if the unique key column were not defined as + <literal>NOT NULL</literal>, then the previous statement + would fail. + </para> + + <para> + In both of these cases, the partitioning key is the <literal>id</literal> column, even though it is not shown in the output of <literal>SHOW CREATE TABLE</literal> or in the <literal>PARTITION_EXPRESSION</literal> column of the @@ -1522,11 +1517,11 @@ <para> <emphasis role="bold">Important</emphasis>: For a key-partitioned table using any MySQL storage engine other - than <literal>NDB CLuster</literal>, you cannot execute an + than <literal>NDB Cluster</literal>, you cannot execute an <literal>ALTER TABLE DROP PRIMARY KEY</literal>, as doing so generates the error <errortext>ERROR 1466 (HY000): Field in list of fields for partition function not found in - table</errortext>. This is not an issue for MySQL CLuster + table</errortext>. This is not an issue for MySQL Cluster tables which are partitioned by <literal>KEY</literal>; in such cases, the table is reorganized using the <quote>hidden</quote> primary key as the table's new @@ -2208,7 +2203,7 @@ </para> <para> - <literal>NULL</literal> is handled some what differently for + <literal>NULL</literal> is handled somewhat differently for tables partitioned by <literal>HASH</literal> or <literal>KEY</literal>. In these cases, any partition expression that yields a <literal>NULL</literal> value is treated as though @@ -2646,7 +2641,7 @@ <para> Dropping <literal>LIST</literal> partitions uses exactly the same <literal>ALTER TABLE ... DROP PARTITION</literal> syntax as - use for dropping <literal>RANGE</literal> partitions. However, + used for dropping <literal>RANGE</literal> partitions. However, there is one important difference in the effect this has on your use of the table afterward: You can no longer insert into the table any rows having any of the values that were included in @@ -2659,10 +2654,9 @@ table, use the <literal>ALTER TABLE ... ADD PARTITION</literal> statement. For tables which are partitioned by <literal>RANGE</literal>, this can be used to add a new range to - the beginning or end of the list of existing partitions. For - example, suppose that you have a partitioned table containing - membership data for your organisation, which is defined as - follows: + the end of the list of existing partitions. For example, suppose + that you have a partitioned table containing membership data for + your organisation, which is defined as follows: </para> <programlisting> @@ -3018,23 +3012,6 @@ PARTITION BY HASH( YEAR(dob) ) PARTITIONS 8; </programlisting> - - <remark role="todo"> - [js] Remove this note when the referenced command is - implemented. - </remark> - - <para> - <emphasis role="bold">Note</emphasis>: In MySQL 5.1 - ¤t-version;, <literal>ALTER TABLE ... PARTITION BY - ...</literal> is not yet implemented. Instead, you must - either drop and re-create the table using the desired - partitioning, or — if you need to retain data already - stored in the table — you can use <literal>CREATE - TABLE ... SELECT ...</literal> to create the new table and - copy the data from the old one, and then drop the old table, - renaming the new one as a final step if desired. - </para> </listitem> </itemizedlist> @@ -3130,12 +3107,12 @@ Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 </programlisting> - + <para> Note that the number following <literal>COALESCE - PARTITION</literal> is the number of partitions to merge into + PARTITION</literal> is the number of partitions to merge into the remainder — in other words, it is the number of - partitions to remove from the table. + partitions to remove from the table. </para> <para> @@ -3155,16 +3132,9 @@ </para> <programlisting> -ALTER TABLE clients ADD PARTITION PARTITIONS 18; +ALTER TABLE clients ADD PARTITION PARTITIONS 6; </programlisting> - <para> - <emphasis role="bold">Note</emphasis>: <literal>ALTER TABLE ... - REORGANIZE PARTITION</literal> cannot be used with tables that - are partitioned by <literal>HASH</literal> or - <literal>KEY</literal>. - </para> - </section> <section id="partitioning-maintenance"> @@ -3684,14 +3654,14 @@ will be in either of the partitions <literal>p0</literal> or <literal>p3</literal>; that is, we need to search only in partitions <literal>p1</literal> and <literal>p2</literal> to find - matching rows. 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. This <quote>cutting away</quote> of unneeded - partitions is known as <firstterm>pruning</firstterm>. When the - optimiser can make use of partition pruning in performing a query, - execution of the query can be an order of magnitude faster than - the same query against a non-partitioned table containing the same - column definitions and data. + matching rows. By doing so, it is possible to expend much more + time and effort in finding matching rows than it is to scan all + partitions in the table. This <quote>cutting away</quote> of + unneeded partitions is known as <firstterm>pruning</firstterm>. + When the optimiser can make use of partition pruning in performing + a query, execution of the query can be an order of magnitude + faster than the same query against a non-partitioned table + containing the same column definitions and data. </para> <para> @@ -3900,7 +3870,7 @@ This section discusses current restrictions and limitations on MySQL partitioning support, as listed here: </para> - + <indexterm> <primary>partitioning limitations</primary> </indexterm> @@ -3948,7 +3918,6 @@ </listitem> <listitem> - <indexterm> <primary>partitioning</primary> <secondary>storage engines (limitations)</secondary> @@ -3997,12 +3966,10 @@ <para> The one exception to this restriction occurs when partitioning by <literal>KEY</literal>, where it is possible to use columns - of the <literal>CHAR</literal>, <literal>VARCHAR</literal>, - <literal>FLOAT</literal>, and <literal>DECIMAL</literal> - column types as partitioning keys, because MySQL's internal - key-hashing function produces the correct datatype from these. - For example, the following <literal>CREATE TABLE</literal> - statement is valid: + of other types types as partitioning keys, because MySQL's + internal key-hashing function produces the correct datatype + from these. For example, the following <literal>CREATE + TABLE</literal> statement is valid: </para> <programlisting> Modified: trunk/refman-common/news-5.1.xml =================================================================== --- trunk/refman-common/news-5.1.xml 2006-06-22 10:01:10 UTC (rev 2469) +++ trunk/refman-common/news-5.1.xml 2006-06-22 12:45:55 UTC (rev 2470) @@ -1250,7 +1250,7 @@ <para> <emphasis remap="bold">Note</emphasis>: As a consequence of this fix, <literal>SHOW CREATE TABLE</literal> no longer - displays auto-partitioning informaiton for + displays auto-partitioning information for <literal>NDBCluster</literal> tables. </para> </listitem> @@ -2554,8 +2554,8 @@ It was possible to create a <literal>RANGE</literal>-partitioned table with a partition defined using the clause <literal>VALUES LESS THAN - (NULL)</literal>, even though such a partition could never any - values whatsoever. (Bug #18752) + (NULL)</literal>, even though such a partition could never + contain any values whatsoever. (Bug #18752) </para> </listitem> @@ -4349,11 +4349,12 @@ <listitem> <para> - Naming a partition using the character - <emphasis role="bold">Ã</emphasis> - (<quote>c-cedilla</quote>; Unicode <literal>00C7</literal> or - <literal>00E7</literal>) made unreadable the table containing - the partition. (Bug #14527) + Naming a partition using the characters + <emphasis role="bold">Ç</emphasis> or + <emphasis role="bold">ç</emphasis> (<quote>c-cedilla</quote>; + Unicode <literal>00C7</literal> or <literal>00E7</literal>) + made unreadable the table containing the partition. (Bug + #14527) </para> </listitem> @@ -5730,7 +5731,7 @@ <listitem> <para> - Error message for specifying value for which no partition + The error message for specifying values for which no partition exists returned wrong values on certain platforms. (Bug #15910) </para> @@ -6574,7 +6575,7 @@ user point of view, the partitioned table is still a single table. See <xref linkend="partitioning"/>, for further information on this functionality. (Author: Mikael - Ronström) + Ronström) </para> </listitem> @@ -6849,11 +6850,11 @@ <listitem> <para> - When two threads compete for the same table, a deadlock could - occur if one thread has also a lock on another table through - <literal>LOCK TABLES</literal> and the thread is attempting to - remove the table in some manner and the other thread want - locks on both tables. (Bug #10600) + When two threads competed for the same table, a deadlock could + occur if one thread also had a lock on another table through + <literal>LOCK TABLES</literal> and the thread was attempting to + remove the table in some manner while the other thread tried + to place locks on both tables. (Bug #10600) </para> </listitem>
| Thread | ||
|---|---|---|
| • svn commit - mysqldoc@docsrva: r2470 - in trunk: refman-5.1 refman-common | jon | 22 Jun |
© 1995-2008 MySQL AB. All rights reserved.
Page generated in 0.742 sec. using MySQL 5.1.14-beta-log