DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workkloads.

Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Recover Distributed Transactions in MySQL
  • Enhancing Database Efficiency With MySQL Views: A Comprehensive Guide and Examples
  • Seamless Transition: Strategies for Migrating From MySQL to SQL Server With Minimal Downtime
  • How To Convert MySQL Database to SQL Server

Trending

  • Beyond ChatGPT, AI Reasoning 2.0: Engineering AI Models With Human-Like Reasoning
  • Fixing Common Oracle Database Problems
  • Internal Developer Portals: Modern DevOps's Missing Piece
  • Unlocking the Potential of Apache Iceberg: A Comprehensive Analysis
  1. DZone
  2. Data Engineering
  3. Databases
  4. How to Partition MySQL Tables

How to Partition MySQL Tables

This guide will walk you through the different types of table partitioning supported in MySQL and demonstrate how to implement each with examples.

By 
Everett Berry user avatar
Everett Berry
·
Oluwatomisin Bamimore user avatar
Oluwatomisin Bamimore
·
Updated Oct. 14, 21 · Tutorial
Likes (3)
Comment
Save
Tweet
Share
6.5K Views

Join the DZone community and get the full member experience.

Join For Free

Horizontal and Vertical Partitioning

There are two types of database partitions: vertical and horizontal.

Vertical Partitioning

Vertical partitioning relies upon initially creating tables with fewer columns and then using additional tables to store leftover columns. Since rows are split according to their columns, vertical partitioning is also known as row splitting (opposite horizontal partitioning). As of right now, MySQL does not support vertical partitioning in its database.

Horizontal Partitioning

The partitioning logic divides the rows into multiple tables. The number of columns remains constant throughout partitions, while the number of rows can vary. My SQL currently supports horizontal partitioning. In this post, we’ll look at three different kinds of horizontal partitions in MySQL.

Range Partitioning

When implementing range partitioning, if a column value falls within the specified range for a particular partition, the row is then added to that partition.

Implementing Range Partitioning

  1. Create a sample table:
  CREATE TABLE arctype.range_crypto(
  timestamp INT,
  open DOUBLE,
  close INT,
  high DOUBLE,
  low DOUBLE,
  volume DOUBLE,
  );

2. (Optional) Populate the table with sample data. In this case, download this dataset. Then, import the CSV data into a table.
3. Create the range partitions using the ALTER TABLE expression:

  ALTER TABLE arctype.range_crypto
  PARTITION BY RANGE (close) (
      partition p0 VALUES LESS THAN (10000),
      partition p1 VALUES LESS THAN (20000),
      partition p2 VALUES LESS THAN (30000),
      partition p3 VALUES LESS THAN (40000),
      partition p4 VALUES LESS THAN (50000),
      partition p5 VALUES LESS THAN (60000),
      partition p6 VALUES LESS THAN  MAXVALUE
  )

4. You can now query data from any of the partitions you have created:

  SELECT * 
  FROM arctype.range_crypto PARTITION (p3) 
  WHERE close BETWEEN 35000 and 38000;

Conversely, if you run this query:

  SELECT * 
  FROM arctype.range_crypto PARTITION (p0) 
  WHERE close BETWEEN 35000 and 38000;

then the result will come up empty. Specifying the partition shows MySQL where to look, making your queries run faster (if you have millions of rows).

Another variation of the range partitioning is the RANGE COLUMNS. It lets you specify more than one partitioning column. Let us create range partitions that hold a range of close prices over some time.

  ALTER TABLE arctype.range_crypto 
  PARTITION BY RANGE COLUMNS (timestamp, close) (
    PARTITION from_2018_10k VALUES LESS THAN (1533127192, 10000),
    PARTITION from_2019_20k VALUES LESS THAN (1564663192, 20000),
    PARTITION from_2020_30k VALUES LESS THAN (1596285592,30000),
    PARTITION from_2021_40k VALUES LESS THAN (1627821592,40000 ),
    PARTITION from_latest_highest VALUES LESS THAN (MAXVALUE, MAXVALUE)
  );

If you want to query all the rows with timestamps between 2018 and 2019 where prices closed between 13500 and 11600, you can run a query like this:

  SELECT * 
  FROM arctype.range_crypto PARTITION(from_2019_20k) 
  WHERE close BETWEEN 11600 AND 13500

List Partitioning

In list partitioning, rows are grouped on the premise that their value in the column used for partitioning is similar to a value in the list (set of discrete values) defined. To put things in perspective, when you create the partitioning list(s), MySQL checks the question, “Does a column in this row have a similar value with values in this list?” If the value is the same, MySQL adds that row to the partition for that value in your list.

Implementing List Partitioning

  1. Create the sample table using:
  CREATE TABLE arctype.football(
  home_team TEXT,
  away_team TEXT,
  home_goals INT,
  away_goals INT, 
  result TEXT,
  season TEXT
  );

2. Optional — Populate the table with sample data. In this case, download this dataset.

  1. You can now create the list partition using the ALTER TABLE expression.
  ALTER TABLE arctype.football
  PARTITION BY LIST (home_goals) (
  PARTITION odd VALUES IN (1,3,5,7,9),
  PARTITION even VALUES IN (0,2,4,6,8)
  );

We can now write queries using the partitions we just created:

  SELECT * FROM arctype.football PARTITION(odd) WHERE (home_goals=3);

Hash Partitioning

When defining the previous partitions, you had to state which column value, range, or column values fell into a particular partition. With hash partitioning, you must specify the number of partitions you want for the column (or expression). MySQL uses MOD(expression, number of partitions) to determine which partition any row falls in. To understand how this works, look at this example:

For the first row, the partition it belongs to based on the home_goals column is determined using MOD(1,2)=1. The partition for the third row will be selected using MOD(2,2)=0. If you have an imaginary row with 9 home_goals, MOD(9,2)=1.

Implementing Range Partitioning

You can implement range partitioning in MySQL using the same ALTER TABLE statement.

ALTER TABLE arctype.football
PARTITION BY HASH (home_goals)
PARTITIONS 2;

Conclusion

In general, partitions speed up your searches. While this is correct, the effect of partitions is not readily apparent in smaller tables. So, if your queries are executing slowly and your database table does not include millions of rows, you should consider other optimization approaches before splitting your tables.

Further Reading

  1. https://en.wikipedia.org/wiki/Partition_(database)
  2. https://dev.mysql.com/doc/refman/8.0/en/partitioning.html
  3. https://www.w3resource.com/mysql/mysql-partition.php
Database Partition (database) MySQL sql

Published at DZone with permission of Everett Berry. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Recover Distributed Transactions in MySQL
  • Enhancing Database Efficiency With MySQL Views: A Comprehensive Guide and Examples
  • Seamless Transition: Strategies for Migrating From MySQL to SQL Server With Minimal Downtime
  • How To Convert MySQL Database to SQL Server

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!