This article examines how MySQL InnoDB decides whether to modify index blocks during UPDATE statements, walks through the internal mysql_update workflow, shows debugging with GDB, explains three test scenarios, and validates the behavior by inspecting block LSNs with the innblock tool.
This article introduces core relational database concepts, covering tables, data models and ER diagrams, the fundamentals of SQL, and the first three normal forms, providing clear explanations and examples to build a solid foundation for further database study.
Using an unsigned INT to store IPv4 addresses in MySQL saves space and enables efficient range queries, while strings are larger and slower; the article explains these advantages, outlines conversion functions INET_ATON/INET_NTOA, shows equivalent handling for IPv6, and provides Java utilities for bidirectional conversion.
This article explains why using MyBatis foreach for bulk inserts can cause severe performance degradation, analyzes the underlying cost of large prepared statements, and demonstrates how switching to ExecutorType.BATCH or limiting each INSERT to 20‑50 rows dramatically improves insertion speed.
This article examines a puzzling MySQL case‑insensitive collation issue where a user‑defined brand "yoyo" cannot be saved because the database returns an existing uppercase "YOYO" entry, explains the underlying COLLATE settings, and proposes practical solutions to prevent duplicate brand data.
This article explains how MySQL 8.0.32 InnoDB handles INSERT … ON DUPLICATE KEY UPDATE under REPEATABLE‑READ and READ‑COMMITTED isolation levels, showing the preparation steps, lock acquisition process, and why the same exclusive record lock is obtained in both cases.
This article explains how to replace Oracle's XMLAGG with OceanBase's WM_CONCAT, analyzes why the result order is lost in OceanBase 3.x, demonstrates reproduction steps, shows that adding a NO_REWRITE hint restores ordering, and provides the final SQL solution and notes for OceanBase 4.x.
This article examines how uncontrolled growth of SQL code leads to performance and maintainability issues, then demonstrates a step‑by‑step refactoring of a complex routing‑system query—formatting, layer decomposition, merging, condition push‑down, join optimization, and testing—to achieve clearer, faster, and more reliable database operations.
This article provides a comprehensive walkthrough of Doris's SQL execution pipeline, covering the query optimizer's parsing, rewriting, and plan generation, the scheduler's fragment distribution, and the executor's fragment processing, including code examples of expression rewrite rules, join strategies, and data flow between FE and BE nodes.
This article introduces Easy-Query, a Java ORM that offers strong‑typed OLTP and OLAP query capabilities, demonstrates how to define entity classes with many‑to‑many and one‑to‑one relationships, and provides multiple code examples for complex queries, DTO generation, and automatic inclusion of related data.
This guide presents essential MySQL configuration and monitoring queries—covering connection limits, Binlog/GTID status, InnoDB settings—plus a one‑click script that consolidates these checks, enabling quick health assessments and more efficient routine inspections of MySQL servers.
This article walks through nine practical techniques—default filters, smaller page sizes, fewer joins, index tuning, straight_join, data archiving, efficient count(*), ClickHouse offloading, and read/write splitting—to dramatically improve the performance of pagination APIs handling millions of product records.
This article explains how MySQL InnoDB deadlocks occur during order‑record idempotency checks, demonstrates step‑by‑step reproduction with SQL scripts, analyzes the lock types involved, and provides practical strategies to avoid and resolve such deadlocks in production systems.
This article presents nine practical techniques—including default filters, smaller page sizes, reduced joins, index tuning, straight_join usage, data archiving, count(*) optimization, ClickHouse offloading, and read/write splitting—to dramatically improve the speed and scalability of pagination query interfaces.
The article explains how to use Flink SQL’s temporary table join to enrich a real‑time traffic‑log stream with versioned tag data, detailing the required DDL, the time‑versioned join syntax, and essential watermark and idle‑timeout settings that prevent stalls and boundary‑delay issues.
The article outlines the growing difficulties of managing diverse databases in cloud-native environments, introduces Apache ShardingSphere as a comprehensive open‑source solution with three core capabilities—connectivity, enhancement, and pluggability—and guides readers through a three‑step learning path from fundamentals to deployment and testing.
This article explains foreign key concepts in relational databases, showing how to create, modify, and drop foreign key constraints with SQL code, provides hands‑on exercises, discusses the pros and cons of using foreign keys, and outlines when they are appropriate or should be avoided.
This article explains how to use PHP's mysqli_query function to connect to a MySQL database, execute SELECT queries, handle result sets, and perform other operations such as INSERT, UPDATE, and DELETE, including a complete example code snippet and best practices for error handling.
This article explains the purpose and proper use of the SQL HAVING clause, contrasts it with WHERE, and provides multiple practical examples—including counting groups, detecting missing IDs, calculating mode and median, and filtering fully‑submitted records—while highlighting common pitfalls and performance considerations.
MySQL Innovation Edition 9.1.0, released on October 15 2024, introduces atomic DDL operations, enhanced audit and firewall handling, numerous compiler and SQL function fixes, JavaScript stored‑procedure improvements, new vector support, updated keyring security, pluggable authentication fixes, expanded GROUP REPLICATION logging, and several EXPLAIN and performance‑schema enhancements.
This article explains DataFusion, a Rust‑written, Arrow‑based query engine that offers high performance, extensibility, and seamless integration with various data sources, detailing its architecture, execution model, Rust advantages, and practical usage examples for building modern data‑warehouse solutions.
This article explains MySQL's logical architecture, detailing the four main layers—connection, core service, storage engine, and data storage—while providing code examples and insights into each component's role in the widely used open‑source relational database.
Updating billions of rows in a MySQL table can overwhelm binlog replication and cause deep‑pagination inefficiencies, so this article explains the pitfalls of direct UPDATE, explores limit‑based and IN‑based approaches, and presents a production‑ready batch update strategy using NO_CACHE and forced primary‑key indexing.
This article breaks down a sophisticated SQL script used to export manually added contact data, explaining the involved tables, the construction of the @abc variable, the two UNIONed sub‑queries, and the final selection logic with detailed code examples.
This article details a step‑by‑step investigation of a compaction‑stuck problem in OceanBase, covering background, environment setup, view and log analysis, root‑cause identification related to clock drift, and the corrective actions taken to restore normal merging.
This article examines MySQL's AUTO_INCREMENT integer types, comparing the 4‑byte INT and 8‑byte BIGINT limits, calculating how long each can store records under realistic insertion rates, and providing step‑by‑step SQL commands and safety tips for converting INT columns to BIGINT.
Learn how to protect sensitive information in MySQL by installing the open‑source Percona data_masking plugin, verifying its status, and using built‑in and custom masking functions for IDs, phone numbers, emails, names, amounts, and addresses, with step‑by‑step SQL examples.
This article explains why the author created the EasyQuery ORM for Java, demonstrates a wide range of query patterns—including single‑record, pagination, joins, subqueries, streaming, custom VO mapping, dynamic conditions, grouping, native SQL, function columns, and high‑performance encryption—while providing complete code snippets and links to documentation and source repositories.
This article shares the most frequent Go middleware pitfalls encountered at Alibaba, explains their root causes—from uneven request distribution and CPU leaks to transaction mishandling and SQL incompatibilities—and provides concrete solutions and best‑practice recommendations to help developers avoid repeating these errors.
This article explains the Spark SQL analysis layer, its core principles, how analysis rules such as ResolveRelations work, and the major pruning optimization introduced in Spark 3.2 that reduces unnecessary rule traversal, illustrated with concrete code examples and Q&A.
This article explores four common batch‑update techniques in MyBatis, compares their performance, shows how to configure Druid and Spring Boot to allow multi‑statement execution, and provides practical code examples and configuration tips for reliable large‑scale data updates.
This article explains MySQL's layered architecture, how the optimizer generates multiple execution plans, evaluates their I/O and engine costs, and selects the plan with the lowest total cost, illustrated with example queries, cost tables, and JSON output.
This article explains how adding an index to a status column in a tens‑of‑millions‑row MySQL table affects query performance, covering the basic index lookup process, extreme cases where full scans are chosen, selectivity, covering indexes, composite indexes, partitioning, and using EXPLAIN to verify execution plans.
This article traces the 38‑year evolution of the SQL standard from its first version in 1986 through successive revisions—SQL‑89, SQL‑92, SQL:1999, SQL:2003, SQL:2006/2008, SQL:2011, SQL:2016, and the latest SQL:2023—highlighting key features, extensions, and the growing gap between standards and vendor implementations.
When a MySQL 5.7 instance crashes without backups or redundancy, this guide shows how to recover both table structures and data using the open‑source tools dbsake and ibd2sql, providing exact commands, parameter explanations, and practical tips for a successful rescue.
This article shares a ready‑to‑run Oracle SQL*Plus script that lists active sessions with details such as instance ID, username, execution time, SQL text snippet, current event, and wait seconds, plus an example output for immediate performance troubleshooting.
This article provides a detailed list of 51 video tutorials covering Oracle database download, installation, basic SQL operations, DML/DQL commands, PL/SQL programming, advanced features, and related database administration topics, offering a complete learning path for Oracle practitioners.
This guide walks through ten essential advanced SQL concepts—including CTEs, recursive queries, temporary functions, CASE‑based pivoting, EXCEPT vs NOT IN, self‑joins, ranking functions, delta calculations, cumulative totals, and date‑time manipulation—providing clear explanations and runnable examples to help data‑science professionals ace interview challenges.
This article demonstrates how to encapsulate MySQL operations in a Python MySQLWrapper class, providing modular methods for connecting, querying, inserting, updating, and deleting records, along with a complete example and usage instructions.
Apache HoraeDB (CeresDB) is an open‑source, distributed, high‑availability time‑series database developed by Ant Group, supporting multi‑dimensional queries, compatible with Prometheus and OpenTSDB, and offering SQL and OLAP capabilities for use cases such as APM, IoT monitoring, financial analytics, and AI‑infra observability.
The article recounts a personal experience of a failed train ticket change, analyzes segment‑wise seat availability, proposes a database model, and demonstrates a SQL query that allocates seats per segment, showing how such logic could enable full‑journey booking and even inspired a new feature on the 12306 platform.
This article walks through converting a SpringBoot + MybatisPlus application from MySQL to PostgreSQL, covering driver addition, JDBC URL changes, dozens of SQL syntax incompatibilities, custom conversion functions, and handy PostgreSQL scripts for bulk column type and default value updates.
This article explains when to use NOT NULL versus NULL in relational database schemas, covering required fields, optional columns, unknown‑state representation, foreign keys, performance impact, versioning, data analysis, migration, default values and JSON types, with concrete SQL examples and practical guidance.
This guide explains how to regularly purge oversized log tables using time‑based DELETE statements, interprets related cloud RDS and ECS alerts, and offers troubleshooting steps and batch‑deletion techniques to address MySQL read‑replica synchronization delays caused by large transactions.
This article walks through practical ways to perform batch updates in MyBatis, compares foreach, case‑when, and INSERT…ON DUPLICATE KEY techniques, explains common Druid WallFilter restrictions, and shows how to enable multi‑statement execution via JDBC and Spring configuration.
This tutorial demonstrates, using MySQL 8.0.32 InnoDB source code, how REPEATABLE-READ and READ-COMMITTED isolation levels affect the locking behavior of SELECT ... FOR SHARE statements on primary‑key rows, including preparation, execution, and detailed lock analysis.
The article recounts a failed ticket change on a high‑speed train, introduces the newly launched 12306 in‑train seat‑swap feature, shares passenger reactions, provides a detailed SQL implementation for segment‑based seat allocation, and reflects on its impact and future suggestions.
This guide explains PostgreSQL table partitioning, covering its definition, benefits, drawbacks, the three partition types (RANGE, LIST, HASH), step‑by‑step SQL commands for creating partitions, indexes, and managing them, and concludes with practical recommendations for effective use.
This article explains what MySQL deadlocks are, outlines the four necessary conditions that cause them, provides a concrete example with SQL code, and offers practical strategies such as lock ordering, timeout settings, and MySQL’s built‑in detection to prevent and resolve deadlocks.
This guide walks through ten essential advanced SQL techniques—including CTEs, recursive CTEs, temporary functions, CASE‑WHEN pivots, EXCEPT vs NOT IN, self‑joins, ranking functions, delta calculations with LAG/LEAD, cumulative sums, and date‑time manipulation—to help data professionals ace interview challenges and write cleaner, more powerful queries.
This article explains why a LEFT JOIN always returns all rows from the left table, clarifies the difference between ON and WHERE clauses, demonstrates the behavior with multiple SQL examples and code snippets, and highlights the special handling of LEFT/RIGHT/FULL joins versus inner joins.
This article explains why adding conditions after a LEFT JOIN's ON clause does not filter rows, contrasts ON and WHERE behavior, and demonstrates the effect with multiple SQL examples and visual illustrations of temporary tables and join semantics.
This article walks through converting a SpringBoot + MyBatisPlus project from MySQL to PostgreSQL, detailing driver setup, JDBC changes, common SQL pitfalls, type‑conversion issues, and provides helper scripts for bulk schema adjustments.
The article traces MariaDB’s evolution from its MySQL roots and community‑driven development, through its NYSE listing, financial performance, restructuring, and the recent K1 Investment Management acquisition, while highlighting key figures, product features, and industry reactions.
This article investigates whether defining a VARCHAR column with a larger length (e.g., VARCHAR(500) vs VARCHAR(50)) changes storage consumption and query speed in MySQL by creating two identical tables, inserting one million rows, measuring space usage, and benchmarking various SELECT and ORDER BY operations.
This article reviews the SQL enhancements added to SQLite between versions 3.22.0 and 3.26.0, covering boolean literals, window functions, the FILTER clause, INSERT … ON CONFLICT (upsert), column renaming, and related API updates.
This guide details essential MySQL upgrade considerations, backup warnings, version‑specific changes, and step‑by‑step procedures for both in‑place and logical migrations, helping administrators safely move from MySQL 5.6 to 5.7 while avoiding common pitfalls.
This guide provides a comprehensive collection of MySQL command‑line scripts covering database and table export/import, creation, deletion, schema inspection, data manipulation, user privilege management, and common DDL operations, all with practical examples.
This article provides a comprehensive overview of MySQL indexing, covering index classifications, B+Tree and Hash structures, primary and secondary (clustered and non‑clustered) indexes, covering index push‑down, index merge, covering indexes, cost‑based index selection, and common pitfalls that cause index inefficiency.
The TDS platform, central to Baidu MEG’s Turing 3.0 ecosystem, unifies data development, warehouse management, monitoring, and resource control through Spark‑based TDE, a visual studio, and AI‑enhanced tools like Smart Diagnosis and Text2SQL, enabling standardized workflows, scalable scheduling, and handling over 30 k daily tasks.
This article explains why the number in MySQL int(N) does not restrict the column’s numeric range, demonstrates that int(1) can store the full unsigned range, and shows how ZEROFILL combined with a display width pads numbers with leading zeros.
This article explains Doris's data partitioning, the four join methods (Broadcast, Shuffle, Bucket Shuffle, and Colocate), runtime filter mechanisms and types, join reorder strategies, and practical tuning recommendations to improve query performance in the Doris MPP database.
The VS Code 1.93 release brings a major shift to ESM modules, fixes SQL language highlighting, adds configuration file UI improvements, introduces Django test support, and enhances IntelliSense and experimental network inspection for JavaScript and Node.js developers.
This tutorial explains how to connect to a MySQL database using PHP's mysqli extension, execute queries, and retrieve results with mysqli_fetch_assoc, providing step-by-step code examples for connection, querying, and a complete script.
This article walks through a real‑world MySQL InnoDB deadlock case, detailing log analysis, reproducing the issue with test data, explaining gap and insert‑intention locks, and presenting a practical solution that checks existence before updating or inserting to prevent deadlocks.
This guide explains how to export MongoDB collections to JSON, load them into DuckDB, generate compatible table schemas, and then transfer the data efficiently into MySQL using DuckDB as an intermediate processing engine.
This tutorial walks you through the fundamentals of Apache Iceberg, its architecture and key features, explains why it’s advantageous for lakehouse workloads, and provides a step‑by‑step Docker‑Compose setup to integrate Iceberg with StarRocks for fast, ACID‑compliant analytics on real‑world taxi data.
This article explains what database deadlocks are, illustrates typical deadlock scenarios, outlines common causes such as resource competition and unordered locking, and presents practical solutions including timeout mechanisms, consistent lock ordering, deadlock detection, lock granularity reduction, and optimistic concurrency control, complemented by example SQL code.
This article explains why high‑cardinality GroupBy queries are slow, describes SLS's underlying aggregation pipeline, and shows how adjusting session parameters and enabling high‑cardinality optimizations can reduce query times from dozens of seconds to just a few seconds across three real‑world test scenarios.
This article examines how InnoDB acquires shared record locks during a duplicate‑primary‑key INSERT, explains the underlying lock‑checking mechanism, demonstrates lock behavior with concurrent DELETE and INSERT transactions, and provides SQL examples and performance_schema queries to illustrate the process.
This article explains the challenges of executing full‑table UPDATE statements on large MySQL tables using row‑based binlog replication, analyzes deep pagination issues, and presents a batch‑processing strategy with FORCE INDEX, SQL_NO_CACHE and controlled rate limiting to safely migrate data at scale.
The article examines Oracle’s comprehensive SQL management capabilities—such as parsing, plan caching, process tracing, execution plan handling, optimization, and runtime monitoring—and compares them with the features offered by major Chinese and open‑source databases, highlighting gaps, strengths, and practical recommendations for adopting domestic solutions.
This article demonstrates how a non‑unique indexed column that allows NULL values behaves with the != operator in MySQL, showing why null rows are omitted from result sets, how index usage changes, and how to rewrite queries with OR or UNION to obtain correct results while preserving performance.
The article analyzes a production out‑of‑memory crash triggered by a pagination bug that misused the OFFSET parameter, explains why the error escaped testing and code review, and presents corrected pagination techniques for Oracle, MySQL and MyBatis to prevent similar failures.
This article compiles a series of technical interview questions and answers covering C++ and Go language differences, thread communication methods on Linux and Windows, stack versus heap memory, orphan processes, read‑write locks, Go goroutine concurrency, TCP three‑way handshake, and a sample SQL query for gender‑based grouping.
GaussDB for MySQL introduces a Limit‑Offset pushdown optimization that moves offset calculation to the InnoDB storage engine, eliminating unnecessary row transfers and index‑back‑table lookups, thereby dramatically reducing query latency for large offsets as demonstrated by TPC‑H benchmarks.
This article recounts L's attempt to change a train ticket, analyzes seat availability across four segments, and presents a minimal SQL model with table creation, data insertion, and a query that determines feasible seat combinations, demonstrating how segment‑wise seat allocation can enable complete journey booking.
This article presents six hands‑on Python generative‑AI projects—ranging from a Llama 2 chatbot built with Streamlit and Replicate to natural‑language‑to‑SQL conversion using LlamaIndex and SQLAlchemy—complete with environment setup, required code snippets, deployment tips and resource links for further exploration.
This article provides a step‑by‑step analysis of MySQL 8.0.32 InnoDB deadlock logs, covering preparation, log extraction, auxiliary information, transaction details, row‑lock structures, and the final summary, with full SQL examples and code snippets for deep understanding.
This article provides a comprehensive guide to using MyBatis dynamic SQL tags such as foreach, if, choose, trim, selectKey, and sql fragments, illustrating best practices with detailed code examples for building flexible, error‑free queries and updates in Java backend applications.
This article explains the challenges of performing full‑table updates on large MySQL tables using row‑based binlog replication and presents a step‑by‑step batch‑update strategy that leverages SQL_NO_CACHE, FORCE INDEX, and controlled pagination to minimize binlog volume and maintain performance.
This guide walks through practical MySQL 8.0 security hardening steps using SQL statements to enforce strong passwords, enable SSL, configure connection control, manage user authentication, set file permissions, and verify encryption of logs and tables.
This article explains MySQL index fundamentals—including hash tables, ordered arrays, and B+‑tree structures—covers index maintenance, demonstrates how indexes improve query execution, discusses best practices such as left‑most prefix, covering indexes, index push‑down, and unique indexes, and provides practical tips for index selection, avoiding index loss, and using EXPLAIN for performance tuning.
This article examines the advantages and disadvantages of using stored procedures in relational databases, discusses common pitfalls and vendor‑specific issues, provides guidance for Oracle stored procedures, and offers practical recommendations on when and how to apply them effectively.
This guide provides a comprehensive overview of Apache Paimon’s architecture, key features, and advantages, explains how to integrate it with StarRocks for real‑time lakehouse analytics, and walks through a complete quick‑start setup including component installation, Flink and Kafka deployment, data ingestion, table creation, and query execution with time‑travel support.
This article explains the challenges of loading historical chat messages in an instant‑messaging app and demonstrates why traditional offset pagination fails, then introduces cursor‑based pagination with SQL examples and best‑practice guidelines for reliable incremental loading.
Pinterest migrated its Partner Insights analytics from Druid to StarRocks, achieving a 50% reduction in p90 latency, a six‑fold cost‑performance improvement, and simplified data ingestion, illustrating the benefits of a modern MPP database for real‑time ad analytics.
Despite the hype around NoSQL, this article argues that SQL and relational databases remain the superior choice for transactional data, tracing two decades of research, highlighting the limitations of NoSQL, and showing how modern databases now incorporate the best features of both worlds.
This article introduces an AI‑driven Smart Report Assistant Bot that automatically converts uploaded Excel files into recommended charts, allows users to customize reports, and details the underlying workflow—including Excel parsing, LLM‑generated SQL, dynamic table creation, chart rendering with ECharts, and image‑merging plugins.
This article provides an in‑depth overview of MySQL fundamentals, covering join types, normalization, data types, index structures, locking mechanisms, transaction isolation, MVCC, replication, sharding, and practical performance‑tuning techniques for large‑scale databases.
This article details the step‑by‑step process of switching a SpringBoot‑MyBatisPlus application from MySQL to PostgreSQL, covering driver integration, JDBC configuration changes, numerous SQL and type‑conversion pitfalls, and provides ready‑to‑run PostgreSQL scripts for bulk column adjustments and default values.
This article provides a detailed tutorial on database sharding (分库分表), covering concepts, common sharding strategies, a step‑by‑step MyCAT configuration, Java JDBC implementation, and key considerations such as data distribution, complexity, transaction handling, and development cost.
This article introduces the PHP mysqli_query function, demonstrates how to connect to a MySQL database, execute SELECT, INSERT, UPDATE, and DELETE statements, and explains result handling with example code, providing a concise guide for backend developers working with relational databases.
This comprehensive guide explains how to boost WordPress site speed and SEO by applying PHP‑based strategies such as query optimization, regular database maintenance, custom tables, option‑table tuning, indexing, and caching, complete with practical code examples.
This article reviews two decades of database research, analyzing the rise and decline of various data models—from hierarchical and relational to NoSQL, vector, and graph databases—while highlighting how AI, cloud, and hardware advances are reshaping DBMS architecture and predicting which approaches will dominate tomorrow’s data landscape.
This article introduces Easy‑Query, a Java ORM that provides a strongly‑typed, chainable query syntax covering basic CRUD, pagination, joins, sub‑queries, native SQL fragments, group‑by, custom VO mapping and high‑performance encryption, illustrated with extensive code examples and usage notes.
This article provides a comprehensive guide to MySQL's DQL, DML, and DDL sections, explaining execution order, core clauses, and practical code examples for creating, altering, and managing databases and tables.
This article explains what constitutes a slow SQL query, how to detect and analyze it using MySQL tools, and provides practical optimization steps—including indexing, business logic review, caching, scheduling, and partitioning—to dramatically reduce execution time.
This article examines why a scalar subquery in an OceanBase 3.2.3.3 SQL statement takes over 1000 seconds, breaks down the execution plan, identifies the costly nested-loop behavior, and presents a rewrite using WITH and LEFT JOIN that reduces the cost from 788 million to 3.6 million and cuts runtime to about 10 seconds.
This article presents twenty practical SQL optimization techniques—including index usage, selective column queries, avoiding functions in WHERE clauses, replacing subqueries with joins, limiting result sets, using EXISTS, batch inserts, covering indexes, proper data types, pagination, and transaction management—to significantly improve query performance and database efficiency.
