BestHub
Sign in
Tagged articles
5000 articles
Page 38 of 50
Code Ape Tech Column
Code Ape Tech Column
Jun 4, 2021 · Databases

8 MySQL Performance Tricks to Slash Query Time from Seconds to Milliseconds

This article examines common MySQL performance pitfalls such as inefficient LIMIT offsets, implicit type conversions, suboptimal UPDATE/DELETE joins, mixed sorting, misuse of EXISTS, and limited condition pushdown, then demonstrates concrete rewrites—including range reduction and WITH clauses—that reduce execution times from seconds to milliseconds.

DatabaseMySQLPerformance Optimization
0 likes · 12 min read
8 MySQL Performance Tricks to Slash Query Time from Seconds to Milliseconds
Java Backend Technology
Java Backend Technology
Jun 3, 2021 · Databases

Why MySQL TIMESTAMP Is a Hidden Pitfall and How to Avoid It

This article explains MySQL's three time types, highlights the quirks and automatic defaults of TIMESTAMP—including the 2038 problem, high‑concurrency issues, and unexpected behavior in non‑strict mode—provides practical examples, and offers guidance on configuring server variables or switching to DATETIME for safer handling.

2038 problemDatabasesMySQL
0 likes · 11 min read
Why MySQL TIMESTAMP Is a Hidden Pitfall and How to Avoid It
Aikesheng Open Source Community
Aikesheng Open Source Community
Jun 2, 2021 · Databases

How MySQL Adjusts open_files_limit, max_connections, and table_open_cache When System Resources Are Insufficient

This article explains how MySQL, when run by a non‑root user with limited system resources, automatically adjusts the open_files_limit, max_connections, and table_open_cache parameters by calculating required file descriptors, comparing them to system limits, and correcting the configuration values accordingly.

Database TuningMySQLconfiguration
0 likes · 6 min read
How MySQL Adjusts open_files_limit, max_connections, and table_open_cache When System Resources Are Insufficient
IT Xianyu
IT Xianyu
Jun 1, 2021 · Databases

Improving MySQL Insert Performance with Multithreading, Prepared Statements, and Batch Transactions

The article explains why multithreaded inserts can be faster than single‑threaded ones in MySQL, breaks down the time spent on each step of an insert operation, and presents practical techniques such as using PreparedStatement, multi‑value INSERT statements, and transaction batching to dramatically reduce insertion time.

Batch TransactionDatabase OptimizationInsert Performance
0 likes · 6 min read
Improving MySQL Insert Performance with Multithreading, Prepared Statements, and Batch Transactions
MaGe Linux Operations
MaGe Linux Operations
May 31, 2021 · Databases

Why Misplaced Quotes in MySQL UPDATE Can Zero Out Your Data

A developer’s production fix using dozens of UPDATE statements unintentionally set a column to zero because of misplaced quotation marks, illustrating how MySQL’s implicit conversion and syntax quirks can turn seemingly correct SQL into disastrous data loss.

Data RecoveryDatabaseImplicit Conversion
0 likes · 6 min read
Why Misplaced Quotes in MySQL UPDATE Can Zero Out Your Data
Tencent Database Technology
Tencent Database Technology
May 31, 2021 · Databases

TXSQL Query Optimizer Framework: Transformation, Join Reorder, and Cost Model

This article introduces the TXSQL query optimizer built on MySQL 8.0.22, detailing its cascades‑style framework, transformation rewrite rules such as outer‑join elimination and subquery flattening, join‑order heuristics, cost‑model configuration, and execution strategies, providing a comprehensive overview of its design and enhancements.

Cost ModelDatabaseJoin Reorder
0 likes · 25 min read
TXSQL Query Optimizer Framework: Transformation, Join Reorder, and Cost Model
Top Architect
Top Architect
May 31, 2021 · Databases

How to Achieve Fast Queries: MySQL Index Optimization, Large‑Table Strategies, Elasticsearch Basics, and HBase Overview

This article explains common causes of slow MySQL queries, how proper indexing and lock handling can improve performance, introduces Elasticsearch’s inverted‑index advantages and suitable use cases, and outlines HBase’s column‑family storage model and row‑key design for large‑scale data.

Big DataDatabase OptimizationHBase
0 likes · 18 min read
How to Achieve Fast Queries: MySQL Index Optimization, Large‑Table Strategies, Elasticsearch Basics, and HBase Overview
Efficient Ops
Efficient Ops
May 30, 2021 · Databases

