BestHub
Sign in
Tagged articles

MySQL

5000 articles · Page 41 of 50
Architect
Architect
Jun 6, 2021 · Databases

Optimizing MySQL Pagination for Large Datasets: Analysis and Solutions

An engineer describes a production MySQL pagination issue where large offset queries caused severe slowdown, analyzes the root cause, demonstrates data simulation scripts, and presents three optimization strategies—including index covering with subqueries, redefining start positions, and applying offset limits—to improve query performance on massive tables.

MySQLPerformance OptimizationSQL
0 likes · 13 min read
Optimizing MySQL Pagination for Large Datasets: Analysis and Solutions
Full-Stack Internet Architecture
Full-Stack Internet Architecture
Jun 5, 2021 · Databases

How Many Rows Can a MySQL InnoDB B+Tree Store?

This article explains InnoDB's storage hierarchy (sector, block, page), calculates how many rows fit in a 16KB page, shows how B+‑tree height and pointer counts determine total record capacity, and demonstrates the I/O cost of primary and secondary index lookups using practical MySQL commands.

B+TreeDatabase IndexingInnoDB
0 likes · 8 min read
How Many Rows Can a MySQL InnoDB B+Tree Store?
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.

MySQLPerformance OptimizationQuery Tuning
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 problemMySQLSQL
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.

ConfigurationMySQLdatabase tuning
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 TransactionInsert PerformanceMySQL
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.

MySQLSQLUpdate
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 ModelJoin ReorderMySQL
0 likes · 25 min read
TXSQL Query Optimizer Framework: Transformation, Join Reorder, and Cost Model
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.

Database LocksMySQLOptimistic Lock
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.

InspectionMySQLPerformance
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.

EXPLAINMySQLdatabase performance
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.

Cloud NativeDistributed 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.

ElasticsearchHBaseMySQL
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.

MySQLcacheconsistency
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.

Database DebuggingMySQLQuote Error
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+ TreeInnoDBMyISAM
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.

ElasticsearchHBaseIndex Optimization
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.

Food DeliveryFull-StackMongoDB
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.

Database ReplicationFederated EngineMySQL
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.

JOINLEFT JOINMySQL
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.

Database SecurityMySQLSQL
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.

MySQLPerconaauto-increment
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.

Database IndexingFull-Text IndexInnoDB
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.

MySQLSQL optimizationdatabase design
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.

LIMITMySQLPerformance
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.

MySQLPerformanceSQL
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-TreeMySQLdatabase optimization
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.

Food DeliveryFull-StackMongoDB
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.

BackendDatabase CachingMySQL
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.

AOPJavaMySQL
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.

BackendDruidJDBC
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.

MySQLSQLdatabase performance
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.

DDLMySQLOnlineDDL
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.

CanalDTSDatabus
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 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.

Index PushdownInnoDBMySQL
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+ TreeMySQLPerformance
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.

Large DataMySQLTokuDB
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.

EXPLAINMySQLSQL
0 likes · 8 min read
Understanding and Analyzing MySQL Execution Plans
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
Qingyun Technology Community
Qingyun Technology Community
Apr 26, 2021 · Databases

Sync MySQL to ClickHouse Using QingCloud MySQL Plus & MaterializeMySQL

This article explains how to break the barrier between heterogeneous databases by synchronizing MySQL to ClickHouse using QingCloud MySQL Plus and the MaterializeMySQL engine, covering MySQL replication history, architecture details, HTAP scenarios, and providing practical code examples for setup and operation.

ClickHouseDatabase ReplicationHTAP
0 likes · 13 min read
Sync MySQL to ClickHouse Using QingCloud MySQL Plus & MaterializeMySQL
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.

ConfigurationMySQLTroubleshooting
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.

Access ControlDatabase SecurityMySQL
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.

MySQLSSHbackup
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.

MySQLProfilingSQL optimization
0 likes · 10 min read
Mastering MySQL: Proven Steps to Optimize Slow Queries