This guide explains the exact sequence in which SQL statements are processed, offers practical tips for optimizing multi‑table joins, and provides comprehensive index recommendations and mnemonic rules to improve query performance and reduce resource consumption.
This article explains how MySQL 8.0.32's InnoDB engine generates, reads, and parses Undo logs for an UPDATE operation, and details the step‑by‑step process of locating the primary record, constructing a rollback record, and restoring both secondary and primary index entries.
MySQL 9.2.0 introduces the CREATE_SPATIAL_REFERENCE_SYSTEM privilege, JavaScript library support, enhanced SQL stored routine APIs, ENUM and SET handling, EXPLAIN FORMAT=JSON version 2, while deprecating several version‑token functions and privileges and removing keyword restrictions, providing a comprehensive overview of additions, deprecations, and removals.
Manticore Search is a high‑performance, open‑source C++ search engine that improves upon Sphinx, offering fast full‑text search, SQL compatibility, Docker deployment, and extensive performance advantages over Elasticsearch, with detailed usage examples and code snippets.
The article explains that using a 32‑bit UNSIGNED INT to store IPv4 addresses in MySQL saves space and improves index and range‑query performance, outlines the storage savings compared to VARCHAR, mentions the need for manual conversion, and provides MySQL and Java code examples for converting between string and integer representations.
This article introduces the open‑source JSQLParser library, shows how to set it up in a Spring Boot 3 project, and provides step‑by‑step code examples for simple parsing, extracting tables, retrieving WHERE clauses, building or modifying SQL statements, and handling parsing errors.
A simple typo that changed a daily interest rate from 0.00006944444 to 0.00069444444 inflated calculated interest tenfold, went unnoticed because the author self‑approved the SQL update and testing only checked syntax, and the incident underscores the need for rigorous code review, testing, and change‑management procedures for critical data.
The article investigates a MyBatis batch‑insert job that consumes excessive CPU and takes 14 minutes, explains why the foreach‑generated giant INSERT statement is inefficient, and demonstrates how switching to ExecutorType.BATCH or MyBatis dynamic‑SQL batch support reduces the runtime to under two seconds.
When a client sends an SQL statement over TCP, the parser checks syntax and semantics and builds a query tree, the optimizer evaluates possible execution plans using statistics to estimate costs and selects the most efficient one, and the executor runs the plan, using the buffer manager for reads or the transaction manager for updates while the lock manager enforces ACID properties.
This article explains how MySQL 8.0.32 InnoDB generates and stores Update undo logs, detailing the log format, the meaning of each field, how to interpret the log address, and provides concrete SQL examples and shell calculations for practical insight.
This article explains the concept of database indexes, compares various index structures such as hash tables, ordered arrays, binary trees, B‑trees and B+‑trees, details InnoDB primary, secondary, covering and composite indexes, discusses prefix indexes and order‑by optimization, and provides practical advice and SQL examples for improving MySQL query performance.
This article explains how to migrate sharded Mycat data to ActionDB with ActionOMS, addressing duplicate primary keys by modifying both source and target schemas, configuring migration tools, and completing validation and post‑migration steps.
This article presents a real‑world case study of a high‑concurrency data‑processing interface whose response time was reduced from 30 seconds to under one second by diagnosing SQL bottlenecks, applying PostgreSQL array aggregation, moving logic to the database, and introducing Caffeine caching, with detailed code examples and performance analysis.
This guide compiles the most common MySQL interview questions asked by leading Chinese tech firms, covering fundamentals, data types, storage engines, indexes, transactions, locks, views, stored procedures, triggers, and optimization, with clear explanations, diagrams, and a free downloadable resource.
This guide explains how to overcome MySQL analytical performance bottlenecks by integrating AnalyticDB MySQL with Data Transmission Service (DTS), covering architecture, resource setup, test data creation, real‑time synchronization, and high‑performance SQL queries using window functions.
To recover accidentally deleted MySQL rows, ensure binary logging is enabled and set to ROW mode, locate the appropriate binlog files, use mysqlbinlog (or tools like binlog2sql) to extract the original INSERT events within a time window, and replay or reconstruct them to restore the data.
The article details how WanJia Shuke, the tech arm of China Resources Vanguard, tackled retail system fragmentation, user‑experience degradation, complex linkages and scalability limits by migrating dozens of projects to the distributed OceanBase database, achieving up to 70% performance improvement, 25% cost reduction and streamlined operations.
This article presents ten practical MyBatis techniques—including dynamic SQL, resultMap, foreach, pagination, annotation proxies, second‑level cache, dynamic table names, custom type handlers, logging, and multi‑datasource configuration—to help Java developers write cleaner, safer, and more performant data‑access code.
This article explains how Spark ≥ 3.3 introduces Storage Partitioned Join (SPJ) to avoid costly shuffle operations when joining partitioned V2 source tables such as Apache Iceberg, detailing the required conditions, configuration settings, practical code examples, and various join scenarios including mismatched partitions and data skew.
The article shares practical methods and code examples for making SQL both readable and high‑performing in large‑scale data platforms, covering predicate push‑down with subqueries, deduplication strategies, bucket utilization, and Python‑driven job parameter handling.
This article recounts a 2012 incident where a backend API slowed dramatically after hidden BLOB columns were added, and explains why using SELECT *—which blocks index‑only scans, forces extra I/O, increases deserialization, network, and client processing costs, and hampers schema maintenance—should be avoided in favor of explicit column lists.
This guide introduces the open‑source DBeaver database manager, walks through downloading, installing, and configuring it, demonstrates its use with a SpringBoot3 + Vue e‑commerce project, and covers theme settings, data source creation, table operations, SQL editing, and its support for various database types.
This article explains how MySQL 8.0.32 InnoDB generates and stores Insert Undo logs, detailing their binary format, field meanings, example values, and how the hidden DB_ROLL_PTR field encodes the log address, with accompanying SQL and shell examples.
This article introduces RisingWave, an open‑source streaming database, and explains how its SQL‑based interface, compute‑storage separation, UDF support, and materialized views enable efficient real‑time feature engineering, state management, and diverse downstream applications, including the enhancements in RisingWave 2.0.
Discover ten powerful MyBatis techniques—from dynamic SQL and resultMap customization to batch operations, pagination, annotation‑based mappers, caching, dynamic table names, custom type handlers, logging, and multi‑datasource configuration—that can dramatically improve Java backend development efficiency and code quality.
This article explains the hidden performance costs of traditional OFFSET pagination in MySQL, analyzes why deep paging becomes a bottleneck, and presents keyset (cursor) pagination and other optimization techniques as efficient alternatives for handling massive or frequently changing datasets.
Alibaba Cloud’s Serverless Log Service (SLS) has overhauled its SQL engine with a C++‑based compute engine, SIMD acceleration, storage‑compute fusion, and optimized scheduling, delivering up to three‑fold speed gains, 50% latency reduction, and significant improvements across high‑cardinality, JSON, IP, and join queries.
The article explains how MaxCompute’s newly introduced Bloomfilter index dramatically improves emergency data tracing by cutting query time and resource consumption, replacing costly secondary indexes, reducing storage by over 45%, and providing a lightweight, high‑efficiency solution for large‑scale point‑lookup scenarios.
This article explains how to accurately calculate the real data size in OceanBase by using OCP cluster overview, system views, and SQL queries for server‑level, tenant‑level, and table‑level statistics, covering differences between V3 and V4 versions and the use of obdiag.
This article presents a step‑by‑step AI‑assisted investigation of an OceanBase performance test that triggered error 4013 due to memory limits, detailing four interactive rounds with ChatDBA, the root‑cause analysis, and practical remediation measures.
Testing shows that VARCHAR(50) and VARCHAR(500) occupy identical storage, yet while simple lookups perform similarly, sorting on the longer column triggers disk‑based mergesort and can be several times slower, demonstrating that excessive VARCHAR length harms query performance without saving space.
This article demonstrates how to use MySQL's EXPLAIN statement to examine execution plans, interpret key fields such as possible_keys, key, rows, and Extra, and apply index adjustments—including adding and forcing indexes—to improve query performance while weighing sorting costs.
This article walks through 13 practical MySQL techniques—including group_concat, char_length, locate, replace, now, various INSERT variations, SELECT FOR UPDATE, on duplicate key update, SHOW CREATE TABLE, CREATE TABLE … SELECT, EXPLAIN, SHOW PROCESSLIST, and mysqldump—providing clear examples, SQL snippets, and screenshots to help developers write more efficient and reliable queries.
This article walks through a real‑world slow‑SQL case from a logistics routing system, demonstrating how systematic formatting, layer decomposition, condition push‑down, join optimization, and validation can transform a tangled query into a clean, performant, and maintainable statement.
This article introduces an online SQL-to-ER diagram tool, explains its advantages, provides step‑by‑step usage instructions, showcases example SQL and Java code for parsing and generating ER diagrams, and discusses the underlying design‑pattern architecture such as Strategy, Factory, Dependency Injection, and Singleton.
This article explains why indexes are essential for query performance, lists the most common scenarios for creating single‑column, unique, composite, and prefix indexes, provides concrete SQL examples, and shows how to verify index usage with EXPLAIN.
This article examines how pagination queries on large MySQL tables become dramatically slower as the offset grows, presents real‑world performance measurements, and offers three practical optimization strategies—including selecting only primary keys, range‑based filtering, and using ElasticSearch—to keep query latency low.
This guide explains how to back up single or multiple MySQL databases, entire server instances, specific tables, or only schema, using mysqldump with options like -B, gzip compression, add‑drop flags, and provides restore procedures and an automated backup script.
The article explains why multi‑table JOINs in MySQL can degrade performance, readability, and index usage, and it presents optimization strategies such as query decomposition, data redundancy, wide tables, and introduces the hash join algorithm with detailed build and probe phases, including disk‑based handling.
This article presents twelve practical MyBatis‑Plus optimization techniques—including avoiding isNull checks, specifying select fields, batch operations, using EXISTS, safe ordering, type‑safe Lambda queries, handling nulls, performance tracking, enum mapping, logical deletion, optimistic locking, and increment/decrement methods—to improve code readability, maintainability, and database query performance.
This article walks through StarRocks' materialized view refresh mechanisms, explains the various refresh triggers, details the refresh workflow, and introduces new parameters that allow selective refreshing of only changed data to avoid costly full‑partition refreshes.
This article outlines fifteen typical MySQL pitfalls—including missing WHERE clauses, lack of indexes, improper NULL handling, wrong data types, deep pagination, missing EXPLAIN analysis, charset misconfiguration, SQL injection risks, transaction misuse, collation issues, overusing SELECT *, index loss, frequent schema changes, missing backups, and unarchived historical data—and provides concrete examples and best‑practice solutions to improve performance, reliability, and security.
This article explains the concept and importance of MySQL database transactions, describes the ACID properties, shows how to use transaction control statements and savepoints, and demonstrates each isolation level (read uncommitted, read committed, repeatable read, serializable) with practical SQL examples.
This article outlines fifteen frequent MySQL mistakes—from missing WHERE clauses and absent indexes to improper data types, deep pagination, SQL injection risks, and inadequate backups—providing clear examples and practical solutions to improve query performance, data integrity, and overall database reliability.
The author compares IDEA's built‑in database tool with Navicat for running massive SQL scripts, showing that IDEA consistently uses less CPU, avoids freezes, and achieves higher execution speed—even on files up to 1.83 GB—making it the preferred choice for large‑scale database migrations.
This article explains the different MySQL JOIN types—including INNER, LEFT, RIGHT, and simulated FULL OUTER joins—by creating sample order and customer tables, inserting test data, and showing the corresponding SQL statements and result sets with visual diagrams.
This article explains MySQL's various numeric, date/time, and string data types, discusses their characteristics such as signed/unsigned integers, floating‑point precision, fixed‑point accuracy, and provides practical best‑practice recommendations for choosing optimal types in database design.
This article examines how InnoDB acquires various row, gap, and next‑key locks during INSERT … ON DUPLICATE KEY UPDATE statements under REPEATABLE‑READ and READ‑COMMITTED isolation levels, illustrating the lock behavior with detailed SQL examples and performance‑schema queries.
The article argues that while solid SQL skills are essential for data development and interview preparation, investing excessive effort to master advanced SQL yields diminishing returns, especially as AI tools automate routine queries, and recommends focusing on newer, more impactful technologies instead.
The article recounts a developer’s mistake of entering an extra decimal place in a SQL update for daily interest rates, explains how the error escaped code review, testing and production validation, and shares practical lessons on precision handling, review processes, and operational safeguards.
This article examines how pagination queries on massive MySQL tables become dramatically slower as the offset grows, defines what constitutes a slow SQL, and presents three practical optimization techniques—including returning only primary keys, range filtering, and using Elasticsearch—to dramatically improve query performance.
This guide introduces StarRocks, a high‑performance MPP database, explains its architecture and typical use cases, walks through a Docker‑based quickstart, shows how to create databases and tables, load NYC crash and weather datasets via Stream Load, and demonstrates analytical SQL queries that reveal traffic‑accident patterns under different weather conditions.
This article explains the need for vector databases in the AI era, reviews PostgreSQL's extensible ecosystem, compares vector‑database options, provides step‑by‑step PgVector installation and usage, shares operational best practices, performance tuning tips, and real‑world Qunar & Tujia case studies.
This guide walks through using SQLite's C API—including opening databases, executing queries with and without callbacks, handling results, and performing insert, delete, and display operations—complete with code snippets, table schemas, and compilation instructions.
This tutorial shows how to use Elasticsearch's built‑in SQL support to run familiar MySQL‑style queries—creating an index, inserting documents, and performing SELECT, WHERE, ORDER BY, GROUP BY, LIMIT, aggregation, date and string functions—making ES data retrieval as easy as relational databases.
The article recounts how SQLite’s creator, D. Richard Hipp, learned about covering indexes from MySQL co‑founder David Axmark at a German PHP conference and, during a trans‑Atlantic Delta flight, added support that lets queries be satisfied entirely from the index, dramatically boosting performance by avoiding table reads.
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 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.