Unlock Real-Time MySQL Binlog Streaming with Canal, Maxwell, and DTS

This article explains how Canal, Maxwell, Databus, and Alibaba Cloud DTS capture and stream MySQL binlog changes in real time, detailing their architectures, processing steps, and key features such as filtering, routing, and high‑availability delivery.

CanalChange Data CaptureDTS
0 likes · 7 min read
Unlock Real-Time MySQL Binlog Streaming with Canal, Maxwell, and DTS
Selected Java Interview Questions
Selected Java Interview Questions
May 29, 2021 · Databases

Understanding Database Locks: Shared, Exclusive, Mutex, Pessimistic, Optimistic, Row, Table, and Page Locks, and Common Concurrency Issues

This article explains the concepts, usage scenarios, and differences of various database locks—including shared (S) lock, exclusive (X) lock, mutex, pessimistic and optimistic locks—as well as row‑level, table‑level, and page‑level locks, and it discusses common concurrency problems such as lost updates, non‑repeatable reads, dirty reads, and deadlocks.

Concurrency ControlDatabase LocksMySQL
0 likes · 8 min read
Understanding Database Locks: Shared, Exclusive, Mutex, Pessimistic, Optimistic, Row, Table, and Page Locks, and Common Concurrency Issues
Aikesheng Open Source Community
Aikesheng Open Source Community
May 28, 2021 · Databases

Comprehensive MySQL Inspection Checklist and Command Reference

This guide presents a detailed MySQL inspection checklist covering operating‑system metrics, critical MySQL parameters, status queries, replication health, high‑availability components, and useful SQL scripts, enabling DBAs to efficiently monitor performance, detect issues, and maintain reliable database services.

MySQLhigh availabilityinspection
0 likes · 11 min read
Comprehensive MySQL Inspection Checklist and Command Reference
Full-Stack Internet Architecture
Full-Stack Internet Architecture
May 27, 2021 · Databases

How to Locate and Analyze Slow SQL Queries in MySQL

This article explains how to identify slow MySQL queries using the slow‑query log and SHOW PROCESSLIST, configure logging parameters, and analyze query performance with EXPLAIN, including detailed explanations of key output fields and practical code examples.

Database PerformanceEXPLAINMySQL
0 likes · 13 min read
How to Locate and Analyze Slow SQL Queries in MySQL
Big Data Technology & Architecture
Big Data Technology & Architecture
May 27, 2021 · Databases

Database Selection and TiDB Implementation in NetEase Interactive Entertainment Billing Group

This article details the billing group's challenges with single‑node MySQL, the evaluation of alternative databases such as TiDB and CockroachDB, performance testing, migration strategies, operational best practices, and the final decision to adopt TiDB for scalable, high‑availability data services.

Data ArchitectureDistributed SQLMySQL
0 likes · 15 min read
Database Selection and TiDB Implementation in NetEase Interactive Entertainment Billing Group
IT Architects Alliance
IT Architects Alliance
May 26, 2021 · Databases

Understanding MySQL Slow Queries, Elasticsearch, and HBase: Causes and Practical Solutions

This article explains why MySQL queries become slow, how indexes work and fail, the impact of MDL locks, large‑table challenges, sharding and read‑write splitting strategies, then introduces Elasticsearch’s search capabilities and HBase’s column‑family storage, offering practical guidance for each technology.

Database PerformanceElasticsearchHBase
0 likes · 17 min read
Understanding MySQL Slow Queries, Elasticsearch, and HBase: Causes and Practical Solutions
Top Architect
Top Architect
May 26, 2021 · Databases

How MySQL InnoDB Implements ACID: Locks, MVCC, and Logging

This article explains how MySQL InnoDB guarantees the ACID properties—Atomicity, Consistency, Isolation, and Durability—through its lock mechanisms, multi‑version concurrency control, undo/redo logs, buffer pool management, and the interaction between binlog and redo log.

ACIDInnoDBLock
0 likes · 12 min read
How MySQL InnoDB Implements ACID: Locks, MVCC, and Logging
Aikesheng Open Source Community
Aikesheng Open Source Community
May 26, 2021 · Databases

Practical Guide to Using MySQL Full-Text Indexes

This article explains MySQL full‑text indexing, compares its syntax with ordinary SQL, demonstrates how to create and query a full‑text index using natural language, boolean, and query‑expansion modes, and shows performance differences through execution‑plan analysis and relevance ranking.

Boolean ModeFull-Text IndexMySQL
0 likes · 9 min read
Practical Guide to Using MySQL Full-Text Indexes
macrozheng
macrozheng
May 26, 2021 · Backend Development

