BestHub
Sign in
Tagged articles
5000 articles
Page 31 of 50
Open Source Linux
Open Source Linux
Apr 19, 2022 · Databases

8 Common SQL Pitfalls and How to Optimize Them for Faster Queries

This article examines frequent SQL anti‑patterns—such as inefficient LIMIT usage, implicit type conversion, suboptimal UPDATE/DELETE joins, mixed ordering, unnecessary EXISTS clauses, condition push‑down failures, early limiting, and intermediate result handling—and provides rewritten queries that dramatically improve performance.

DatabaseMySQLPerformance Optimization
0 likes · 14 min read
8 Common SQL Pitfalls and How to Optimize Them for Faster Queries
dbaplus Community
dbaplus Community
Apr 17, 2022 · Databases

Why Killing Slow Queries Often Fails and a Safer MySQL Timeout Solution

The article examines how slow‑query‑induced avalanches arise in MySQL, explains why naïvely killing slow queries is fraught with accuracy, automation and responsibility issues, and proposes a signature‑based timeout mechanism that lets applications safely abort problematic queries.

DBADatabase PerformanceMySQL
0 likes · 17 min read
Why Killing Slow Queries Often Fails and a Safer MySQL Timeout Solution
Java Captain
Java Captain
Apr 17, 2022 · Backend Development

Kindergarten Management System – Overview, Tech Stack, Deployment Guide, and Backend Controller Code

The article presents a kindergarten management system featuring three user roles, outlines its Spring Boot and Thymeleaf technology stack, details required runtime and database environments, provides step‑by‑step deployment instructions, and includes complete backend controller source code for user, teacher, student, and school management functionalities.

Backend DevelopmentJavaMySQL
0 likes · 19 min read
Kindergarten Management System – Overview, Tech Stack, Deployment Guide, and Backend Controller Code
Programmer DD
Programmer DD
Apr 16, 2022 · Databases

Mastering MySQL Hierarchical Queries with the Nested Set Model

This article explains how to replace inefficient recursive MySQL tree queries with the nested‑set (preorder traversal) technique, covering descendant retrieval, subtree counting, leaf detection, insertion, deletion, direct‑child lookup, ancestor path queries, and a JavaScript example for building a tree structure.

Database DesignHierarchical QueryMySQL
0 likes · 12 min read
Mastering MySQL Hierarchical Queries with the Nested Set Model
Practical DevOps Architecture
Practical DevOps Architecture
Apr 15, 2022 · Operations

Zabbix Monitoring Data Cleanup Bash Script

After prolonged Zabbix monitoring generates massive history tables, this guide provides a Bash script that stops Zabbix services, disables foreign key checks, truncates and optimizes various history tables in MySQL, backs up the database, and restarts the services.

MySQLdatabase cleanupzabbix
0 likes · 3 min read
Zabbix Monitoring Data Cleanup Bash Script
Architecture & Thinking
Architecture & Thinking
Apr 14, 2022 · Databases

Mastering MySQL Indexes: Boost Query Performance with B‑Tree, Prefix, and Composite Strategies

This article explains how MySQL stores indexes using B+ trees, how to evaluate index selectivity and prefix length, the importance of the leftmost principle for composite indexes, and practical techniques such as covering indexes, index condition pushdown, and avoiding common pitfalls that render indexes ineffective, all illustrated with real‑world query examples and performance measurements.

B+TreeDatabase PerformanceMySQL
0 likes · 36 min read
Mastering MySQL Indexes: Boost Query Performance with B‑Tree, Prefix, and Composite Strategies
Su San Talks Tech
Su San Talks Tech
Apr 14, 2022 · Databases

How MySQL Uses Indexes for GROUP BY: Compact vs Loose Scans Explained

This article explains how MySQL can implement GROUP BY using either a compact index scan that reads rows sequentially or a loose index scan that jumps to the needed rows, compares their costs, and shows when each method is optimal, including a hybrid sequential‑loose scan.

GROUP BYMySQLcompact index scan
0 likes · 15 min read
How MySQL Uses Indexes for GROUP BY: Compact vs Loose Scans Explained
Top Architect
Top Architect
Apr 14, 2022 · Databases

Analyzing the Cost of Establishing MySQL Database Connections in Java

This article investigates how much time a simple Java program spends establishing and closing a MySQL connection, measures the latency of each TCP and MySQL handshake step with Wireshark, and demonstrates why connection pooling is essential for high‑traffic web services.

