This article explores MySQL’s advanced features—including partition tables, views, stored procedures, triggers, foreign key constraints, bind variables, user‑defined functions, and character set considerations—explaining their principles, usage patterns, performance implications, and practical tips for large‑scale data scenarios.
This article explains how to handle a 200‑million‑row MySQL 5.6 table by adding indexes, cleaning 99% of old data, converting it to a partitioned table, and using online DDL and auxiliary tables to maintain continuous business operations.
Learn how to boost MySQL performance for massive tables by optimizing schema design, indexing, query patterns, engine selection, system parameters, hardware upgrades, read/write splitting, caching layers, partitioning, vertical and horizontal sharding, and choosing appropriate client or proxy sharding solutions.
A colleague handed over a seemingly simple SQL Server query that ran in five seconds for early dates but never returned for later dates, and the article walks through the diagnosis, execution‑plan analysis, statistics update, and final fix that restored performance.
Effective ETL optimization is crucial for data warehouse construction, and this guide outlines three core strategies—ensuring proper resource configuration, leveraging data characteristics for optimal partitioning and bucketing, and monitoring task execution—providing practical principles, pitfalls, and case studies to maximize ETL efficiency.
This guide explains how to transform an ordinary Oracle table larger than 2 GB into a partitioned table using the online redefinition method (DBMS_REDEFINITION), detailing each step from feasibility checks to final cleanup with example PL/SQL code.
This guide provides a comprehensive, step‑by‑step tutorial on using Oracle SQL*Plus for connecting to databases, querying tables, saving and executing scripts, handling variables, formatting output, managing tablespaces, creating external tables, and defining various partitioning strategies.
This article outlines practical techniques for optimizing large MySQL tables—including column and index tuning, query improvements, engine selection, system parameters, hardware upgrades, caching, partitioning, vertical and horizontal sharding, and discusses client‑side and proxy‑based sharding solutions and compatible databases.
A detailed case study describes how a rapidly growing edit‑log feature caused query times to soar to 30 seconds, and walks through the step‑by‑step investigation, identification of a custom function bottleneck, data‑volume analysis, and the eventual implementation of partitioning, mandatory time filters, and composite indexing to restore acceptable performance.
This guide explains how to verify and configure Oracle's automatic optimizer statistics collection, customize collection windows, tailor statistics for large and partitioned tables, detect stale statistics, and decide when to gather histograms, providing practical SQL and PL/SQL examples.
The article dissects a moderately complex Oracle SQL query from a data‑warehouse, shows how changing a parallel hint forces a full table scan that halves execution time, and then explores partitioning strategies and broader lessons on OLAP performance tuning.
The article explains when MySQL sharding is needed, outlines five practical principles for deciding to split databases or tables, provides real‑world examples, and shares operational tips for implementing horizontal partitioning to improve performance, availability, and manageability.
This article explains how Redis Cluster automatically distributes data across nodes, how clients locate the correct node using slot hashing and hash tags, and how resharding and slot migration are performed without downtime, providing a detailed overview of the partitioning mechanism.
An in‑depth case study of a sluggish SSRS report for Siemens China's MES system reveals how missing indexes, full‑table scans on billion‑row tables, and misguided partitioning cripple performance, and demonstrates practical SQL refactoring, stored‑procedure redesign, and indexing strategies that cut query time from hours to seconds.
This article analyzes a poorly performing MES reporting SQL query that scans billions of rows, identifies indexing and partitioning shortcomings, and proposes a redesign using stored procedures, temporary tables, and query‑writing best practices to achieve sub‑second response times.
This article explains the concept, benefits, methods, and practical considerations of partitioning data across multiple Redis instances, covering range and hash partitioning, consistent hashing, implementation options, drawbacks, and recommended tools such as Redis Cluster and Twemproxy.
This article examines how differing key‑hash algorithms in MySQL 5.1 versus 5.5/5.6 cause data loss in partitioned tables, describes compatibility fixes with ALTER TABLE … ALGORITHM=1, analyzes a MySQL client crash caused by mis‑redirected dump output, explores GTID_PURGED replication issues and replicate‑do‑db filtering gaps, and evaluates the behavior of OPTIMIZE TABLE on TokuDB tables.
This article presents a detailed case study of an Oracle RAC system performance bottleneck, walks through comprehensive monitoring, storage and partitioning analysis, and outlines a step‑by‑step optimization plan—including KEEP pool tuning and partition redesign—that dramatically improved I/O latency and overall throughput.
This article examines why MySQL 5.5/5.6 fails to retrieve non‑null partitioned rows after a physical upgrade, how the ALTER TABLE … PARTITION BY KEY ALGORITHM=1 fix works, the cause of MySQL client crashes when dump output mixes warnings, GTID_PURGED replication bugs, replicate‑filter side effects, and the behavior of TokuDB’s OPTIMIZE TABLE.
This article presents 58.com’s experience with MySQL under massive data volumes, covering core concepts such as single‑instance, sharding, replication and grouping, common availability and read‑write challenges, detailed sharding implementations for user, post, friend and order tables, and post‑sharding business practices including IN queries, non‑partition key queries, and cross‑database pagination.
This article compiles practical discussions on database sharding and partitioning, covering criteria such as user ID, time, and hash, redundancy techniques, caching with Redis, query handling, pagination, consistency, and distributed transaction approaches for scalable backend systems.
This article explains what MySQL table partitioning and sharding (splitting tables) are, compares their implementation, data handling, performance impact, and difficulty, and shows how they can complement each other for high‑traffic, large‑data scenarios.