How to Ensure Cache‑Database Consistency: Patterns, Pitfalls, and Solutions

This article explains the concepts of strong, weak, and eventual consistency, introduces three classic cache patterns (Cache‑Aside, Read‑Through/Write‑Through, Write‑Behind), and discusses practical strategies such as delayed double‑delete, retry mechanisms, and binlog‑based asynchronous eviction to keep Redis and MySQL data in sync.

CacheConsistencyMySQL
0 likes · 11 min read
How to Ensure Cache‑Database Consistency: Patterns, Pitfalls, and Solutions
dbaplus Community
dbaplus Community
May 25, 2021 · Databases

How Migrating 40 B Records from MySQL to MongoDB Cut Costs and Boost Performance

A large‑scale IoT service migrated 40 billion rows from MySQL to MongoDB, solving capacity, cost, and data‑skew issues while achieving higher performance, lower storage consumption, and a flexible, highly available architecture, with detailed resource evaluation, migration steps, optimization tactics, and cost‑benefit analysis.

Cost SavingsMongoDBMySQL
0 likes · 21 min read
How Migrating 40 B Records from MySQL to MongoDB Cut Costs and Boost Performance
Top Architect
Top Architect
May 25, 2021 · Databases

Why Misplaced Quotes in MySQL UPDATE Cause Columns to Be Set to Zero

The article explains how incorrectly placed double‑quotation marks in MySQL UPDATE statements change the intended string assignment into a boolean comparison that evaluates to 0, leading to all affected rows being updated with zero values, and shows how to detect and avoid this pitfall.

Data RecoveryDatabase DebuggingMySQL
0 likes · 6 min read
Why Misplaced Quotes in MySQL UPDATE Cause Columns to Be Set to Zero
Programmer DD
Programmer DD
May 25, 2021 · Databases

Boost MySQL Insert Speed with Multithreading, Prepared Statements, and Batch Techniques

This article explains why multithreaded inserts into MySQL can outperform single‑threaded writes, breaks down the time spent on connection, parsing and insertion, and presents practical techniques such as prepared statements, batch processing, multi‑value inserts, and transaction batching that together can reduce insert time to seconds for massive data sets.

Batch InsertMySQLPrepared Statements
0 likes · 6 min read
Boost MySQL Insert Speed with Multithreading, Prepared Statements, and Batch Techniques
ITPUB
ITPUB
May 24, 2021 · Databases

Master MySQL Performance: From Single‑Table Tuning to Horizontal Sharding

This comprehensive guide covers MySQL single‑table optimization, field and index design, query best practices, engine choices, system parameters, hardware scaling, read/write splitting, caching strategies, partitioning, vertical and horizontal sharding, client vs. proxy architectures, and alternative databases, providing actionable steps for high‑performance data handling.

MySQLPartitioning
0 likes · 26 min read
Master MySQL Performance: From Single‑Table Tuning to Horizontal Sharding
Programmer DD
Programmer DD
May 22, 2021 · Databases

Master MySQL Indexes: B+Tree Deep Dive & Optimization Tips

This article explains MySQL's B‑Tree index fundamentals, compares MyISAM and InnoDB implementations, outlines how B+Tree structures work, and provides practical guidelines for designing effective indexes and tuning MySQL configuration to boost query performance.

B+TreeDatabase OptimizationInnoDB
0 likes · 27 min read
Master MySQL Indexes: B+Tree Deep Dive & Optimization Tips
Code Ape Tech Column
Code Ape Tech Column
May 21, 2021 · Databases

Why Your MySQL Queries Are Slow and How ElasticSearch & HBase Can Help

This article analyzes common causes of slow MySQL queries such as index misuse, MDL locks, and large‑table bottlenecks, then presents practical solutions like proper indexing, sharding, read/write splitting, and evaluates when to complement MySQL with ElasticSearch or HBase for better performance.

Database PerformanceElasticsearchHBase
0 likes · 19 min read
Why Your MySQL Queries Are Slow and How ElasticSearch & HBase Can Help
Top Architect
Top Architect
May 19, 2021 · Backend Development

Full‑Stack Food Delivery System (Spring Boot + Vue) – Setup Guide and Code

This article provides a complete tutorial for building a food‑delivery system with a mobile app, backend management, and API, detailing the technology stack, module structure, database setup, code snippets, and step‑by‑step instructions to launch both the admin and mobile front‑ends.