Connection PoolingDatabase ConnectionJava
0 likes · 8 min read
Analyzing the Cost of Establishing MySQL Database Connections in Java
Programmer DD
Programmer DD
Apr 14, 2022 · Databases

How to Speed Up MySQL LIMIT Pagination from 16 s to 0.3 s with Subquery Optimization

This article demonstrates how a MySQL pagination query on a 9.5‑million‑row table that originally took over 16 seconds can be accelerated to under 0.4 seconds by moving the LIMIT condition into a subquery that selects only primary‑key IDs and then joining to retrieve the remaining columns, with detailed code and experimental verification.

InnoDBLimit OptimizationMySQL
0 likes · 10 min read
How to Speed Up MySQL LIMIT Pagination from 16 s to 0.3 s with Subquery Optimization
Big Data Technology & Architecture
Big Data Technology & Architecture
Apr 13, 2022 · Databases

Common MySQL Index Failure Scenarios and Optimization Techniques

This article explains typical MySQL index failure cases such as left‑most matching truncation, implicit conversion, IN + ORDER BY, range queries, leading wildcard, OR conditions, function usage, NOT IN/!=, low selectivity, and index fragmentation, and provides practical optimization strategies including index redesign, covering indexes, and pagination improvements.

Database PerformanceIndex OptimizationMySQL
0 likes · 17 min read
Common MySQL Index Failure Scenarios and Optimization Techniques
Architecture & Thinking
Architecture & Thinking
Apr 11, 2022 · Databases

Unlocking MySQL Indexes: How B+ Trees Boost Query Performance

This article explains how MySQL implements indexes using B+ trees in InnoDB and MyISAM, compares primary and secondary indexes, demonstrates performance gains from proper indexing on large tables, and provides practical commands for creating, viewing, and dropping indexes.

B+TreeMySQLperformance
0 likes · 12 min read
Unlocking MySQL Indexes: How B+ Trees Boost Query Performance
Top Architect
Top Architect
Apr 11, 2022 · Databases

Why Using UUID as MySQL Primary Key Is Inefficient: A Performance Comparison with Auto‑Increment and Random Keys

This article investigates MySQL's recommendation against UUID primary keys by creating three tables—auto‑increment, UUID, and random (snowflake) keys—running insert and query benchmarks with SpringBoot/JdbcTemplate, analyzing index structures, and discussing the performance trade‑offs and security considerations of each key strategy.

DatabaseMySQLauto_increment
0 likes · 11 min read
Why Using UUID as MySQL Primary Key Is Inefficient: A Performance Comparison with Auto‑Increment and Random Keys
Top Architect
Top Architect
Apr 9, 2022 · Databases

Applying the Nested Set Model for Hierarchical Department Queries in MySQL

This article explains how to use the nested set (left‑right) model to efficiently query, count, and manipulate hierarchical department data in MySQL, providing SQL examples for retrieving descendants, ancestors, leaf detection, as well as insert, delete, and JavaScript tree‑building techniques.

DatabaseMySQLNested Set Model
0 likes · 11 min read
Applying the Nested Set Model for Hierarchical Department Queries in MySQL
Architect's Tech Stack
Architect's Tech Stack
Apr 8, 2022 · Databases

Comprehensive Guide to MySQL Query Optimization and Common Pitfalls

This article explains why SQL performance degrades as data grows, outlines a step‑by‑step optimization workflow—including slow‑query detection, EXPLAIN analysis, profiling, optimizer tracing, and corrective actions—while detailing type and extra fields, index usage, and practical scenarios such as large pagination, IN‑order‑by, range queries, and complex joins.

DatabaseMySQLindexing
0 likes · 10 min read
Comprehensive Guide to MySQL Query Optimization and Common Pitfalls
Liangxu Linux
Liangxu Linux
Apr 7, 2022 · Databases

Essential MySQL Interview Guide: Normal Forms, Permissions, ACID, Indexes & More

This comprehensive guide covers MySQL fundamentals including the three normal forms, permission tables, ACID properties, index design principles, SQL statement categories, sharding, deadlock resolution, isolation anomalies, view characteristics, SQL lifecycle, primary key choices, performance troubleshooting, replication, GTID, backup tools, and backup planning.

Database DesignMySQLPerformance tuning
0 likes · 13 min read
Essential MySQL Interview Guide: Normal Forms, Permissions, ACID, Indexes & More
Java Backend Technology
Java Backend Technology
Apr 7, 2022 · Backend Development

Does @Transactional Commit Before Unlock? Deep Dive into Spring Transaction Timing

