Tagged articles
Table Partitioning
27 articles · Page 1 of 1
This article explains the performance drawbacks of using UUIDs as primary keys in MySQL—including larger indexes, random inserts, and slower comparisons—details why data updates trigger index refreshes, and offers practical optimization techniques such as ordered UUIDs, binary storage, hybrid auto‑increment keys, and table partitioning.
Database PerformanceIndex OptimizationMySQL
0 likes · 8 min read

This article dissects the performance problems of MySQL deep pagination, explains why OFFSET‑based queries become increasingly costly, and presents six practical optimization strategies—including cursor pagination, deferred joins, covering indexes, partitioning, precomputed pages, and Elasticsearch integration—to dramatically speed up order‑lookup queries in large e‑commerce databases.
Covering IndexCursor PaginationDeep Pagination
0 likes · 9 min read

This guide explains why and how to split large MySQL log tables by month, covering table design, core SQL statements, required Webman and ThinkORM setup, and complete PHP code for creating, querying, and inserting into monthly partitions.
MySQLPHPTable Partitioning
0 likes · 8 min read

The article walks through how to assess MySQL table size, explains why large tables slow down due to B+‑tree index overhead, and compares three practical solutions—table partitioning, database sharding (horizontal, vertical, hash‑range), and hot‑cold archiving—while outlining their trade‑offs and selection criteria.
Hot Cold ArchivingMySQLSharding
0 likes · 16 min read

This article explains the difference between database sharding and table partitioning, illustrates scaling challenges with a fast‑growing startup scenario, and compares popular sharding middleware such as Cobar, TDDL, Atlas, ShardingSphere, and Mycat, offering practical recommendations for different company sizes.
MySQLMycatShardingSphere
0 likes · 12 min read

This article explains why database sharding and partitioning become necessary, outlines performance bottlenecks, presents software and hardware optimization techniques, details horizontal and vertical splitting strategies, and discusses the added complexity such as cross‑database joins, distributed transactions, and ID generation.
Horizontal ScalingSQL OptimizationTable Partitioning
0 likes · 13 min read

When MySQL tables grow to millions of rows, insert and query latency increase, schema changes become costly, and irrelevant data bloats storage, so this guide evaluates table size, explains why B+‑tree depth hurts performance, and presents partitioning, sharding, and hot‑cold archiving solutions with practical SQL examples.
B+TreeMySQLSharding
0 likes · 15 min read

This article explains how to assess MySQL table size, understand performance degradation caused by large B+‑tree indexes, and apply three practical solutions—table partitioning, database sharding, and cold‑data archiving—to improve query speed and maintainability.
B+TreeCold Data ArchivingMySQL
0 likes · 16 min read

This article presents a hands‑on walkthrough of implementing in‑database horizontal partitioning by month for a high‑volume billing table, covering the business background, design decisions, stored‑procedure table creation, dynamic table selection in MyBatis, and query strategies such as serial‑number lookup and date‑range retrieval.
ShardingSpringBootStored Procedure
0 likes · 31 min read


ITPUBSep 18, 2023 · Databases
This article explains why a single MySQL table should stay below about twenty million rows by examining primary‑key limits, InnoDB data‑page structure, B+‑tree storage calculations, and provides a practical recommendation based on row size and fragmentation.
B+TreeDatabase DesignMySQL
0 likes · 9 min read

The article explains that, although MySQL’s theoretical limits are far higher, practical B+‑tree page and index calculations suggest keeping a single table under about twenty million one‑kilobyte rows to avoid performance degradation, illustrated with interview examples and estimation formulas.
B+TreeData ModelingMySQL
0 likes · 9 min read

This article explains how to split a massive MySQL table into smaller tables, use the MERGE engine to create a unified view, troubleshoot common errors, and implement efficient pagination across the split tables with example SQL and Java code.
MySQLPaginationSQL
0 likes · 6 min read

Database sharding and table partitioning are essential scaling techniques that separate data across multiple databases and tables to handle growing user volume, data size, and concurrency, with explanations of when to split, methods (horizontal vs vertical), and a comparison of popular middleware solutions.
Horizontal ScalingTable Partitioningdatabase sharding
0 likes · 14 min read