MongoDBMySQLSpring Boot
0 likes · 4 min read
Full‑Stack Food Delivery System (Spring Boot + Vue) – Setup Guide and Code
Aikesheng Open Source Community
Aikesheng Open Source Community
May 17, 2021 · Databases

Using MySQL Federated Storage Engine for Remote Table Access

This article explains how to enable and configure MySQL's Federated storage engine to access remote tables without data synchronization, covering engine architecture, supported features, environment setup, server configuration, table creation, data manipulation, and binlog verification, along with practical code examples.

Federated EngineMySQLRemote Table Access
0 likes · 10 min read
Using MySQL Federated Storage Engine for Remote Table Access
Top Architect
Top Architect
May 16, 2021 · Databases

Understanding LEFT JOIN, Common Pitfalls, and Practical Solutions in SQL

This article explains how LEFT JOIN works in SQL, illustrates why duplicate rows can appear when joining tables, and provides practical techniques such as DISTINCT, GROUP BY, MAX, and IN subqueries to ensure a one‑to‑one result set while also covering RIGHT JOIN, INNER JOIN, update and delete operations, and the concept of Cartesian products.

DatabaseJoinLEFT JOIN
0 likes · 14 min read
Understanding LEFT JOIN, Common Pitfalls, and Practical Solutions in SQL
Aikesheng Open Source Community
Aikesheng Open Source Community
May 14, 2021 · Databases

Understanding and Managing MySQL User Password Expiration

This article explains the MySQL password expiration mechanism, introduces the relevant columns in the mysql.user table, demonstrates how to set passwords to never expire, expire after a specific interval, or expire immediately, and shows how to configure the global default_password_lifetime variable.

MySQLUser Managementdatabase security
0 likes · 10 min read
Understanding and Managing MySQL User Password Expiration
Tech Musings
Tech Musings
May 14, 2021 · Databases

How to Diagnose and Fix MySQL Deadlocks Using Locks and Indexes

This article explains why MySQL deadlocks occur in a test module, details table and row lock mechanisms, reproduces the deadlock scenario with two sessions, analyzes lock logs, and provides a solution by adding a composite index to prevent the conflict.

InnoDBLockMySQL
0 likes · 19 min read
How to Diagnose and Fix MySQL Deadlocks Using Locks and Indexes
Programmer DD
Programmer DD
May 13, 2021 · Databases

How an Unexpected Primary Key Update Triggered a MySQL Auto‑Increment Bug

A developer encountered a duplicate‑key error caused by an unexpected primary‑key update that overflowed the signed INT limit, exposing a hidden MySQL auto‑increment bug that persisted across normal inserts until a later DDL operation triggered failures, a problem present in Percona 5.6/5.7 but fixed in MySQL 8.0.

DatabaseMySQLPercona
0 likes · 5 min read
How an Unexpected Primary Key Update Triggered a MySQL Auto‑Increment Bug
Aikesheng Open Source Community
Aikesheng Open Source Community
May 12, 2021 · Databases

Understanding MySQL Full-Text Indexes: Structure, Auxiliary Tables, Buffer Pool, and Transaction Handling

This article explains MySQL full-text (inverted) indexes, their underlying auxiliary tables, buffer pool configuration, document ID handling, and the special transaction semantics required for inserting and querying indexed data, providing practical SQL examples and performance considerations.

Full-Text IndexInnoDBMySQL
0 likes · 9 min read
Understanding MySQL Full-Text Indexes: Structure, Auxiliary Tables, Buffer Pool, and Transaction Handling
Java Architect Essentials
Java Architect Essentials
May 11, 2021 · Databases

Comprehensive MySQL Query Optimization and Best Practices

This article presents an extensive collection of MySQL performance‑tuning techniques, covering index design, query rewriting, use of UNION and EXISTS, temporary tables, NOLOCK hints, join limits, backup strategies, storage engine choices, data‑type selection, and other practical tips to improve query efficiency and overall database reliability.

Database DesignIndexesMySQL
0 likes · 18 min read
Comprehensive MySQL Query Optimization and Best Practices
Code Ape Tech Column
Code Ape Tech Column
May 11, 2021 · Databases

How to Speed Up MySQL LIMIT Pagination for Millions of Rows

This article analyzes six MySQL pagination techniques, presents benchmark results showing how query time grows with offset, and offers practical index‑based optimizations—including covering indexes, subqueries, and composite index design—to make LIMIT pagination fast even on very large tables.