This article explores the precise moment a Spring @Transactional transaction is committed relative to a surrounding lock release, revealing that commits occur after the method finishes, and demonstrates how to verify this through source code inspection, debugging techniques, and proper lock‑usage to prevent overselling.

DebuggingLockMySQL
0 likes · 21 min read
Does @Transactional Commit Before Unlock? Deep Dive into Spring Transaction Timing
Aikesheng Open Source Community
Aikesheng Open Source Community
Apr 6, 2022 · Databases

Using MySQL 8.0 component_log_filter_dragnet to Filter and Transform Error Logs

This article explains how to install and configure MySQL 8.0's component_log_filter_dragnet, describes its core filter fields and actions, and provides practical examples for customizing error‑log output such as dropping, throttling, setting, and unsetting log entries based on time, priority, error codes, symbols, and subsystems.

Log FilteringMySQLcomponent_log_filter_dragnet
0 likes · 11 min read
Using MySQL 8.0 component_log_filter_dragnet to Filter and Transform Error Logs
HomeTech
HomeTech
Apr 6, 2022 · Databases

MySQL High Availability Architecture and Practices at AutoHome

This article explains MySQL high‑availability concepts, defines HA, RPO and RTO, outlines common HA architectures such as master‑slave+VIP, MHA and MGR+Proxy, and details AutoHome's evolution from simple master‑slave setups to a container‑based MGR solution with automated failover and monitoring platforms.

DatabaseMGRMHA
0 likes · 11 min read
MySQL High Availability Architecture and Practices at AutoHome
Liangxu Linux
Liangxu Linux
Apr 5, 2022 · Databases

Efficiently Query Multi‑Business‑Entity Sales Data with MySQL Row Comparison

This article explains how to retrieve sales statistics for multiple business units and their selected products in MySQL, compares loop‑based, OR‑concatenated, mixed‑filter, and SQL‑92 row‑comparison approaches, and shows why the row‑comparison technique was ultimately chosen for its index usage and simplicity.

Database DesignDynamic SQLMySQL
0 likes · 6 min read
Efficiently Query Multi‑Business‑Entity Sales Data with MySQL Row Comparison
Architecture Digest
Architecture Digest
Apr 4, 2022 · Databases

Splitting a Massive MySQL Financial Transaction Table: Challenges, Sharding Strategy, and Migration Process

This article describes how a finance team tackled a 500 million‑row MySQL transaction table by analyzing pre‑split issues, defining sharding goals, selecting sharding‑jdbc, addressing multi‑datasource transaction and pagination challenges, designing a hybrid data‑migration plan, and executing a three‑stage rollout to ensure system stability and performance.

Data MigrationMySQLdatabase scaling
0 likes · 11 min read
Splitting a Massive MySQL Financial Transaction Table: Challenges, Sharding Strategy, and Migration Process
21CTO
21CTO
Apr 3, 2022 · Backend Development

How We Achieved 20k+ TPS High Availability for a Billion‑User Membership System

This article details the design and implementation of a highly available, high‑performance membership system serving over a billion users, covering Elasticsearch dual‑center clusters, traffic isolation, Redis caching, MySQL migration, and fine‑grained flow‑control and degradation strategies.

ElasticsearchMySQLRedis
0 likes · 21 min read
How We Achieved 20k+ TPS High Availability for a Billion‑User Membership System
Open Source Linux
Open Source Linux
Apr 2, 2022 · Databases

Mastering MySQL with Sequel Pro: A Complete Beginner's Guide

Sequel Pro is a user‑friendly MySQL client that lets you connect via standard, socket or SSH methods, configure connection details, test and save them, and then explore databases through panels for selecting schemas, viewing table structures, contents, relationships, triggers, table info, and running queries.

Database ManagementGUIMySQL
0 likes · 3 min read
Mastering MySQL with Sequel Pro: A Complete Beginner's Guide
Java Interview Crash Guide
Java Interview Crash Guide
Apr 1, 2022 · Databases

Why MySQL Uses B+ Trees Over B Trees: Deep Dive into Index Structures

Explore MySQL's comprehensive index guide, covering basic concepts, B‑tree and B+‑tree structures, storage engine differences, index types, optimization strategies, and practical tips for designing effective primary, secondary, and composite indexes to boost query performance and reduce I/O overhead.

B+TreeDatabase OptimizationInnoDB
0 likes · 35 min read
Why MySQL Uses B+ Trees Over B Trees: Deep Dive into Index Structures
Open Source Linux
Open Source Linux
Apr 1, 2022 · Databases

Master MySQL Performance: Deep Dive into Indexes, Explain Plans, and Optimization Techniques

