A public account focused on workplace, career growth, team management, and self-improvement. The author is the writer of books including 'SpringBoot Technology Insider' and 'Drools 8 Rule Engine: Core Technology and Practice'.
This article explains MySQL’s native JSON data type introduced in 5.7, covering its binary storage format, built‑in functions, generated‑column indexing, performance advantages over string storage, and detailed CRUD examples with SQL syntax and practical code snippets.
This article explains what MySQL composite (multi‑column) indexes are, how they are built and ordered, the leftmost‑prefix rule that governs their usage, factors such as cardinality and selectivity for optimal design, and common scenarios where the index may become ineffective.
This article explains three frequent MySQL table‑design errors—choosing wrong data types and lengths, missing or redundant indexes, and improper storage structures—illustrating how they affect storage, query speed, and scalability, and offers practical guidelines to prevent them.
This article walks through the evolution from binary search trees to balanced trees, B‑trees and finally B+ trees, explaining how MySQL's InnoDB and MyISAM storage engines implement these structures to reduce disk I/O and boost query performance.
MySQL query optimization involves three distinct index processing stages—Index Key, Index Filter, and Table Filter—each handling different parts of condition matching and data retrieval, and mastering their roles and execution flow can dramatically improve query performance, especially with index condition pushdown.
This article explains the functional differences, performance considerations, and practical use cases of MySQL's SELECT DISTINCT and GROUP BY clauses, providing syntax details, example tables, query results, and a comparison table to help developers choose the right approach for deduplication or aggregation tasks.
This article explains why selecting only needed columns and ensuring deterministic ordering are essential for MySQL queries, then compares Offset/Limit pagination with cursor‑based pagination, detailing their syntax, advantages, drawbacks such as data drift and performance issues, and offers practical optimization tips.
This article explains how to verify whether MySQL uses an index with EXPLAIN, outlines eight common reasons why an index may be ignored—including better alternatives, low selectivity, stale statistics, full‑table scans, structural limitations, function usage, invisible indexes—and provides practical commands and hints to resolve each issue.
Index Condition Pushdown (ICP) in MySQL pushes filter conditions to the storage engine, dramatically cutting data transferred to the server layer, speeding up queries especially when using composite indexes, and requiring no changes to the original SQL statements.
This article explains the concept of table lookups in MySQL, shows how they arise from using secondary indexes, provides concrete query examples, and offers practical optimization techniques such as creating covering indexes, reducing selected columns, and analyzing execution plans to improve performance.