LIMITMySQLindex
0 likes · 12 min read
How to Speed Up MySQL LIMIT Pagination for Millions of Rows
Full-Stack Internet Architecture
Full-Stack Internet Architecture
May 10, 2021 · Databases

MySQL Index Design Principles and Common Index Pitfalls

This article explains MySQL index fundamentals, covering primary and unique indexes, best practices for choosing indexed columns, handling functions and large fields, avoiding excessive or frequently updated indexes, and enumerates typical scenarios where indexes become ineffective.

Database OptimizationMySQLindex design
0 likes · 12 min read
MySQL Index Design Principles and Common Index Pitfalls
Java Interview Crash Guide
Java Interview Crash Guide
May 10, 2021 · Databases

Master MySQL Indexes: From B‑Tree to Hash and When to Use Them

This article explains MySQL indexing fundamentals, covering index types, underlying data structures such as B‑Tree and Hash, the differences between clustered and non‑clustered indexes, practical performance tips, and advanced features like adaptive hash indexes and index condition pushdown.

B-TreeDatabase OptimizationHash Index
0 likes · 26 min read
Master MySQL Indexes: From B‑Tree to Hash and When to Use Them
Java Architect Essentials
Java Architect Essentials
May 9, 2021 · Backend Development

Full‑Stack Food Delivery System Based on Spring Boot and Vue.js

This article presents a complete food‑delivery system with mobile, admin, and API modules, detailing its Spring Boot and Vue.js technology stack, module structure, database setup, quick‑start commands, and how to run both the management console and mobile client for learning purposes.

MongoDBMySQLSpring Boot
0 likes · 4 min read
Full‑Stack Food Delivery System Based on Spring Boot and Vue.js
dbaplus Community
dbaplus Community
May 9, 2021 · Databases

Why MySQL Index Merge Triggers Deadlocks and How to Fix Them

This article examines a real‑world MySQL deadlock caused by the Index Merge optimizer, explains InnoDB's lock‑on‑index mechanism, walks through the deadlock logs, and presents four practical solutions including force‑index hints, disabling Index Merge, creating a composite index, and a two‑step update approach.

InnoDBMySQLOptimization
0 likes · 15 min read
Why MySQL Index Merge Triggers Deadlocks and How to Fix Them
Architect's Alchemy Furnace
Architect's Alchemy Furnace
May 9, 2021 · Databases

Master MySQL Transaction Isolation: From Dirty Reads to Phantom Reads with Live Examples

This article explains MySQL InnoDB’s four transaction isolation levels—Read Uncommitted, Read Committed, Repeatable Read, and Serializable—detailing the phenomena of dirty reads, non‑repeatable reads, and phantom reads, and provides step‑by‑step SQL demonstrations to illustrate each level’s behavior and practical implications.

InnoDBMySQLtransaction isolation
0 likes · 13 min read
Master MySQL Transaction Isolation: From Dirty Reads to Phantom Reads with Live Examples
Architecture Digest
Architecture Digest
May 9, 2021 · Databases

Optimizing MySQL Pagination with LIMIT: Methods, Experiments, and Index Strategies

This article examines the performance drawbacks of MySQL's LIMIT pagination on large tables, presents six practical query methods—including direct LIMIT, primary‑key indexing, index‑based ordering, prepared statements, covering indexes, and sub‑query/join techniques—provides extensive benchmark results, and offers concrete indexing recommendations to achieve fast, stable pagination even with millions of rows.

LIMITLarge DataMySQL
0 likes · 12 min read
Optimizing MySQL Pagination with LIMIT: Methods, Experiments, and Index Strategies
Selected Java Interview Questions
Selected Java Interview Questions
May 8, 2021 · Databases

Cache Consistency Strategies for MySQL Using Redis

This article examines why caching is needed for MySQL, discusses consistency challenges, and compares four Redis‑based solutions—including expiration, synchronous updates, Kafka‑mediated async updates, and binlog replication—offering guidance on selecting the appropriate approach based on latency and reliability requirements.

BackendCache ConsistencyDatabase Caching
0 likes · 9 min read
Cache Consistency Strategies for MySQL Using Redis
Aikesheng Open Source Community
Aikesheng Open Source Community
May 8, 2021 · Databases

Analysis of MHA Master Crash Failover Process and Source Code

This article examines the MHA open‑source MySQL high‑availability solution, detailing the master‑crash failover workflow, source‑code analysis, configuration checks, binlog handling, new‑master selection, and slave recovery, and provides a concise step‑by‑step checklist for practitioners.