This comprehensive guide explores MySQL's architecture, explains how indexes work, details the use of EXPLAIN to analyze query execution, and provides practical optimization strategies—including index creation, avoiding common pitfalls, and improving single- and multi-table queries—to dramatically boost database performance.

IndexesMySQLPerformance tuning
0 likes · 33 min read
Master MySQL Performance: Deep Dive into Indexes, Explain Plans, and Optimization Techniques
Laravel Tech Community
Laravel Tech Community
Mar 31, 2022 · Databases

SQL Optimization Steps and Common Cases for Improving Query Performance

This article outlines a systematic approach to identifying and fixing slow MySQL queries, explains how to interpret EXPLAIN output and profiling information, and presents multiple real‑world cases illustrating index usage, implicit conversion, large pagination, IN queries, and other optimization techniques.

Database OptimizationExplain PlanMySQL
0 likes · 10 min read
SQL Optimization Steps and Common Cases for Improving Query Performance
Programmer DD
Programmer DD
Mar 31, 2022 · Databases

Mastering MySQL: Proven Steps to Diagnose and Optimize Slow Queries

This guide walks through systematic MySQL query optimization—from identifying slow statements via logs and EXPLAIN analysis, to profiling, tracing, and applying targeted fixes such as index tuning, query rewrites, and handling special cases like left‑most matching, implicit conversion, large pagination, and IN‑order‑by pitfalls.

Index TuningMySQLQuery Profiling
0 likes · 13 min read
Mastering MySQL: Proven Steps to Diagnose and Optimize Slow Queries
Java Interview Crash Guide
Java Interview Crash Guide
Mar 31, 2022 · Backend Development

How We Achieved 20k TPS High‑Availability for a Billion‑User Membership System

This article details the design and implementation of a highly available, high‑performance membership system serving billions of users, covering Elasticsearch dual‑center clusters, traffic‑isolated architectures, deep ES optimizations, Redis caching with distributed locks, dual‑center MySQL partitioning, migration strategies, abnormal account handling, and future fine‑grained flow‑control and degradation policies.

CachingDistributed SystemsElasticsearch
0 likes · 20 min read
How We Achieved 20k TPS High‑Availability for a Billion‑User Membership System
Architect's Journey
Architect's Journey
Mar 30, 2022 · Databases

How Does MySQL InnoDB Ensure ACID Guarantees?

This article breaks down how MySQL's InnoDB engine guarantees the four ACID properties—atomicity, consistency, isolation, and durability—by leveraging undo and redo logs, the binary log, hidden transaction metadata, and MVCC with ReadView, illustrating each mechanism with concrete examples.

ACIDInnoDBMVCC
0 likes · 9 min read
How Does MySQL InnoDB Ensure ACID Guarantees?
NiuNiu MaTe
NiuNiu MaTe
Mar 30, 2022 · Databases

Why Is MySQL Query Slow? Hidden Factors and Proven Speed‑Up Tricks

This article explains why MySQL queries can become sluggish beyond missing indexes, covering the full query execution flow, profiling tools, index pitfalls, connection‑pool limits, InnoDB buffer‑pool sizing, and practical steps to diagnose and accelerate database performance.

Connection PoolMySQLbuffer pool
0 likes · 16 min read
Why Is MySQL Query Slow? Hidden Factors and Proven Speed‑Up Tricks
Laravel Tech Community
Laravel Tech Community
Mar 29, 2022 · Databases

Efficient Methods for Removing Duplicate Records in MySQL Tables

This article explains why a naïve Python‑based row‑deletion approach is slow for large MySQL tables and provides step‑by‑step SQL techniques—including identifying duplicate names, handling MySQL’s update‑from‑same‑table limitation, and deleting duplicates while preserving a single record per group—complete with executable code examples.

Data cleaningMySQLduplicate removal
0 likes · 5 min read
Efficient Methods for Removing Duplicate Records in MySQL Tables
Tencent Database Technology
Tencent Database Technology
Mar 29, 2022 · Fundamentals

Understanding Memory Management: From OS to MySQL

This article explores memory management concepts from both operating system and MySQL perspectives, covering virtual vs physical memory, process memory usage analysis using tools like top and /proc, and experimental verification of memory allocation behaviors.

LinuxMySQLOperating Systems
0 likes · 9 min read
Understanding Memory Management: From OS to MySQL
Open Source Linux
Open Source Linux
Mar 29, 2022 · Databases

Essential MySQL SQL Optimization Tips to Speed Up Your Queries