This article provides a comprehensive guide to MySQL performance optimization, covering CPU and I/O bottlenecks, index types and tuning, slow query capture and analysis, and best practices for table design and partitioning to improve query efficiency.
MySQLSlow QueriesTable Partitioning
0 likes · 11 min read

This article explains how to handle massive MySQL tables by splitting a single large table into multiple smaller tables, creating a MERGE (union) table to combine them, and using the merged view to implement efficient pagination, including necessary SQL statements and troubleshooting tips.
PaginationSQLTable Partitioning
0 likes · 5 min read

This article explains why high‑traffic systems need database sharding, compares table‑level and database‑level partitioning methods, presents concrete MySQL table‑creation scripts and Java service code, and discusses the advantages, drawbacks, and common middleware solutions such as Sharding‑JDBC and Mycat.
Horizontal ScalingMySQLMycat
0 likes · 12 min read

This article walks through a practical approach to safely split a 50 million‑row MySQL history table, add missing indexes, and migrate data in batches using backup, table‑copy, and incremental INSERT operations while avoiding long locks and performance degradation.
IndexingMySQLTable Partitioning
0 likes · 9 min read

This article explains why and when to use table partitioning in PostgreSQL, describes inheritance‑based and declarative partitioning methods (list, range, hash), provides complete SQL and C code examples for creating and maintaining partitions, compares version differences, and shows a real‑world performance improvement case from Qunar.
C extensionsSQL PerformanceTable Partitioning
0 likes · 24 min read

This article explains how ordered data indexes improve query efficiency, compares B+Tree and hash indexes, discusses table partitioning versus sharding, outlines MVCC read types, examines row‑level lock pros and cons, and provides practical MySQL optimization tips including key vs index differences and engine choices.
B+TreeMySQLTable Partitioning
0 likes · 10 min read

This article explains why large tables hurt performance, defines database sharding and its vertical and horizontal strategies, compares partitioning by key, size, or time, and outlines practical implementation options and migration steps for robust, scalable backend systems.
Table Partitioningbackend developmentdatabase sharding
0 likes · 12 min read


21CTOOct 31, 2017 · Databases
This article explains InnoDB's record storage mechanics, compares auto‑increment and business primary keys, details primary, secondary, and composite index structures, and shares practical table‑design guidelines and real‑world case studies from ZhaiZhai's MySQL usage.
Composite IndexInnoDBMySQL
0 likes · 12 min read


ITPUBOct 9, 2017 · Databases
PostgreSQL 10, released after months of beta testing, brings logical replication, declarative table partitioning, higher query concurrency, notable performance gains, stronger SCRAM‑SHA‑256 authentication, and enhanced monitoring and control capabilities, marking a significant evolution for the open‑source database.
PostgreSQLTable Partitioningdatabase
0 likes · 3 min read

This article explains how to enable MySQL scheduled events, shows the full CREATE EVENT syntax with practical timing examples, covers advanced operations like multi‑statement events and disabling, and then dives into table partitioning techniques (RANGE, HASH, KEY, LIST) with detailed commands and troubleshooting for large InnoDB tables.
InnoDBMySQLScheduled Events
0 likes · 8 min read

Learn why massive MySQL tables need partitioning and how techniques like table splitting, merge storage engine, vertical partitioning, and horizontal sharding—combined with master‑slave replication and proxies such as Amoeba—can reduce load, improve query speed, and enhance scalability.
Horizontal PartitioningMySQLTable Partitioning
0 likes · 6 min read


21CTOMay 15, 2016 · Databases
This article presents practical database optimization techniques, including SQL Server table partitioning to handle massive datasets and using the XML data type to replace traditional master‑detail designs, offering clearer schemas and faster queries for large‑scale applications.
SQL ServerTable PartitioningXML Data Type
0 likes · 9 min read

This guide explains how to transform ordinary, partitioned, or index‑organized Oracle tables to new structures online, using the DBMS_REDEFINITION package, while minimizing lock time and avoiding service interruption in 24‑7 environments.
DBMS_REDEFINITIONOnline RedefinitionOracle
0 likes · 8 min read

This guide explains why Zabbix monitoring can become sluggish with large MySQL tables, and provides step‑by‑step instructions—including table modifications, stored procedures for creating, dropping, maintaining, and verifying partitions, plus automation scripts—to shrink the database and improve responsiveness.
MySQLTable Partitioningdatabase optimization
0 likes · 10 min read