MHAMySQLmaster crash
0 likes · 9 min read
Analysis of MHA Master Crash Failover Process and Source Code
Liangxu Linux
Liangxu Linux
May 7, 2021 · Databases

Why a Missing Index Parameter Nearly Crashed Our Production Database

A production MySQL server suffered CPU overload and half‑hour alerts because a high‑volume query skipped a crucial composite index field, leading to full‑table scans; the post explains the root cause, the temporary index fix, the left‑most index principle, code validation oversights, and lessons learned.

Composite IndexDatabase DebuggingJava Validation
0 likes · 8 min read
Why a Missing Index Parameter Nearly Crashed Our Production Database
Top Architect
Top Architect
May 7, 2021 · Backend Development

Common Spring Transaction Failure Scenarios and Their Solutions

This article explains typical reasons why @Transactional may not work in Spring applications—such as non‑public methods, beans not managed by the container, internal method calls, non‑RuntimeException throws, swallowed exceptions, wrong propagation settings, and unsupported MySQL storage engines—and provides concrete code examples and fixes for each case.

AOPException HandlingJava
0 likes · 11 min read
Common Spring Transaction Failure Scenarios and Their Solutions
Full-Stack Internet Architecture
Full-Stack Internet Architecture
May 7, 2021 · Databases

Resolving MySQL wait_timeout Issues with Druid Connection Pool Configuration

This article analyzes the root causes of MySQL 'wait_timeout' related exceptions in a backend system using MHA read/write separation, explains how oversized connection pool timeout settings and MySQL wait_timeout lead to idle connections being closed, and provides practical Druid configuration adjustments and alternative solutions to eliminate the errors.

BackendConnection PoolDruid
0 likes · 7 min read
Resolving MySQL wait_timeout Issues with Druid Connection Pool Configuration
Programmer DD
Programmer DD
May 7, 2021 · Databases

Boost MySQL Pagination Performance: Real-World Tests and Optimizations

This article examines why full-table scans become slow on large MySQL tables, demonstrates various pagination techniques—including simple LIMIT, sub‑query, ID‑range, and IN‑clause methods—provides benchmark results for different record counts and offsets, and offers practical tips to dramatically speed up paginated queries.

Database PerformanceMySQLpagination
0 likes · 10 min read
Boost MySQL Pagination Performance: Real-World Tests and Optimizations
Aikesheng Open Source Community
Aikesheng Open Source Community
May 6, 2021 · Databases

Designing an Online DDL Work Order System for MySQL

This article examines the pain points of MySQL DDL operations for large tables, analyzes the limitations of online DDL, compares tools like gh‑ost and pt‑osc, and outlines a comprehensive workflow and implementation details for an automated online DDL work‑order system.

DDLDatabase AdministrationMySQL
0 likes · 9 min read
Designing an Online DDL Work Order System for MySQL
Programmer DD
Programmer DD
May 6, 2021 · Databases

Unlock Real-Time MySQL Binlog Streaming with Canal, Maxwell, and DTS

This article explains how Canal simulates a MySQL slave to capture binary logs, outlines the parsing workflow, compares Maxwell’s JSON output approach, introduces LinkedIn’s low‑latency Databus, and reviews Alibaba Cloud’s Data Transmission Service (DTS) as a managed change‑data‑capture solution for MySQL.

CanalChange Data CaptureDTS
0 likes · 7 min read
Unlock Real-Time MySQL Binlog Streaming with Canal, Maxwell, and DTS
Full-Stack Internet Architecture
Full-Stack Internet Architecture
May 6, 2021 · Databases

Understanding MySQL Query Execution Process and Architecture

This article explains MySQL’s internal architecture—including server and storage engine layers—and walks through each stage of query processing from connection and authentication, through query cache, parsing, optimization, and execution, illustrated with a concrete SELECT example.

Database ArchitectureMySQLOptimization
0 likes · 9 min read
Understanding MySQL Query Execution Process and Architecture
Selected Java Interview Questions
Selected Java Interview Questions
May 5, 2021 · Databases

Understanding MySQL Temporary Tables, MEM_ROOT Memory Management, and Query Optimization

This article explains how MySQL processes a GROUP BY query on a user view table, detailing the creation and use of memory and disk temporary tables, the MEM_ROOT allocation mechanism, the execution stages revealed by EXPLAIN, and how adding a composite index can eliminate temporary tables and filesort for better performance.

GROUP BYMySQLindex
0 likes · 13 min read
Understanding MySQL Temporary Tables, MEM_ROOT Memory Management, and Query Optimization
ITPUB
ITPUB
Apr 30, 2021 · Databases