This guide presents a comprehensive set of MySQL optimization techniques—including proper use of EXPLAIN, limiting IN clause size, avoiding SELECT *, using LIMIT 1, minimizing sorting, replacing OR with UNION ALL, preferring UNION ALL over UNION, eliminating ORDER BY RAND(), distinguishing IN versus EXISTS, applying efficient pagination, segmenting large scans, avoiding NULL checks and leading wildcards, rewriting expressions, preventing implicit type conversion, respecting composite index leftmost rules, forcing indexes, handling range queries, and optimizing JOINs—to dramatically improve query performance.

Database PerformanceEXPLAINJoin
0 likes · 11 min read
Essential MySQL SQL Optimization Tips to Speed Up Your Queries
Liangxu Linux
Liangxu Linux
Mar 28, 2022 · Databases

Master MySQL Performance: Indexes, Explain Plans, and Optimization Tips

This comprehensive guide explains MySQL's architecture, storage engines, and the critical role of indexes, walks through the SQL parsing process, details how to read and interpret EXPLAIN output, and provides practical optimization examples for single‑table and multi‑table queries.

Database PerformanceEXPLAINIndexes
0 likes · 38 min read
Master MySQL Performance: Indexes, Explain Plans, and Optimization Tips
MaGe Linux Operations
MaGe Linux Operations
Mar 28, 2022 · Databases

Why GitHub’s MySQL Cluster Crashed: Lessons from Recent Outages

GitHub experienced multiple service outages over recent weeks due to resource contention in its MySQL1 cluster, leading to prolonged downtimes, and the company disclosed detailed timelines, root causes, and ongoing mitigation measures such as load audits, traffic shifting, and infrastructure scaling to prevent future incidents.

GitHubMySQLOperations
0 likes · 3 min read
Why GitHub’s MySQL Cluster Crashed: Lessons from Recent Outages
Java Captain
Java Captain
Mar 26, 2022 · Backend Development

Vaccination Management System – Features, Architecture, and Core Backend Code

This article presents a Java Spring Boot vaccination management system that supports user registration, vaccine record handling, case history, nucleic‑acid test reporting, travel tracking, risk‑area management, and epidemic‑prevention knowledge, detailing its roles, technology stack, database design, UI screenshots, and core backend source code.

JavaMySQLREST API
0 likes · 13 min read
Vaccination Management System – Features, Architecture, and Core Backend Code
Java Captain
Java Captain
Mar 25, 2022 · Databases

Common MySQL Interview Questions and Answers

This article compiles essential MySQL interview topics, covering normal forms, privilege tables, ACID properties, SQL statement categories, sharding, deadlocks, isolation anomalies, view characteristics, statement lifecycle, primary key choices, CPU troubleshooting, replication, GTID, backup tools, and backup planning strategies.

Database DesignInterview QuestionsMySQL
0 likes · 15 min read
Common MySQL Interview Questions and Answers
Java Interview Crash Guide
Java Interview Crash Guide
Mar 25, 2022 · Databases

Master MySQL Performance: Proven Steps to Optimize Slow Queries

Learn how to identify and fix slow MySQL queries by analyzing slow‑query logs, using EXPLAIN, profiling, and optimizer trace, understanding index types and extra flags, and applying practical case‑by‑case optimizations such as index tuning, query rewriting, pagination strategies, and handling large data sets.

DatabaseIndex TuningMySQL
0 likes · 11 min read
Master MySQL Performance: Proven Steps to Optimize Slow Queries
Java High-Performance Architecture
Java High-Performance Architecture
Mar 24, 2022 · Databases

How to Efficiently Query Millions of Rows in MySQL: Pagination Tricks and Optimizations

This article walks through generating a 10‑million‑row MySQL table, inserting data with a fast batch script, measuring plain LIMIT pagination performance, and applying several optimization techniques—including sub‑queries, ID‑range filtering, and column selection—to dramatically reduce query time on large data sets.

Batch InsertMySQLSQL optimization
0 likes · 9 min read
How to Efficiently Query Millions of Rows in MySQL: Pagination Tricks and Optimizations
Java Backend Technology
Java Backend Technology
Mar 23, 2022 · Databases

Master MySQL Performance: 10 Proven SQL Optimization Techniques

This article walks through why SQL queries become bottlenecks as data grows and presents a step‑by‑step guide—including slow‑query analysis, EXPLAIN interpretation, profiling, optimizer tracing, index tuning, and scenario‑specific tricks—to dramatically improve MySQL performance.

