This article explains OceanBase's write throttling feature, describing the two main parameters, the internal implementation logic, source‑code analysis, configuration steps, and practical usage scenarios for controlling write speed and ensuring system stability.
This guide explains how to use MyBatis dynamic SQL tags such as foreach, if, where, choose, trim, and selectKey, providing practical examples for conditional queries, batch inserts, updates, and reusable SQL fragments to write flexible and error‑free MySQL statements in Java applications.
The SQLE 2.2305.0‑pre1 release introduces a MySQL‑instance‑based smart scan for slow queries, adds configurable data source defaults, fixes several MySQL rule and workflow bugs, and provides comprehensive documentation, download links, and usage examples for both community and enterprise editions.
This article provides a comprehensive overview of Apache Doris, detailing its origins, MPP‑based analytical capabilities, data‑lake integration techniques, recent architectural enhancements, performance optimizations, community growth, and upcoming development plans, while also addressing common user questions.
This guide walks you through installing DataGrip, configuring drivers, customizing settings, and using its powerful SQL editing, navigation, result‑set filtering, export/import, and shortcut features to streamline database development and administration tasks.
This article explains why correlated subqueries are a performance bottleneck, introduces the Apply (Correlated Join) operator and a systematic set of transformation rules that push Apply down and replace subqueries with efficient joins, covering scalar, existential, and quantified subqueries, aggregation handling, and set operations, all illustrated with TPC‑H examples.
This guide explains how to pinpoint the MySQL statements that use the most CPU by mapping OS thread IDs to MySQL sessions, querying performance_schema and information_schema, and reviewing the execution plan to identify optimization opportunities.
This comprehensive guide covers MySQL basics such as join types, normal forms, data types, indexing strategies, lock mechanisms, transaction isolation levels, replication, sharding, and practical optimization techniques for handling large tables and high‑traffic workloads.
This article explains MySQL index fundamentals, focusing on the leftmost prefix principle, common index failure scenarios, the impact of SELECT *, range queries, functions, LIKE patterns, OR, IN, ORDER BY, and provides practical examples and optimization tips.
This article explains how MySQL handles queries that involve two separate indexed columns, demonstrates the index_merge execution plan with real‑world examples, details the three index‑merge strategies (intersect, union, sort_union), and shows how to control or disable the feature for better performance.
This article examines the most frequent reasons MySQL queries become slow—including missing or ineffective indexes, deep pagination, massive tables, excessive joins, IN‑list overload, dirty pages, ORDER BY file‑sort, lock contention, and hardware limits—while offering concrete optimization techniques and best‑practice recommendations.
This article explains how MySQL implements partition pruning, describing the conditions under which the optimizer can skip irrelevant partitions, the internal data structures such as read_partitions and lock_partitions, the role of SEL_TREE and SEL_ARG, and provides a detailed example with code to illustrate the pruning process.
This article explains how to verify whether OceanBase's freeze mechanism and memstore dump processes are functioning correctly by querying internal tables, inspecting logs, and checking related metrics such as active_memstore_used, major_freeze_trigger, and MemTable reference counts.
This article presents a practical comparison between Elasticsearch and ClickHouse, detailing their architectures, Docker‑Compose deployment, data ingestion pipelines, a series of representative queries, and benchmark results that show ClickHouse generally outperforms Elasticsearch in basic search and aggregation scenarios.
This article explains why correlated subqueries are costly, introduces the Apply (correlated join) operator, and presents a series of systematic transformation rules that convert scalar, existential, and aggregate subqueries into efficient join‑based plans while preserving SQL semantics.
This guide walks you through downloading and installing the free, open‑source DBeaver tool, configuring connections to various databases such as PostgreSQL and Oracle via JDBC, testing those connections, and using the built‑in ER diagram feature to visualize database schemas.
Effective PostgreSQL performance tuning requires a holistic approach, covering hardware sizing, OS sysctl tweaks, filesystem choices, query and schema design, key PostgreSQL parameters, concurrency settings, cache management, and careful scheduling of automated maintenance tasks to avoid resource conflicts.
OpenMLDB is an open‑source machine‑learning database that delivers a production‑grade, consistent online‑offline feature platform for real‑time AI applications such as recommendation, risk control and fraud detection, offering millisecond‑level feature computation, dual SQL engines, extensive ecosystem integration, and a roadmap of new capabilities.
This article walks through MySQL 8.0.32 source code to explain how the server builds a tree for a WHERE clause, evaluates each condition using classes such as Item_cond_and, Item_cond_or, Item_func_gt, and Item_func_eq, and ultimately decides whether a row satisfies the query.
This article compares Elasticsearch and ClickHouse by describing their architectures, demonstrating a Docker‑compose test environment, executing equivalent queries via both systems, and presenting performance results that show ClickHouse generally outperforms Elasticsearch in basic search and aggregation scenarios for log data.
This article explains how SQL, built on relational algebra, became the standard analysis language for OLAP engines, covering its history, data models, syntax, functions, aggregation techniques, window functions, subqueries, and practical optimization considerations for modern data warehouses.
This article examines a MySQL deadlock caused by concurrent INSERT operations on a unique index, detailing the detection, reproduction, root cause analysis using InnoDB status, performance_schema data, and provides scripts and recommendations to trace and resolve such deadlocks.
This article introduces ClickHouse as an OLAP columnar database, explains its advantages over row‑based systems, details a real‑world migration from MySQL, shows performance benchmarks, shares practical installation and data‑sync methods, and highlights common pitfalls and solutions.
This weekly roundup curates the latest high‑quality technical shares on databases, featuring articles about PolarDB HTAP acceleration, MySQL datetime precision, new MySQL releases, ChatGPT as a database client, and the SQLE platform's recent enhancements and upcoming plans.
This article explains how MySQL 8.0.32 expands the '*' in a SELECT statement into all table columns, detailing the lexical parsing, query preparation, and the functions Item_asterisk::itemize, Query_block::prepare, Query_block::setup_wild, and insert_fields through source‑code excerpts.
The article analyses SQL’s fundamental shortcomings—its lack of ordered collections, incomplete set semantics, and missing object‑reference support—illustrates these issues with concrete query examples, and argues that the open‑source Structured Process Language (SPL) solves them with true ordering, full collection handling, and native reference mechanisms.
This article explains MySQL's index‑merge optimization, detailing how the EXPLAIN output indicates its use, the three underlying algorithms (intersect, union, sort‑union), practical query examples, configuration flags, and step‑by‑step guidance for testing with sample tables and indexes.
This guide explains how to use the OceanBase server_permanent_offline_time parameter to permanently offline a faulty node, rebuild its data, and restore normal operation, including preparation, command steps, verification, and recommended settings for production.
This article introduces Alibaba's Druid database connection pool, explains its core features and filters, shows how to add the Maven starter, configure properties and filters in Spring Boot, demonstrates the built‑in monitoring pages, slow‑SQL logging, Spring AOP integration, and provides methods to remove the default advertisement and retrieve monitoring data via code.
This guide introduces DataGrip, a JetBrains database client, covering download, installation, theme selection, driver management, connection setup, SQL editing shortcuts, data export/import, table creation, navigation, and various productivity features for efficient database development.
The article introduces the SQLE 2.2304.0-pre2 preview release, outlines the tool’s purpose for database users and administrators, details new enterprise‑level features such as affected‑row statistics and enhanced rule severity display, lists added MySQL audit rules, and provides complete release information and resource links.
This guide introduces DBeaver, a free Java‑based database management tool, covering its cross‑platform installation, how to create connections for databases like PostgreSQL via JDBC, and how to generate ER diagrams from existing schemas, all illustrated with step‑by‑step screenshots.
This article describes how Meituan addresses the rapid growth of Flink SQL jobs by introducing fine‑grained TTL and concurrency settings, an editable execution plan for state migration, pre‑analysis compatibility checks, and a bytecode‑instrumented debugging system that captures operator data and streams it to Kafka for analysis.
This article explains how OceanBase's recycle bin works, covering error causes when flashing back tables, methods to obtain a table's database, handling of multiple same‑name tables, visibility across tenants, and the ability to flash back tables or tenants even after the recycle bin is disabled.
This article details Meituan's implementation of Flink SQL at scale, covering fine‑grained job configuration, state‑TTL management, state‑migration techniques for job upgrades, a custom debugging tool for correctness issues, and future directions for Flink SQL enhancements.
The SQLE 2.2304.0-pre1 release introduces a preview version of the SQL audit and quality control tool with detailed project information, enhanced suggestion features for audit results, and a concise list of new functionalities and release details for database users and administrators.
This article presents a senior architect’s comprehensive MySQL optimization guide, covering execution process, naming conventions, storage engine choices, indexing strategies, query writing best practices, transaction handling, partitioning, and other performance‑tuning techniques to improve database efficiency and reliability.
This article explains how the default PL/SQL delimiter '/' in OceanBase Oracle tenants conflicts with division operators, causing syntax errors, and demonstrates fixing the issue by changing the delimiter to '//' with detailed code examples and execution results.
This article explains MySQL's date and time manipulation functions—including addtime, adddate, date_add, subtime, subdate, and date_sub—showing their syntax, usage with numeric and INTERVAL arguments, and providing example queries for adding or subtracting seconds, minutes, days, weeks, months, and years.
The 2023 DAMS China Data Intelligence Management Summit in Shanghai featured a technical presentation by Zhang Shenbo on an open‑source SQLE solution for SQL quality control, covering multi‑database auditing, automated review workflows, and practical tips to reduce DBA workload and cross‑department communication.
This article demonstrates how to generate millions of test rows in MySQL, analyzes the performance impact of deep pagination using LIMIT, explains why non‑clustered index lookups cause costly table scans, and presents two optimization approaches—sub‑query ID filtering and key‑set pagination—to dramatically reduce query latency.
This article explores MySQL single‑table row limits, demonstrates how to generate massive test data, explains InnoDB page and index structures, calculates theoretical maximum rows based on B+‑tree parameters, and provides practical tuning tips for handling large tables.
When a mistaken UPDATE statement overwrites an entire Oracle table, this guide explains four practical recovery techniques—including transaction rollback, flashback queries, LogMiner undo extraction, and RMAN backup restoration—detailing their prerequisites, commands, and trade‑offs.
This tutorial walks through setting up Liquibase in a Spring Boot project, creating a Maven plugin to generate XML/YAML changelogs, configuring the application, and performing common database operations such as creating tables, adding columns, creating indexes, initializing data, while also addressing a common includeAll path issue.
This article explains why developers often include the redundant condition “WHERE 1=1” in SQL statements, demonstrating through performance tests that it has no effect on execution time, and describing its practical use for simplifying the addition of further conditions in both static and dynamic queries.
SCQL, an open‑source Secure Collaborative Query Language built on multi‑party computation, enables SQL‑style privacy‑preserving data analysis for small‑to‑medium organizations by offering easy integration, fine‑grained column‑level access control, broad data‑source support, and optimized performance for collaborative queries.
This article explains MySQL index covering and how avoiding table lookups (back‑to‑table) can reduce retrieval steps, improve query performance, and provides practical examples, execution‑plan analysis, and optimization tips for large tables in production.
The article explains the concepts, differences, and practical usage scenarios of local (partition‑aligned) and global indexes in OceanBase, providing SQL examples for queries with and without partition keys and guidance on creating unique indexes using either index type.
This article examines how major SQL parsers like ANTLR and Calcite implement the LIKE operator, compares regex‑based, naïve, and finite‑state‑machine approaches, and presents optimizations that achieve linear‑time performance for most pattern matching scenarios.
The article reviews Apache Flink 1.17's major batch and streaming improvements, including new Delete/Update APIs, performance boosts, SQL client gateway, checkpoint and watermark enhancements, StateBackend upgrades, and practical use‑case scenarios for data engineers.
This article demonstrates how to generate two million MySQL rows, examines the performance impact of large OFFSET values in LIMIT clauses, explains why non‑clustered indexes cause back‑table lookups, and presents two optimization strategies—using sub‑queries to fetch IDs and employing key‑based pagination—to dramatically speed up deep page queries.
The article explains three ways to perform batch inserts with MyBatis—ordinary single-row inserts, foreach‑based bulk inserts, and ExecutorType.BATCH—analyzes their SQL logs, performance bottlenecks, MySQL limits, and provides practical recommendations for large‑scale data insertion.
This article outlines nine typical problems that arise when executing SQL scripts in production—such as missing scripts, syntax errors, wrong order, timing issues, wrong database selection, long‑running updates, lack of rollback, missing indexes, and field renaming—and provides practical solutions to prevent each pitfall.
This article walks through common SQL development scenarios on ODPS, examining why naïve UNION and COUNT DISTINCT can be slow, how to rewrite queries with GROUP BY, UNION ALL, JSON aggregation, and map‑join techniques, and shows the resulting execution‑plan improvements with concrete code and performance numbers.
This article provides a detailed tutorial on installing, configuring, and efficiently using JetBrains' DataGrip database client, covering connection setup, UI navigation, SQL editing shortcuts, data import/export, and advanced features for streamlined database management.
Apache Flink 1.17 introduces a suite of batch and streaming enhancements—including a new Streaming Warehouse API, significant TPC‑DS performance boosts, adaptive batch scheduling, improved checkpointing, expanded SQL capabilities, Hive connector upgrades, and broader filesystem support—while also delivering upgrades to FRocksDB, Calcite, and the token framework to strengthen its position as a leading unified data‑processing engine.
This article explains the background, environment setup, overall process, and detailed SQL commands required to safely lock, kill sessions, and permanently delete a MySQL tenant in OceanBase, including optional recycle‑bin handling.
This article reveals frequent SQL mistakes—such as misuse of LIMIT, implicit type conversion, sub‑query updates, mixed ordering, EXISTS, condition push‑down, premature filtering, and intermediate result push‑down—and provides concrete rewrite techniques that dramatically improve MySQL query performance.
The article describes an intelligent merchant‑side diagnostic platform that unifies ad‑operation data in a centralized lake, uses a low‑code rule engine with arithmetic, code, and Java class modes to orchestrate reusable SOPs, and employs an acceleration layer for fast large‑scale queries, achieving over 90% coverage and outlining future expansion.
This article introduces several SQL date‑time functions—including TO_TIMESTAMP, LOCALTIMESTAMP, NOW, CURRENT_TIMESTAMP, DATE_FORMAT, and UNIX_TIMESTAMP—explaining their syntax, parameters, behavior, and providing example queries with expected results for each function.
The article explains what OceanBase BUFFER (Queuing) tables are, why frequent bulk updates cause performance degradation, and provides three optimization methods—including binding execution plans, manual data transfer, and setting table_mode='queuing'—with concrete MySQL and Oracle examples and query scripts to monitor merges.
This article explains the MySQL sql_mode setting, details each of its ten modes—including STRICT_TRANS_TABLES, STRICT_ALL_TABLES, and ONLY_FULL_GROUP_BY—provides a command to view the current mode, and also includes a brief promotion for a PHP training class.
This article reviews the SQLite SQL enhancements introduced between versions 3.22.0 and 3.26.0, covering boolean literals, window functions, the FILTER clause, upsert syntax, column renaming, and related API changes, while comparing SQLite’s support to other major database systems.
This guide demonstrates how to configure Hive metastore, connect SparkSQL to Apache Hudi, create COW and MOR tables, perform insert, update, merge, delete, and insert‑overwrite operations, and illustrates each step with executable code snippets and sample results.
MySQL 8.0 introduces numerous enhancements including InnoDB engine defaults, atomic DDL, instant column addition, CTE support, UTF8MB4 default charset, Clone plugin, resource groups, role management, multi-valued and functional indexes, invisible indexes, descending indexes, SET_VAR syntax, persistent parameters, window functions, online DDL, and improved backup and binlog capabilities.
This article explains common scenarios that cause MySQL (and related services) to consume 900% CPU, outlines step‑by‑step diagnosis using tools like top and show processlist, and provides practical remediation such as killing offending threads, adding missing indexes, disabling slow‑log, and leveraging Redis caching to bring CPU usage down to acceptable levels.
Upgrading MySQL 5.6/5.7 databases to 8.0 can introduce charset mismatches between existing utf8 (utf8mb3) tables and newly created utf8mb4 tables, causing index loss and performance degradation that can be resolved by unifying the character set across all tables.
This article introduces JSqlParser, a Java library that parses SQL statements into object structures, demonstrates how to extract query components with sample code, lists supported databases, and highlights additional features like formatting and framework integration.
This article introduces Flyway as a database migration solution, explains its versioned, undo and repeatable migrations, details common commands, shows Maven and YAML configuration for Spring Boot, provides sample SQL migration scripts and logs, and highlights paid‑only features.
SQLite’s 2018 updates bring powerful new SQL capabilities—including true/false literals, IS TRUE/FALSE predicates, window functions, FILTER clauses, UPSERT syntax, and column‑renaming—demonstrating that this lightweight, serverless database can handle modern workloads while remaining reliable and widely deployable.
This article explains the motivation, design, and implementation of Alibaba Cloud's SLS Schema‑on‑Read scanning mode, showing how it enables SQL analysis on raw log data without pre‑built indexes, improves flexibility for evolving schemas, and reduces storage and index costs in various log‑analysis scenarios.
This article explains the architecture and key components of distributed analytical databases, contrasting them with MPP systems, and details the design of StarRing’s ArgoDB—including its storage, compute, transaction, and SQL engines—highlighting scalability, elasticity, and resource management for modern data warehousing.
This article analyses how MySQL 5.7 InnoDB processes COUNT(*) versus COUNT(1), COUNT(id) and COUNT on indexed or non‑indexed columns, showing execution‑plan differences across various index configurations, explaining why count(*) can be unexpectedly slower and offering practical guidance for query optimization.
This article walks through a practical SQL optimization workflow, showing how to detect problematic queries via system metrics and execution plans, analyze table structures and indexes, apply concrete ALTER statements and index changes, and iteratively verify performance improvements, illustrated with a detailed MySQL case study that achieved a twenty‑fold speed increase.
This article compares MySQL's DISTINCT and GROUP BY clauses, explaining how index availability, implicit sorting, and MySQL version differences affect their execution speed, and provides practical guidance on choosing the right clause for optimal query performance.
This guide presents 21 practical SQL habits covering performance tuning, safe data manipulation, and coding standards—such as using EXPLAIN, adding LIMIT to deletes, documenting tables, consistent formatting, proper indexing, and transaction handling—to help developers write more efficient, reliable, and maintainable queries.
Deploying SQL scripts to production often leads to missed scripts, syntax errors, wrong execution order, mistimed runs, wrong databases, long runtimes, lack of rollback, missing indexes, and illegal column renames, but following structured version‑controlled practices can prevent these costly issues.
Alibaba’s Secure Data Hub (SDH) is a privacy‑preserving data clean‑room platform that uses secure multi‑party computation and privacy‑enhancing machine learning to let advertisers, ad platforms, and auditors jointly analyze marketing data via a simple SQL API while keeping raw data encrypted, column‑level protected, and confined to each party’s private domain.
This article explains OceanBase's three tenant deletion approaches—normal, delayed, and immediate—detailing the DROP TENANT syntax, recycle‑bin behavior, and providing step‑by‑step SQL examples for creating, dropping, restoring, and permanently removing tenants.
This article presents Datacake's experience of integrating AI algorithms into big data governance, covering the bidirectional relationship between AI and big data, health‑score assessment of data tasks, intelligent Spark parameter tuning, SQL engine selection, and future application scenarios across the data lifecycle.
This article walks through a MySQL 5.6 scenario where a nested sub‑query to find students scoring 100 in Chinese took over 30,000 seconds, then demonstrates how adding single‑column and composite indexes, rewriting the query with EXISTS or JOIN, and analyzing execution plans dramatically reduces execution time to milliseconds while explaining index merge, covering indexes, left‑most prefix, and sorting optimizations.
The article explains how Oracle's built‑in auto‑task jobs run during specific weekday and weekend windows, why they can cause performance degradation by changing execution plans or consuming resources, and provides practical steps to diagnose, adjust windows, and selectively disable problematic tasks.
This article presents a comprehensive set of MySQL performance guidelines, including avoiding SELECT *, preferring specific columns, eliminating OR in WHERE clauses, using numeric types, choosing VARCHAR over CHAR, applying proper JOINs, leveraging LIMIT, UNION ALL, composite indexes, and other practical tips to improve query efficiency and resource usage.
This article compiles fifteen typical scenarios where MySQL indexes become ineffective—covering composite index left‑most matching, SELECT *, arithmetic on indexed columns, functions, LIKE patterns, type conversion, OR/AND logic, range queries, ORDER BY, and optimizer decisions—providing example SQL, EXPLAIN output, and practical tips to help developers prevent costly full‑table scans.
This article walks through a hands‑on process for detecting problematic SQL statements, interpreting execution plans, and applying concrete optimizations such as index adjustments and data‑type fixes to dramatically improve query performance, illustrated with a real MySQL case study.
This article explains how to identify slow SQL statements, analyze execution plans with EXPLAIN, use profiling and optimizer trace, and apply practical solutions such as index optimization, query rewriting, and alternative storage, illustrated with ten real‑world MySQL case studies.
This article provides a step‑by‑step guide for scaling an OceanBase cluster, covering both white‑screen and black‑screen methods to add zones (replicas) and resources (OBServers), including configuration file preparation, deployment commands, zone addition, verification queries, and procedures for both expansion and contraction.
This article details the evolution of Tencent Music's content library data platform from version 1.0 to 4.0, describing business requirements, architectural redesigns—including migration from ClickHouse to Apache Doris, introduction of a semantic layer, and extensive write, query, and cost optimizations—while sharing practical lessons and future directions.
This article explains why preserving state during Flink SQL job iterations is crucial, analyzes the challenges caused by DAG changes and serializer incompatibility, and presents a visual‑preview, UID editing, and automatic mapping solution to enable reliable state migration for streaming workloads.
This article explains why SQL join execution plans often cause severe performance drops after moving from Oracle to domestic or open‑source databases, demonstrates each join type with PostgreSQL examples, shows how plan choices like HASH JOIN versus NESTED LOOP affect speed, and offers rewrite techniques such as NOT EXISTS or UNION to restore efficiency.
The SQLE 2.2302.0 release introduces data source import from external platforms, operation‑record viewing, improved CloudBeaver integration, manual deployment support, numerous UI optimizations, and a long list of bug fixes, providing a more seamless and secure SQL auditing experience for both enterprise and community users.
This article examines how common business operations using SELECT ... FOR UPDATE and INSERT can cause MySQL Next‑Key lock deadlocks, explains the underlying gap‑lock and row‑lock mechanisms, demonstrates practical experiments, and proposes strategies such as postponing locks or using primary‑key updates to avoid such deadlocks.
This article provides a comprehensive step‑by‑step tutorial on deploying OceanBase in a single‑node environment, covering resource unit, resource pool, and MySQL‑type tenant creation, modification, deletion, user management, and relevant system data dictionary tables, with full SQL command examples.
This article explains the logical execution sequence of a typical SQL query, detailing each phase from FROM/JOIN/WHERE through GROUP BY, HAVING, SELECT, ORDER BY, and LIMIT, and illustrating the process with diagrams and code examples.
This article examines how common business operations using SELECT ... FOR UPDATE and INSERT can cause Next‑Key lock deadlocks in MySQL under repeatable‑read isolation, demonstrates the locking behavior with practical SQL examples, and proposes strategies such as post‑locking and primary‑key updates to prevent such deadlocks.
Discover 18 essential MySQL 8.0 management enhancements—including persistent global variables, admin IP/port settings, minimal installation packages, resource groups, read‑only databases, improved SHOW PROCESSLIST, instant DDL, faster DROP/TRUNCATE, OS thread visibility, connection memory limits, enriched slow‑log details, backup options, Clone plugin, upgrade process, client defaults, and RESTART command usage.
This article explains how to create and use control files with the obloader tool to import and export data in OceanBase, covering template syntax, practical examples, common errors, and multiple solutions for handling column mismatches and data preprocessing.
This article examines eight frequent SQL anti‑patterns—including misuse of LIMIT, implicit type conversion, sub‑query updates, mixed ordering, EXISTS clauses, condition push‑down, premature data reduction, and intermediate result handling—explaining why they degrade performance and providing concrete rewrite strategies and code examples to dramatically speed up queries.
This guide introduces Sequel Pro, explains how to configure Standard, Socket, or SSH connections with required fields, describes the main interface modules for browsing schemas, tables, relationships, triggers, and running queries, and highlights useful shortcuts for efficient MySQL management.
This article walks through setting up a local MySQL cluster, configuring Ngrok, deploying Bytebase via Docker, enabling GitOps integration with GitHub, and managing SQL schema changes through automated pull‑request workflows, providing code snippets and detailed screenshots for each step.