How Index Pushdown Cuts Unnecessary Table Lookups in MySQL

This article explains MySQL's table‑lookup (回表) process, the difference between primary‑key and secondary indexes, and how the index‑pushdown feature introduced in MySQL 5.6 reduces unnecessary lookups by filtering rows directly in the index.

Database OptimizationIndex PushdownInnoDB
0 likes · 7 min read
How Index Pushdown Cuts Unnecessary Table Lookups in MySQL
Open Source Linux
Open Source Linux
Apr 30, 2021 · Databases

Master MySQL Query Optimization: Architecture, Caching, and Index Strategies

This article explains MySQL's logical architecture, query execution flow, client‑server protocol, query cache behavior, parsing and optimization stages, cost‑based optimizer, execution engine, and provides practical performance‑tuning advice such as schema design, data‑type choices, index creation, B‑Tree fundamentals, covering indexes, and handling COUNT, JOIN, LIMIT, and UNION queries.

B+TreeMySQLindexing
0 likes · 35 min read
Master MySQL Query Optimization: Architecture, Caching, and Index Strategies
Programmer DD
Programmer DD
Apr 29, 2021 · Databases

Loading 2 Billion Rows into MySQL Fast with TokuDB – 570k Rows/s

This article details a real‑world test of loading over 200 million rows into MySQL using XeLabs TokuDB, showing configuration tweaks, bulk‑loader commands, performance metrics (≈570 k rows per second), file‑size reductions, and comparisons with InnoDB under a 8‑core, 8 GB RAM, 500 GB SSD environment.

Database OptimizationLarge DataMySQL
0 likes · 6 min read
Loading 2 Billion Rows into MySQL Fast with TokuDB – 570k Rows/s
Laravel Tech Community
Laravel Tech Community
Apr 28, 2021 · Databases

Understanding and Analyzing MySQL Execution Plans

This article explains the three‑layer architecture of MySQL, how an execution plan is generated and interpreted, and provides a detailed walkthrough of the columns displayed by the EXPLAIN statement to help developers optimize their SQL queries.

DatabaseEXPLAINMySQL
0 likes · 8 min read
Understanding and Analyzing MySQL Execution Plans
Aikesheng Open Source Community
Aikesheng Open Source Community
Apr 28, 2021 · Databases

Using pt-archiver for Batch Deletion of Large MySQL Tables

The article explains why massive MySQL tables need periodic cleanup, compares risky drop/truncate approaches, and provides a step‑by‑step guide to safely delete historical data in batches using pt‑archiver with proper parameters, session handling, and post‑deletion maintenance.

Batch DeletionMySQLdata cleanup
0 likes · 7 min read
Using pt-archiver for Batch Deletion of Large MySQL Tables
Aikesheng Open Source Community
Aikesheng Open Source Community
Apr 27, 2021 · Databases

Efficient Data Import with MySQL Shell import_table and Its Performance Compared to LOAD DATA

This article introduces MySQL Shell’s import_table utility, demonstrates its core features and configuration options such as parallel import, speed throttling, and custom chunk sizes, provides side‑by‑side command examples with LOAD DATA, and shows that import_table can achieve more than double the import speed in practical tests.

MySQLMySQL Shelldata import
0 likes · 10 min read
Efficient Data Import with MySQL Shell import_table and Its Performance Compared to LOAD DATA
Liangxu Linux
Liangxu Linux
Apr 26, 2021 · Databases

Master MySQL Internals: Query Cache, Indexes, Optimizer & Transaction Logs

This article explains MySQL's query cache settings, clustered and non‑clustered index design, optimizer cost calculations, how to interpret EXPLAIN output, and the roles of redo/undo logs and transaction isolation levels, providing practical examples and performance tips.

IndexesIsolation LevelsMySQL
0 likes · 23 min read
Master MySQL Internals: Query Cache, Indexes, Optimizer & Transaction Logs
Programmer DD
Programmer DD
Apr 26, 2021 · Databases

Why MySQL Subqueries Slow Down and How to Optimize Them

This article explains why MySQL subqueries often cause performance bottlenecks, describes the underlying execution mechanism, and presents practical optimization strategies—including semi‑join, materialization, and EXISTS—plus a real‑world case study showing how to rewrite a slow query into a fast join.

MySQLOptimizationperformance
0 likes · 7 min read
Why MySQL Subqueries Slow Down and How to Optimize Them
Open Source Linux
Open Source Linux
Apr 26, 2021 · Databases