Explain PlanIndex TuningMySQL
0 likes · 12 min read
Master MySQL Performance: 10 Proven SQL Optimization Techniques
IT Architects Alliance
IT Architects Alliance
Mar 22, 2022 · Backend Development

High‑Availability Architecture for a Membership System: Elasticsearch Dual‑Center, Redis Caching, MySQL Migration and Fine‑Grained Flow Control

This article details the design and implementation of a high‑availability membership system, covering Elasticsearch dual‑center master‑slave clusters, traffic‑isolated three‑cluster ES architecture, Redis multi‑center caching, MySQL dual‑center partitioning, data migration strategies, and refined flow‑control and degradation mechanisms to ensure stable, low‑latency service under massive concurrent load.

Distributed SystemsElasticsearchMySQL
0 likes · 20 min read
High‑Availability Architecture for a Membership System: Elasticsearch Dual‑Center, Redis Caching, MySQL Migration and Fine‑Grained Flow Control
Aikesheng Open Source Community
Aikesheng Open Source Community
Mar 21, 2022 · Databases

MySQL‑to‑DBLE Data Synchronization Using DTLE: Full and GTID‑Based Incremental Sync Guide

This article provides a step‑by‑step tutorial on deploying a DBLE cluster, configuring sharding.xml, db.xml and user.xml, creating test tables, installing DTLE, performing full MySQL‑to‑DBLE data migration, then switching to GTID‑based incremental synchronization with detailed troubleshooting tips and best‑practice recommendations.

DBLEDTLEDatabase Synchronization
0 likes · 15 min read
MySQL‑to‑DBLE Data Synchronization Using DTLE: Full and GTID‑Based Incremental Sync Guide
Java Interview Crash Guide
Java Interview Crash Guide
Mar 21, 2022 · Databases

Mastering Multi‑Entity Sales Queries with SQL Row Comparison

This article explains how to design a MySQL table for product sales statistics and compares several query strategies—including loop queries, OR concatenation, mixed filtering, and the SQL‑92 row‑by‑row comparison—to efficiently retrieve sales data for dynamic sets of business institutions and their products in a single indexed query.

Database QueryMySQLRow Comparison
0 likes · 7 min read
Mastering Multi‑Entity Sales Queries with SQL Row Comparison
dbaplus Community
dbaplus Community
Mar 20, 2022 · Databases

Master MySQL Performance: Step-by-Step SQL Optimization Techniques

This guide walks through essential MySQL performance tuning methods, from identifying slow queries via logs and EXPLAIN analysis to interpreting Extra information, using profiling and optimizer trace, and applying concrete index, query, and schema adjustments for common scenarios such as left‑most matching, implicit conversion, large pagination, IN‑order‑by, and complex queries.

EXPLAINIndex TuningMySQL
0 likes · 12 min read
Master MySQL Performance: Step-by-Step SQL Optimization Techniques
Top Architect
Top Architect
Mar 20, 2022 · Backend Development

High‑Availability Architecture for a Membership System: Elasticsearch Dual‑Center Cluster, Redis Caching, MySQL Migration, and Flow‑Control Strategies

The article details a comprehensive high‑availability solution for a large‑scale membership system, covering Elasticsearch dual‑center master‑slave clusters, traffic‑isolated three‑cluster designs, deep ES optimizations, Redis caching with consistency safeguards, MySQL partitioned migration, and fine‑grained flow‑control and degradation mechanisms.

ElasticsearchFlow ControlMySQL
0 likes · 19 min read
High‑Availability Architecture for a Membership System: Elasticsearch Dual‑Center Cluster, Redis Caching, MySQL Migration, and Flow‑Control Strategies
Java High-Performance Architecture
Java High-Performance Architecture
Mar 20, 2022 · Databases

Master MySQL Data Recovery: From Full Backups to Point‑in‑Time Restoration

This guide explains how to prevent data loss in MySQL by enabling row‑based binlog, performing full restores with mysqldump or xtrabackup, executing point‑in‑time recovery, restoring individual tables, skipping erroneous SQL statements using binlog or GTID, and leveraging flashback tools such as binlog2sql and MyFlash.

FlashbackMySQLRecovery
0 likes · 15 min read
Master MySQL Data Recovery: From Full Backups to Point‑in‑Time Restoration
Architecture Digest
Architecture Digest
Mar 18, 2022 · Backend Development

High‑Availability Architecture for a Membership System: Elasticsearch Dual‑Center Cluster, Redis Caching, and MySQL Migration

