MySQL :: MySQL Partitioning

<!DOCTYPE html> <html lang="en"> <head><script type="text/javascript" src="/_static/js/bundle-playback.js?v=HxkREWBo" charset="utf-8"></script> <script type="text/javascript" src="/_static/js/wombat.js?v=txqj7nKC" charset="utf-8"></script> <script>window.RufflePlayer=window.RufflePlayer||{};window.RufflePlayer.config={"autoplay":"on","unmuteOverlay":"hidden"};</script> <script type="text/javascript" src="/_static/js/ruffle/ruffle.js"></script> <script type="text/javascript"> __wm.init("https://web.archive.org/web"); __wm.wombat("http://www.mysql.com:80/products/enterprise/partitioning.html","20130114052213","https://web.archive.org/","web","/_static/", "1358140933"); </script> <link rel="stylesheet" type="text/css" href="/_static/css/banner-styles.css?v=S1zqJCYt" /> <link rel="stylesheet" type="text/css" href="/_static/css/iconochive.css?v=3PDvdIFv" />  <meta charset="utf-8"/>  <title>MySQL :: MySQL Partitioning</title> <link rel="stylesheet" media="screen" href="/web/20130114052213cs_/http://www.mysql.com/common/css/mysql.css?v=20121230"/> <link rel="stylesheet" media="projection" href="/web/20130114052213cs_/http://www.mysql.com/common/css/mysql.css?v=20121230"/> <link rel="stylesheet" media="print" href="/web/20130114052213cs_/http://www.mysql.com/common/css/print.css?v=20111230"/> <link rel="shortcut icon" href="/web/20130114052213im_/http://www.mysql.com/common/themes/sakila/favicon.ico"/> <script src="/web/20130114052213js_/http://www.mysql.com/common/js/clear_search_text.js"></script> <script type="text/javascript">var addthis_config = {"data_track_addressbar":false};</script>  <script type="text/javascript" src="//web.archive.org/web/20130114052213js_/http://s7.addthis.com/js/250/addthis_widget.js#pubid=ra-4fc7ca0b3e7d0a70"></script> </head> <body class="www"> <div id="container">  <a class="skipToContent" href="#mainContent">Skip navigation links</a> <div id="header"> <div class="left"> <div id="logo"> <a href="https://web.archive.org/web/20130114052213/http://www.mysql.com/" title="MySQL"> <img src="/web/20130114052213im_/http://www.mysql.com/common/logos/logo-mysql-110x57.png" alt="MySQL" width="110" height="57"/></a> </div> <div id="tagline">The world's most popular open source database</div> </div> <div class="right"> <div id="contact_rep"><a href="https://web.archive.org/web/20130114052213/http://www.mysql.com/about/contact/">Contact a MySQL Representative</a></div> <div id="search_box">  <form id="searchform" name="searchform" method="get" action="https://web.archive.org/web/20130114052213/http://search.oracle.com/search/search"> <input type="text" id="q" name="q" value="Search" class="swap_value" onfocus="clearSearchText();"/> <input type="hidden" name="group" value="MySQL"/> <input type="image" src="/web/20130114052213im_/http://www.mysql.com/common/themes/sakila/search_g.png" id="go" alt="Search" title="Search"/> </form> </div>  <div id="login"> <p><a href="https://web.archive.org/web/20130114052213/https://www.mysql.com/auth/login/?dest=http%3a%2f%2fwww.mysql.com%2Fproducts%2Fenterprise%2Fpartitioning.html">Login</a> | <a href="https://web.archive.org/web/20130114052213/https://www.mysql.com/auth/register/">Register</a></p> </div> </div> </div>  <div id="nav_container"> <div id="flags"> <ul> <li style="position:relative; top:-5px;">  <a href="https://web.archive.org/web/20130114052213/http://www.facebook.com/mysql"><img src="/web/20130114052213im_/http://www.mysql.com/common/icons/facebook.png" alt="Facebook" title="Join us on Facebook" width="20" height="20"/></a>  <a href="https://web.archive.org/web/20130114052213/https://twitter.com/#!/mysql"><img src="/web/20130114052213im_/http://www.mysql.com/common/icons/twitter.png" alt="Twitter" title="Follow us on Twitter" width="20" height="20"/></a>  <a href="https://web.archive.org/web/20130114052213/http://www.youtube.com/mysqlchannel"><img src="/web/20130114052213im_/http://www.mysql.com/common/icons/youtube.png" alt="YouTube" title="Visit our YouTube channel" width="20" height="20"/></a>       </li> <li><a href="https://web.archive.org/web/20130114052213/http://www.mysql.com/products/enterprise/partitioning.html" title="MySQL.com in English"><img src="/web/20130114052213im_/http://www.mysql.com/common/themes/sakila/flag_en.png" alt="MySQL.com in English" width="15" height="24"/></a></li><li><a href="https://web.archive.org/web/20130114052213/http://www.mysql.fr/products/enterprise/partitioning.html" title="MySQL.com en Fran莽ais"><img src="/web/20130114052213im_/http://www.mysql.com/common/themes/sakila/flag_fr.png" alt="MySQL.com en Fran莽ais" width="15" height="24"/></a></li><li><a href="https://web.archive.org/web/20130114052213/http://www.mysql.de/products/enterprise/partitioning.html" title="MySQL.com auf Deutsch"><img src="/web/20130114052213im_/http://www.mysql.com/common/themes/sakila/flag_de.png" alt="MySQL.com auf Deutsch" width="15" height="24"/></a></li><li><a href="https://web.archive.org/web/20130114052213/http://www.mysql.it/products/enterprise/partitioning.html" title="Italiana"><img src="/web/20130114052213im_/http://www.mysql.com/common/themes/sakila/flag_it.png" alt="Italiana" width="15" height="24"/></a></li><li><a href="https://web.archive.org/web/20130114052213/http://www-jp.mysql.com/products/enterprise/partitioning.html" title="鏃ユ湰"><img src="/web/20130114052213im_/http://www.mysql.com/common/themes/sakila/flag_jp.png" alt="鏃ユ湰" width="15" height="24"/></a></li> </ul> </div> <div id="tab_first"> <ul> <li class="current"><a href="https://web.archive.org/web/20130114052213/http://www.mysql.com/">MySQL.com</a></li> <li class="first"><a href="https://web.archive.org/web/20130114052213/http://www.mysql.com/downloads/">Downloads (GA)</a></li> </ul> </div> <div id="mysql_menu"> <ul> <li class="current"><a class="current " href="/web/20130114052213/http://www.mysql.com/products/">Products</a> </li> <li class="link"><a href="/web/20130114052213/http://www.mysql.com/services/">Services</a> </li> <li class="link"><a href="/web/20130114052213/http://www.mysql.com/partners/">Partners</a> </li> <li class="link"><a href="/web/20130114052213/http://www.mysql.com/customers/">Customers</a> </li> <li class="link"><a href="/web/20130114052213/http://www.mysql.com/why-mysql/">Why MySQL?</a> </li> <li class="link"><a href="/web/20130114052213/http://www.mysql.com/news-and-events/on-demand-webinars/">News & Events</a> </li> <li class="link last"><a href="/web/20130114052213/http://www.mysql.com/buy-mysql/">How to Buy</a> </li> </ul> </div> <div id="og_sakila"> </div> <div id="og_title"> </div> </div>   <div class="page_container"> <div class="page_sidebar">  <div id="menu_title">   </div> <div id="menu"> <ul> <li class="link"><a href="/web/20130114052213/http://www.mysql.com/products/">MySQL Editions</a> </li> <li class="current"><a class="current subitems" href="/web/20130114052213/http://www.mysql.com/products/enterprise/">MySQL Enterprise Edition</a> <ul class="subitems1"> <li class="link"><a href="/web/20130114052213/http://www.mysql.com/products/enterprise/mysql-datasheet.en.pdf">Datasheet (PDF)</a> </li> <li class="link"><a href="/web/20130114052213/http://www.mysql.com/products/enterprise/techspec.html">Technical Specification</a> </li> <li class="link"><a href="/web/20130114052213/http://www.mysql.com/products/enterprise/database/">MySQL Database</a> </li> <li class="link"><a href="/web/20130114052213/http://www.mysql.com/products/enterprise/monitor.html">Enterprise Monitor</a> </li> <li class="link"><a href="/web/20130114052213/http://www.mysql.com/products/enterprise/backup.html">Enterprise Backup</a> </li> <li class="link"><a href="/web/20130114052213/http://www.mysql.com/products/enterprise/high_availability.html">MySQL Enterprise HA</a> </li> <li class="link"><a href="/web/20130114052213/http://www.mysql.com/products/enterprise/scalability.html">Enterprise Scalability</a> </li> <li class="link"><a href="/web/20130114052213/http://www.mysql.com/products/enterprise/security.html">Enterprise Security</a> </li> <li class="link"><a href="/web/20130114052213/http://www.mysql.com/products/enterprise/audit.html">Enterprise Audit</a> </li> <li class="link"><a href="/web/20130114052213/http://www.mysql.com/products/enterprise/replication.html">Replication</a> </li> <li class="current"><a class="current " href="/web/20130114052213/http://www.mysql.com/products/enterprise/partitioning.html">Partitioning</a> </li> <li class="link"><a href="/web/20130114052213/http://www.mysql.com/products/workbench/">Workbench</a> </li> <li class="link"><a href="/web/20130114052213/http://www.mysql.com/products/connector/">Connectors</a> </li> <li class="link"><a href="/web/20130114052213/http://www.mysql.com/support/">Support</a> </li> <li class="link"><a href="/web/20130114052213/http://www.mysql.com/training/">Training</a> </li> <li class="link"><a href="/web/20130114052213/http://www.mysql.com/consulting/">Consulting</a> </li> <li class="link"><a href="/web/20130114052213/http://www.mysql.com/products/enterprise/testimonials/">Customer Successes</a> </li> <li class="link last"><a href="/web/20130114052213/http://www.mysql.com/products/enterprise/resources.html">Resources</a> </li> </ul> </li> <li class="link"><a href="/web/20130114052213/http://www.mysql.com/products/standard/">MySQL Standard Edition</a> </li> <li class="link"><a href="/web/20130114052213/http://www.mysql.com/products/classic/">MySQL Classic Edition</a> </li> <li class="link"><a href="/web/20130114052213/http://www.mysql.com/products/cluster/">MySQL Cluster CGE</a> </li> <li class="link"><a href="/web/20130114052213/http://www.mysql.com/oem/">MySQL Embedded (OEM/ISV)</a> </li> <li class="link"><a href="/web/20130114052213/http://www.mysql.com/products/community/">MySQL Community Edition</a> </li> </ul> </div> <br class="clear"/> <div class="promo11"> <h2 style="color: #e87c00; margin: 0px 0px 8px 0px">Contact Sales</h2> <p style="font-size: 90%;"> USA/Canada - Toll Free: <span class="nobr">+1-866-221-0634</span><br/> USA - From abroad: <span class="nobr">+1-208-338-8100</span><br/> USA/Canada - Subscription Renewals: <span class="nobr">+1-866-221-0634</span><br/> <br/> Latin America: +1 512 535 7751<br/> Brazil: +55 11 5189-1097<br/> South Africa: <span class="nobr">+27 113194408</span><br/> UK: <span class="nobr">+44 207 553 8447</span><br/> Ireland: <span class="nobr">+353 1 8031050</span><br/> Germany: <span class="nobr">+49 89 143 01280</span><br/> France: <span class="nobr">+33 1 57 60 83 57</span><br/> Benelux: <span class="nobr">+31 30 662 7960</span><br/> Italy: <span class="nobr">+39 02 249 59 120</span><br/> Israel: <span class="nobr">+353 1 8031123</span><br/> Spain & Portugal: <span class="nobr">+34 916312182</span><br/> Sweden: <span class="nobr">+46 84773853</span><br/> Other EMEA countries: <span class="nobr">+353 1 8031050</span><br/> <a href="/web/20130114052213/http://www.mysql.com/about/contact/apac.html">Asia Pacific: Toll Free</a> </p> <p><strong><a class="moreinfo" href="https://web.archive.org/web/20130114052213/http://www.mysql.com/about/contact/">Contact Us Online »</a></strong></p> </div> <div><a href="/web/20130114052213/http://www.mysql.com/trial/"><img src="/web/20130114052213im_/http://www.mysql.com/common/themes/sakila/banners/h12-enterprise-trial.en.png" width="230" height="100" alt="MySQL Enterprise Edition Trial - Try Now"/></a></div> <div></div>  </div>  <div id="page" class="sidebar"> <h1 class="page_header" id="mainContent">MySQL Partitioning</h1> <div class="boxStyled" style="width: 30%;"> <div class="content"> <h2>MySQL Enterprise Edition</h2> <ul> <li><a href="/web/20130114052213/http://www.mysql.com/products/enterprise/backup.html">MySQL Enterprise Backup</a></li> <li><a href="/web/20130114052213/http://www.mysql.com/products/enterprise/monitor.html">MySQL Enterprise Monitor</a></li> <li><a href="/web/20130114052213/http://www.mysql.com/products/enterprise/high_availability.html">MySQL Enterprise HA</a></li> <li><a href="/web/20130114052213/http://www.mysql.com/products/enterprise/scalability.html">MySQL Enterprise Scalability</a></li> <li><a href="/web/20130114052213/http://www.mysql.com/products/enterprise/security.html">MySQL Enterprise Security</a></li> <li><a href="/web/20130114052213/http://www.mysql.com/products/enterprise/audit.html">MySQL Enterprise Audit</a></li> <li><a href="/web/20130114052213/http://www.mysql.com/products/enterprise/whitepapers.html">White Papers</a></li> <li><a href="/web/20130114052213/http://www.mysql.com/about/contact/">Contact MySQL Sales</a></li> <li><a href="https://web.archive.org/web/20130114052213/https://shop.oracle.com/pls/ostore/product?p1=MySQL">Buy Now</a></li> <li><a href="/web/20130114052213/http://www.mysql.com/trials/">Try Now</a></li> <li><a href="https://web.archive.org/web/20130114052213/http://www.youtube.com/watch?v=LESI4m0dMNE">Demo</a></li> </ul> </div> </div> <p>MySQL Partitioning enables developers and DBAs to improve database performance and simplify the management of very large databases. MySQL supports horizontal partitioning, allowing the rows of a database to be divided into smaller data sets and then distributed across multiple directories and disks.</p> <p>Partitioning delivers increased query performance, as smaller sets of data only need to be accessed for specific operations, rather than one large single table. It is also possible to stripe a partitioned table across different physical drives allowing physical I/O contention to be reduced when multiple partitions are accessed simultaneously.</p> <p>Partitioning also simplifies data management. For example, a DBA can drop specific partitions in a partitioned table while the remaining partitions remain intact (as opposed to crafting a fragmentation-producing mass delete operation for the whole table). with no DBA intervention being necessary.</p> <p>Multiple partitioning methods, enable DBAs to precisely control how the data is partitioned:</p> <h2>Range Partitioning</h2> <p>Each partition contains rows mapping to a specific set (range) of values. Range partitioning is useful when frequently running queries that depend directly on the column used for partitioning the table, or for quickly deleting old data by simply dropping a table. </p> <h2>List Partitioning</h2> <p>List partitioning is similar to range partitioning. The main difference is that this partitioning mode allows data to be segmented based on a pre-defined list of values that are specified by the DBA, rather than a set of contiguous ranges of values.</p> <h2>Columns Partitioning</h2> <p>Columns partitioning enables the use of multiple columns in partitioning keys. The multiple columns are used for both the placing of rows in partitions and for determining which partitions are checked for matching rows in partition pruning operations. Column partitioning is an extension of Range and List partitioning.</p> <h2>Hash Partitioning</h2> <p>Partitioning by Hash is used primarily to ensure an even distribution of data among a pre-determined number of partitions. With range or list partitioning, you must specify explicitly into which partition a given column value is to be stored. With hash partitioning, MySQL takes care of this for you, and you need only specify a column value or expression based on a column value to be hashed and the number of partitions into which the partitioned table is to be divided.</p> <p>A variant of Hash partitioning is Linear Hash, which uses a more complex algorithm to partition data which can make adding, dropping, merging, and splitting of partitions much faster when dealing with multi-terabyte tables.</p> <h2>Key Partitioning</h2> <p>Partitioning by key is similar to partitioning by hash, where MySQL guarantees even distribution of data through a system-generated hash key rather than user-defined expressions used by hash partitioning. Key Partitioning allows a great range of column data types to be used for partitioning.</p> <h2>Sub-Partitioning</h2> <p>Sub-Partitioning enables the further division of each partition in a partitioned table and is designed to be used with especially large tables, enabling the distribution of data and indexes across many servers and disks.</p> <h2>Partition Pruning</h2> <p>By using the appropriate "WHERE" or "ON" clauses in a query, it is possible for the MySQL Optimizer to only access those partitions where matching values will exist. As a result, the execution of the query can be an order of magnitude faster than the same query against a non-partitioned table.</p> <h2>Additional Resources</h2> <ul> <li><a href="https://web.archive.org/web/20130114052213/http://dev.mysql.com/tech-resources/articles/mysql_55_partitioning.html">Partitioning Enhancements in MySQL 5.5</a></li> <li><a href="https://web.archive.org/web/20130114052213/http://dev.mysql.com/doc/refman/5.5/en/partitioning.html">MySQL Partitioning Documentation</a></li> </ul> </div>  </div> </div> <div id="footer"> <div class="links"> <ul> <li class="top"><a href="/web/20130114052213/http://www.mysql.com/products/">Products</a></li> <li><a href="/web/20130114052213/http://www.mysql.com/products/enterprise/">MySQL Enterprise Edition</a></li> <li><a href="/web/20130114052213/http://www.mysql.com/products/standard/">MySQL Standard Edition</a></li> <li><a href="/web/20130114052213/http://www.mysql.com/products/classic/">MySQL Classic Edition</a></li> <li><a href="/web/20130114052213/http://www.mysql.com/products/cluster/">MySQL Cluster CGE</a></li> <li><a href="/web/20130114052213/http://www.mysql.com/oem/">MySQL Embedded (OEM/ISV)</a></li> </ul> </div> <div class="links"> <ul> <li class="top"><a href="/web/20130114052213/http://www.mysql.com/downloads/">Downloads (GA)</a></li> <li><a href="/web/20130114052213/http://www.mysql.com/downloads/mysql/">MySQL Server</a></li> <li><a href="/web/20130114052213/http://www.mysql.com/downloads/cluster/">MySQL Cluster</a></li> <li><a href="/web/20130114052213/http://www.mysql.com/downloads/workbench/">MySQL Workbench</a></li> <li><a href="/web/20130114052213/http://www.mysql.com/downloads/connector/">MySQL Connectors</a></li> </ul> </div> <div class="links"> <ul> <li class="top"><a href="/web/20130114052213/http://www.mysql.com/services/">Services</a></li> <li><a href="/web/20130114052213/http://www.mysql.com/training/">Training</a></li> <li><a href="/web/20130114052213/http://www.mysql.com/certification/">Certification</a></li> <li><a href="/web/20130114052213/http://www.mysql.com/consulting/">Consulting</a></li> <li><a href="/web/20130114052213/http://www.mysql.com/support/">Support</a></li> </ul> </div> <div class="links"> <ul> <li class="top"><a href="https://web.archive.org/web/20130114052213/http://www.mysql.com/about/">About MySQL</a></li> <li><a href="https://web.archive.org/web/20130114052213/http://www.mysql.com/about/contact/">Contact Us</a></li> <li><a href="https://web.archive.org/web/20130114052213/http://www.mysql.com/buy-mysql/">How to Buy</a></li> <li><a href="https://web.archive.org/web/20130114052213/http://www.mysql.com/partners/">Partners</a></li> <li><a href="https://web.archive.org/web/20130114052213/http://www.mysql.com/about/jobs/">Job Opportunities</a></li> <li><a href="https://web.archive.org/web/20130114052213/http://www.mysql.com/sitemap.html">Site Map</a></li> </ul> </div> <div class="links"> <ul> <li class="top"><a href="https://web.archive.org/web/20130114052213/http://www.mysql.com/about/legal/">Legal</a></li> <li><a href="https://web.archive.org/web/20130114052213/http://www.mysql.com/about/legal/">Legal Policies</a></li> <li><a href="https://web.archive.org/web/20130114052213/http://www.oracle.com/us/legal/privacy/index.htm">Your Privacy Rights</a></li> <li><a href="https://web.archive.org/web/20130114052213/http://www.oracle.com/us/legal/terms/index.html">Terms of Use</a></li> <li><a href="https://web.archive.org/web/20130114052213/http://www.oracle.com/us/legal/third-party-trademarks/index.html">Trademark Policy</a></li> <li><a href="https://web.archive.org/web/20130114052213/http://www.oracle.com/technetwork/community/oca-486395.html">Contributor Agreement</a></li> </ul> </div> <div id="search" class="en"> <form id="footer_search" action="https://web.archive.org/web/20130114052213/http://search.oracle.com/search/search" method="get"> <input type="text" id="f_q" name="q" value="" class="swap_value" onfocus="clearSearchText();"/> <input type="hidden" name="group" value="MySQL"/> <input type="image" src="/web/20130114052213im_/http://www.mysql.com/common/themes/sakila/footer_search_g.png" id="f_go" alt="Search" title="Search"/> </form> </div> </div> <div id="copyright-oracle"><a href="https://web.archive.org/web/20130114052213/http://www.oracle.com/"><img src="/web/20130114052213im_/http://www.mysql.com/common/logos/logo-oracle-red-91x22.gif" alt="Oracle" width="91" height="22"/></a>  <span>© 2013, Oracle Corporation and/or its affiliates</span></div> <script src="/web/20130114052213js_/http://www.mysql.com/common/js/metrics/s_code_remote.js"></script> </body> </html>

CINXE.COM

MySQL :: MySQL Partitioning