How to Resolve MySQL Too Many Connections Errors by Raising max_connections

This guide explains why MySQL reports "too many connections", shows how to check the current max_connections setting, compares default limits across MySQL versions, and provides four practical methods—including editing my.cnf, using SQL commands, modifying source code, and tweaking mysqld_safe—to increase the connection limit safely.

DatabaseMySQLTroubleshooting
0 likes · 4 min read
How to Resolve MySQL Too Many Connections Errors by Raising max_connections
dbaplus Community
dbaplus Community
Apr 25, 2021 · Information Security

Essential MySQL Security Practices: From Storage to Data Encryption

Learn comprehensive MySQL security strategies covering storage RAID configurations, network whitelisting, OS hardening, account management, privilege restrictions, audit logging, regular backups, data encryption, and disaster recovery to protect enterprise data against breaches and ensure stable, high‑performance operations.

MySQLaccess controlbackup
0 likes · 13 min read
Essential MySQL Security Practices: From Storage to Data Encryption
Aikesheng Open Source Community
Aikesheng Open Source Community
Apr 25, 2021 · Databases

Remote MySQL Backup with mysqlbackup and Percona XtraBackup

This article demonstrates how to perform remote physical backups of MySQL databases using the mysqlbackup and Percona XtraBackup tools, covering test objectives, environment setup, step‑by‑step streaming commands, restoration procedures, replication configuration, and a comparison of the two approaches.

DatabaseMySQLSSH
0 likes · 13 min read
Remote MySQL Backup with mysqlbackup and Percona XtraBackup
Architecture Digest
Architecture Digest
Apr 25, 2021 · Databases

MySQL Lock Types and Deadlock Analysis

This article explains MySQL's lock granularity (table, row, page), the different lock modes such as next‑key, gap and record locks, illustrates common deadlock scenarios with detailed SQL examples, and discusses InnoDB's deadlock prevention strategies and best‑practice solutions.

InnoDBLockMySQL
0 likes · 14 min read
MySQL Lock Types and Deadlock Analysis
Programmer DD
Programmer DD
Apr 25, 2021 · Databases

Mastering MySQL: Proven Steps to Optimize Slow Queries

This article outlines a systematic approach to identifying and fixing inefficient MySQL queries, covering slow‑query detection, EXPLAIN analysis, profiling, optimizer tracing, and practical case studies that demonstrate index tuning, query rewriting, and handling large‑scale pagination and complex conditions.

Index TuningMySQLOptimizer_trace
0 likes · 10 min read
Mastering MySQL: Proven Steps to Optimize Slow Queries
Programmer DD
Programmer DD
Apr 24, 2021 · Databases

50 Essential SQL Performance Optimization Tips Every Developer Should Know

This article compiles 50 practical SQL performance optimization techniques—from indexing strategies and query rewriting to avoiding full table scans, using temporary tables, proper data types, and efficient backup methods—helping developers write faster, more scalable database queries.

MySQLindexingsql
0 likes · 22 min read
50 Essential SQL Performance Optimization Tips Every Developer Should Know
Liangxu Linux
Liangxu Linux
Apr 24, 2021 · Databases

Understanding MySQL Lock Types and How to Prevent Deadlocks

This article explains MySQL's table, row, and page lock levels, describes next‑key, gap, and record locks, illustrates common deadlock scenarios with detailed SQL examples, and outlines InnoDB's lock acquisition and deadlock‑prevention strategies to help developers avoid concurrency issues.

ConcurrencyDatabaseInnoDB
0 likes · 14 min read
Understanding MySQL Lock Types and How to Prevent Deadlocks
Selected Java Interview Questions
Selected Java Interview Questions
Apr 22, 2021 · Databases

Key MySQL Concepts: DROP/DELETE/TRUNCATE, Data Types, Storage Engines, Transactions, Views, Procedures, Indexes, UNION, and SQL Language Parts

This article explains the differences between DROP, DELETE, and TRUNCATE, recommends appropriate MySQL data types for monetary values, compares MyISAM and InnoDB storage engines, outlines transaction ACID properties, describes views, stored procedures versus triggers, indexes, UNION vs UNION ALL, and lists the main SQL language categories.

DatabaseIndexesMySQL
0 likes · 10 min read
Key MySQL Concepts: DROP/DELETE/TRUNCATE, Data Types, Storage Engines, Transactions, Views, Procedures, Indexes, UNION, and SQL Language Parts