This article details the design and implementation of a high‑performance, highly available membership system, covering Elasticsearch dual‑center master‑slave clusters, traffic‑isolated three‑cluster ES architecture, Redis cache strategies, MySQL dual‑center partitioning, seamless migration, abnormal member handling, and fine‑grained flow‑control and degradation policies.

ElasticsearchFlow ControlMySQL
0 likes · 20 min read
High‑Availability Architecture for a Membership System: Elasticsearch Dual‑Center Cluster, Redis Caching, and MySQL Migration
Architecture & Thinking
Architecture & Thinking
Mar 17, 2022 · Databases

Why Large OFFSETs Slow Down MySQL Queries and How to Fix Them

This article analyzes a production incident where massive OFFSET values in MySQL pagination caused severe query slowdown, demonstrates the problem with simulated data, benchmarks the impact, and presents three practical solutions—including index‑covering subqueries, remembering the last primary key, and limiting offset size—to dramatically improve performance and protect the database.

MySQLindexingperformance
0 likes · 13 min read
Why Large OFFSETs Slow Down MySQL Queries and How to Fix Them
Qunar Tech Salon
Qunar Tech Salon
Mar 17, 2022 · Databases

Killing Slow Queries in MySQL: Problems, Drawbacks, and Practical Design Solutions

The article analyses why indiscriminately killing slow MySQL queries is risky, enumerates the drawbacks of common kill‑based approaches, and proposes more reliable designs such as registration, signature‑based killing, and source‑code modifications to safely mitigate query‑induced database avalanches.

Database PerformanceMySQLSQL signature
0 likes · 16 min read
Killing Slow Queries in MySQL: Problems, Drawbacks, and Practical Design Solutions
政采云技术
政采云技术
Mar 17, 2022 · Databases

InnoDB Lock System: Types, Modes, Structures, and Compatibility

This article explains InnoDB's lock system in MySQL, covering lock granularity, intent, shared, exclusive and auto‑increment locks, row‑lock types, the underlying C++ structures, lock mode and type encoding, and the compatibility and strength matrices that govern lock acquisition and waiting.

C++ConcurrencyDatabase
0 likes · 13 min read
InnoDB Lock System: Types, Modes, Structures, and Compatibility
Cloud Native Technology Community
Cloud Native Technology Community
Mar 15, 2022 · Databases

How to Build a High‑Availability MySQL PXC Cluster: Installation & Features

This guide explains the Percona XtraDB Cluster (PXC) architecture, its advantages and limitations, and provides step‑by‑step commands for removing MariaDB, opening firewall ports, disabling SELinux, downloading packages, configuring MySQL, bootstrapping the first node, adding additional nodes, and verifying the cluster status.

ClusterDatabaseInstallation
0 likes · 8 min read
How to Build a High‑Availability MySQL PXC Cluster: Installation & Features
Aikesheng Open Source Community
Aikesheng Open Source Community
Mar 15, 2022 · Databases

Using MySQL 8.0.27 Multi‑Factor Authentication with the authentication_policy Variable

This article explains how MySQL 8.0.27 adds multi‑factor authentication (MFA) via the new authentication_policy system variable, demonstrates configuring the variable, creating users with single or multiple authentication plugins, installing external plugins, and verifies MFA behavior with practical Docker‑based examples.

DatabasesMFAMySQL
0 likes · 10 min read
Using MySQL 8.0.27 Multi‑Factor Authentication with the authentication_policy Variable
ITPUB
ITPUB
Mar 14, 2022 · Databases

Master MySQL Performance: Practical SQL, Index, Join, and Hardware Tuning

This article provides a comprehensive guide to MySQL performance improvement, covering pagination, index, join, sorting, UNION, slow‑query‑log, schema design, and hardware optimizations with concrete SQL examples, best‑practice tips, and cautionary notes for real‑world deployments.

Database DesignMySQLPerformance tuning
0 likes · 16 min read
Master MySQL Performance: Practical SQL, Index, Join, and Hardware Tuning
Top Architect
Top Architect
Mar 14, 2022 · Databases

Implementing Multi‑Business‑Unit Sales Queries with SQL Row‑Comparison in MySQL

This article explains how to design a MySQL table for product sales statistics and compares several query strategies—including loop queries, OR concatenation, mixed filtering, and the SQL‑92 row‑comparison technique—to efficiently retrieve sales data for multiple business units and their associated products in a single indexed query.

Database OptimizationDynamic SQLMyBatis
0 likes · 7 min read
Implementing Multi‑Business‑Unit Sales Queries with SQL Row‑Comparison in MySQL
Programmer DD
Programmer DD
Mar 14, 2022 · Databases

Does MySQL int(1) Limit Your Data? The Truth About Display Width and ZEROFILL

Although MySQL’s int type always occupies 4 bytes, the number in parentheses (e.g., int(1) or int(10)) does not restrict its range; only when combined with ZEROFILL does it affect display formatting, as demonstrated by inserting the maximum unsigned int value and observing zero‑padded outputs.

MySQLZEROFILLdisplay width
0 likes · 6 min read
Does MySQL int(1) Limit Your Data? The Truth About Display Width and ZEROFILL
Practical DevOps Architecture
Practical DevOps Architecture
Mar 14, 2022 · Databases

Step-by-Step MySQL Full and Incremental Backup & Restore Using XtraBackup

This tutorial walks through creating a MySQL database, setting up a backup user, performing full and incremental backups with XtraBackup, compressing and transferring backup files, and restoring the database by preparing logs, copying back data, fixing permissions, restarting MySQL, and verifying the restored data.

Database RestoreFull BackupIncremental Backup
0 likes · 9 min read
Step-by-Step MySQL Full and Incremental Backup & Restore Using XtraBackup
IT Services Circle
IT Services Circle
Mar 12, 2022 · Databases

Understanding MySQL Master‑Slave Replication and Read/Write Splitting

This article explains the basics of MySQL master‑slave replication, illustrates how read/write splitting works to alleviate performance bottlenecks, and shares practical test results showing reduced latency on the master and improved overall throughput when directing reads to replica servers.

DatabasePerformanceMySQLReadWriteSplitting
0 likes · 4 min read
Understanding MySQL Master‑Slave Replication and Read/Write Splitting
21CTO
21CTO
Mar 9, 2022 · Databases

Mastering Multi‑Business Sales Queries with Row‑Comparison in MySQL

This article explores how to query sales data for multiple business units and selected products in MySQL, comparing loop queries, OR concatenation, mixed filtering, and SQL‑92 row‑value comparison, ultimately recommending the row‑comparison technique for efficiency and maintainability.

Database DesignDynamic SQLMySQL
0 likes · 8 min read
Mastering Multi‑Business Sales Queries with Row‑Comparison in MySQL
DaTaobao Tech
DaTaobao Tech
Mar 7, 2022 · Databases

MySQL InnoDB Lock Types and Deadlock Analysis

The article explains MySQL InnoDB lock scopes and modes—including record, gap, next‑key and insert‑intention locks—illustrates a deadlock caused by concurrent inserts on a distributed lock table, and recommends limiting concurrency, sharding by business key, and splitting lock operations into smaller sub‑transactions to avoid such deadlocks.

DatabaseInnoDBLock
0 likes · 16 min read
MySQL InnoDB Lock Types and Deadlock Analysis
Sanyou's Java Diary
Sanyou's Java Diary
Mar 5, 2022 · Databases

Unlock MySQL’s Secrets: Redo Log, Undo Log, Binlog & Transaction Mechanics

This article delves into MySQL’s internal logging mechanisms—redo log, undo log, and binlog—explaining their structures, purposes, and interactions, and then explores transaction concepts such as dirty reads/writes, non‑repeatable reads, phantom reads, isolation levels, and the MVCC implementation that ensures data consistency.

Isolation LevelsMVCCMySQL
0 likes · 11 min read
Unlock MySQL’s Secrets: Redo Log, Undo Log, Binlog & Transaction Mechanics
Top Architect
Top Architect
Mar 5, 2022 · Databases

Why MySQL LIMIT with Large Offsets Is Slow and How to Optimize It

The article demonstrates that MySQL queries using LIMIT with a large offset cause heavy random I/O by scanning many rows, and shows how rewriting the query with a sub‑query that selects only primary keys dramatically reduces execution time from seconds to milliseconds.

LIMITMySQLbuffer pool
0 likes · 8 min read
Why MySQL LIMIT with Large Offsets Is Slow and How to Optimize It
Aikesheng Open Source Community
Aikesheng Open Source Community
Mar 5, 2022 · Databases

Weekly Community Tech Digest – MySQL Updates, Middleware Progress, and Community News

This weekly digest summarizes the latest MySQL 8.0 enhancements, replication delay case, user connection memory limits, Oracle‑to‑MySQL synchronization, new SQLE release, DBLE middleware developments, bug fixes, community Q&A, recruitment notices, and upcoming plans, providing concise technical insights for the open‑source community.

CommunityDatabasesMySQL
0 likes · 4 min read
Weekly Community Tech Digest – MySQL Updates, Middleware Progress, and Community News