This article explains why SQL queries can become slow, outlines MySQL's internal architecture, demonstrates how to use the EXPLAIN statement to view execution plans, and interprets key fields such as type, key, and Extra to help developers identify and optimize performance bottlenecks.
This article introduces Presto, an open‑source MPP SQL engine, explains its coordinator‑worker architecture and connector model, and provides detailed storage, query, and join optimization strategies—including in‑memory parallelism, dynamic plan compilation, and practical SQL code examples—to achieve low‑latency, high‑performance analytics on big data.
This article introduces the open‑source pg_recovery tool for PostgreSQL, explains how to install it, and demonstrates recovering data lost through UPDATE, DELETE, ROLLBACK, and DROP COLUMN operations with practical command‑line examples.
When a table grows beyond ten million rows, sharding it across multiple databases improves performance, but the usual LIMIT offset, pagesize pagination breaks, leading to missing or incorrect records; this article examines why simple merge approaches fail and evaluates global, secondary query, and no‑skip paging strategies, highlighting their trade‑offs.
This article reviews MySQL horizontal partitioning, explains five partitioning strategies (HASH, RANGE, KEY, LIST, COMPOSITE), demonstrates how to create and test partitioned tables, compares performance with and without indexes, and covers partition management operations such as adding, dropping, and rebuilding partitions.
This tutorial explains how to use Apache Flink 1.12 to construct a unified streaming‑batch data warehouse by integrating Hive via HiveCatalog and HiveDialect, performing read/write operations, configuring upsert‑Kafka sinks, and leveraging Flink CDC connectors for change data capture from MySQL and other sources.
This article explains why MyBatis foreach batch inserts can become extremely slow when inserting thousands of rows, analyzes the underlying SQL and PreparedStatement overhead, and demonstrates how using ExecutorType.BATCH together with a limited batch size (20‑50 rows per statement) dramatically improves insertion speed.
This tutorial shows how to quickly construct a streaming ETL pipeline that captures changes from MySQL and PostgreSQL using Flink CDC, enriches order data with product and shipment information, and writes the results into Elasticsearch for real‑time visualization in Kibana.
This article explains why indexes are vital for MySQL performance, how to decide when to add ordinary, composite, prefix, or unique indexes, the pitfalls of using non‑sequential primary keys, and advanced optimizations such as change buffer, index condition pushdown, and MRR to reduce I/O.
The article details the design, architecture, core features, scheduling logic, and performance gains of Vipshop’s self‑built Hera data service, which unifies data‑warehouse access, supports multiple engines, adapts SQL execution, and dramatically improves SLA for both B‑to‑B and B‑to‑C workloads.
The SQLE 1.2111.0 release introduces online DDL support, LDAP login, custom database extensions, PostgreSQL and Oracle audit plugins, several UI optimizations, and a long list of bug fixes addressing rollback generation, configuration password changes, CI triggers, and plugin rule execution.
This article examines seven typical scenarios where MySQL indexes fail—such as OR conditions, leading wildcards, missing quotes, functions, NULL checks, arithmetic on indexed columns, and composite index order—explaining why they happen and offering practical solutions to maintain query performance.
This article explains how MySQL InnoDB partition tables can dramatically boost query speed, simplify routine operations, enable parallel writes, and provide efficient data exchange and cleanup compared to non‑partitioned tables, illustrated with concrete SQL examples and performance metrics.
This article presents fifteen practical SQL optimization techniques—including avoiding SELECT *, using UNION ALL, leveraging IN/EXISTS, batching inserts, applying LIMIT, controlling IN list size, incremental queries, efficient pagination, preferring JOINs, limiting join tables, managing indexes, choosing proper column types, improving GROUP BY, and using EXPLAIN—to help developers dramatically improve query speed and resource usage.
This article presents fifteen practical SQL optimization tips—including avoiding SELECT *, preferring UNION ALL, leveraging small‑table‑driven queries, batching inserts, applying LIMIT, handling large IN lists, incremental fetching, efficient pagination, join strategies, index management, field‑type choices, and using EXPLAIN—to dramatically improve MySQL query speed and resource usage.
This article introduces MySQL 8.0's enhanced JSON capabilities, covering storage constraints, utility functions, path expressions, in‑place updates, indexing, merging, extraction operators, and the JSON_TABLE function, with clear code examples for each feature.
This article introduces chiner, an open‑source, cross‑platform database design tool built with React, Electron, and Java, compares it with PowerDesigner, walks through installation, core concepts, table and relationship management, and shows import/export capabilities for efficient schema development.
This article explains why MySQL queries become slow, how index design and common pitfalls affect performance, introduces MDL locks and large‑table strategies, and then compares Elasticsearch and HBase as complementary solutions for fast search and massive data storage.
This article demonstrates how to use Fluent Mybatis, native Mybatis, and Mybatis‑Plus to implement a complex student‑score aggregation query, compares their code structures, generation settings, and highlights the advantages and drawbacks of each approach for backend development.
After a colleague accidentally dropped a production database, we revoked DROP privileges for a MySQL user and systematically tested how this restriction impacts common operations such as renaming tables, using TRUNCATE, deleting rows, dropping objects, and recovering data via binlog, revealing surprising behaviors and practical workarounds.
This article explains MySQL's character encoding workflow, illustrates why queries containing Chinese characters fail without proper settings, and shows how to configure JDBC URLs, server variables, and Docker‑based MySQL instances to ensure lossless UTF‑8 handling.
The November 2021 DB-Engines popularity ranking, based on October data, reveals shifts among the top 20 databases, highlights score drops for MySQL, SQL Server and MongoDB, growth for PostgreSQL and MongoDB, and explains the five metrics used to calculate these rankings.
This article explains how to remove duplicate rows in SQL by using DISTINCT, GROUP BY, and the ROW_NUMBER window function, compares their syntax and performance, and provides concrete examples with a Task table and a Test helper table.
This article explains why duplicate rows appear in MySQL tables, demonstrates how to identify them with SELECT queries, and provides step‑by‑step SQL solutions—including safe deletion of all duplicates or retaining a single record per group—using subqueries and temporary tables for efficient cleanup.
This article presents fifteen practical SQL optimization tips—including avoiding SELECT *, using UNION ALL, driving large tables with small ones, batch operations, LIMIT usage, index control, proper data types, and efficient GROUP BY—to help developers improve query speed, reduce resource consumption, and avoid common performance pitfalls.
This article explains MySQL index fundamentals, classifies indexes by data structure, storage engine, field characteristics and column count, compares B+Tree with B‑Tree, hash and red‑black trees, and demonstrates practical creation, usage, and optimization techniques with SQL examples and diagrams.
This article demonstrates how to implement a student score statistics query—calculating count, minimum, maximum, and average scores per term and subject—using Fluent MyBatis, native MyBatis, and MyBatis‑Plus, comparing code complexity, configuration, and generated code across the three approaches.
This article demonstrates how to create and use a sample employees table, explains composite index structures, and provides ten concrete MySQL index best‑practice guidelines—covering full‑value matches, leftmost‑prefix rules, avoiding functions, range conditions, covering indexes, inequality, NULL checks, leading wildcards, type conversions, and OR usage—each illustrated with EXPLAIN output and code examples.
An engineer’s oversight in a PlanetScale schema—omitting a foreign‑key‑based index—led to massive row‑scan costs, prompting a $5,000 bill, while the article also critiques junior developers’ naive schema designs and offers advice on proper database indexing and mentorship.
This article provides a detailed introduction to MyBatis, covering its relationship with JDBC, core components, execution flow, configuration, design patterns, caching strategies, plugin mechanisms, logging options, dynamic SQL features, and includes complete Java and XML code examples to illustrate practical usage.
This article explains the fundamentals of MySQL indexes, focusing on B+Tree structures, differences between MyISAM and InnoDB implementations, practical indexing strategies, configuration and SQL tuning tips, and provides a detailed case study with EXPLAIN analysis to help developers design efficient indexes.
The SQLE 1.2111.0-pre1 preview release introduces plugin-based support for arbitrary databases, provides a PostgreSQL plugin example, includes API adjustments, fixes a rule‑passing bug, and offers links to the source repository, documentation, and release information for users and administrators.
This article provides a comprehensive overview of Snowflake and Delta Lake, detailing their cloud‑native architectures, storage‑compute separation, transaction support, performance optimizations, and a side‑by‑side comparison of their capabilities, openness, and real‑time data handling.
This guide walks through SQL’s language categories—DDL, DML, DQL, DCL, and TCL—then demonstrates practical database and table management commands such as creating and dropping databases, defining tables with various constraints (not‑null, default, primary/foreign keys, unique, auto‑increment), and modifying or copying fields and tables.
This article explains how MySQL's SELECT ... FOR UPDATE statement adds a pessimistic lock, detailing when it results in row-level locking versus table-level locking based on the presence of indexes or primary keys, and demonstrates the behavior with multiple practical examples.
This article demonstrates how to extend MyBatis‑Plus to support ClickHouse-specific SQL operations by creating custom enum definitions, method classes, a SQL injector, a super mapper interface, and accompanying unit tests, enabling update, delete, and logical delete functionalities that differ from standard MySQL behavior.
This article explains the end‑to‑end big data processing pipeline, outlines the diverse challenges of data interfaces, storage and performance, introduces the unified "Three Ones" approach, details a three‑layer service architecture, and shows how low‑code orchestration can streamline API creation and composition.
The pre‑release version 1.2110.0‑pre1 of the open‑source SQL audit tool SQLE is announced, detailing its project overview, repository links, new feature (Online DDL support), UI optimizations, and bug fixes, followed by promotional links and unrelated personal commentary.
This article outlines the principles, recommended practices, and detailed steps for upgrading MySQL from 5.7 to 8.0, covering version compatibility, major 8.0 feature changes, pre‑upgrade checks, in‑place and logical migration procedures, and essential configuration adjustments with example commands.
This article explains MySQL InnoDB lock types, analyzes common deadlock scenarios with detailed SQL examples, describes the underlying locking mechanisms such as next‑key and gap locks, and provides practical prevention and resolution techniques for developers working with transactional databases.
This article explains how the MySQL system variable explicit_defaults_for_timestamp controls the default and NULL handling of TIMESTAMP columns, demonstrates the differences between OFF and ON settings with practical tests, and discusses why the parameter is being deprecated for stricter time‑data management.
This article explains MySQL index fundamentals, the B+Tree structure, various index types, the concept of covering indexes to avoid row look‑ups, the left‑most prefix rule, and presents three real‑world optimization cases that illustrate how to design effective composite indexes, reduce index size, and improve query performance while avoiding costly table scans and sorts.
This article demonstrates how to use Fluent MyBatis, native MyBatis, and MyBatis‑Plus to implement a statistical query on a student_score table, compares their code complexity and generation settings, and provides practical code examples and a summary of each framework’s advantages and drawbacks.
This article explains how Flink SQL implements Top‑N queries, shows the standard SQL syntax, analyzes the logical and physical execution plans generated by the optimizer, and details the internal Rank node, optimization rules, state handling, and configuration options for efficient stream processing.
This article introduces MySQL 8.0’s enhanced JSON capabilities, covering data types, storage limits, indexing, core functions like JSON_SET, JSON_EXTRACT, path expressions, merging, and the JSON_TABLE feature, while providing practical code examples for developers.
This article introduces three MyBatis batch insertion techniques—looped single inserts, MyBatis‑Plus batch inserts, and native SQL batch inserts—provides complete implementation code, performance test results for inserting 100,000 records, analyzes the causes of differences, and offers practical solutions to common pitfalls such as MySQL packet size limits.
This article explains MySQL's native horizontal sharding using MERGE tables, covering the concepts of horizontal versus vertical splitting, table‑level sharding and partitioning, practical examples, advantages, limitations, and suitable use cases for MERGE tables.
After a client mistakenly imported 60,000 useless rows and a developer’s faulty delete command erased over 100,000 critical records, this guide explains how to use MySQL binlog to locate, extract, and replay the missing SQL statements, restore the data, and prevent future disasters.
When a MySQL table is sharded into multiple databases or tables, the usual LIMIT offset, pagesize pagination breaks, and this article examines why simple merge fails, then presents three practical approaches—global limit, secondary queries, and jump‑page restriction—highlighting their trade‑offs and limitations.
This article explains the fundamentals and structures of database indexes—including binary trees, red‑black trees, B‑Tree, B+Tree, hash indexes—and details how MySQL’s InnoDB and MyISAM engines implement clustered and non‑clustered indexes, covering their characteristics, storage files, and query behavior.
This article explores the evolution of data lakes, compares major cloud providers' lake architectures, introduces the emerging lakehouse concept, and provides a step‑by‑step Flink‑Iceberg implementation—including dependencies, catalog setup, table creation, checkpointing, and Kafka ingestion—demonstrating practical big‑data streaming solutions.
The article explains Snuba’s multi‑stage query processing pipeline—from parsing legacy and SnQL languages into an AST, through logical validation and transformation, storage selection, translation, physical optimization, and final ClickHouse execution—highlighting how each step optimizes queries and safeguards the infrastructure.
This article explains how to integrate the Dataway interface‑configuration tool into a Spring Boot project by adding Hasor dependencies, configuring Dataway properties, creating required database tables, setting up the data source, wiring Hasor modules, enabling Hasor in the application, and finally creating and testing a Dataway API without writing any business code.
This article provides a comprehensive overview of Apache Flink's major releases from version 1.7 to 1.14, detailing new APIs, state management improvements, Kubernetes integration, SQL and Table API enhancements, checkpointing advances, and performance optimizations that together illustrate the platform's evolution for both streaming and batch processing workloads.
This article explains why MySQL LIMIT deep pagination becomes slow with large offsets and presents four practical optimization methods—including subqueries, delayed INNER JOIN, tag‑recording, and BETWEEN…AND—demonstrated with real‑world production examples.
This article introduces MySQL 8.0's enhanced JSON capabilities, covering key constraints, storage functions, GeoJSON support, indexing, in‑place updates, essential JSON utilities, path expressions, extraction, modification techniques, and the powerful JSON_TABLE feature with clear code examples.
The October DB‑Engines popularity ranking shows the same top‑10 databases as September, highlights score gains for MySQL and PostgreSQL, notes a year‑over‑year decline for the three leading systems, and reveals Snowflake’s rise into the top‑20, all based on five key popularity indicators.
This article explains MySQL indexes, covering their definition, benefits, various types such as B‑tree and B+‑tree, practical techniques for creating high‑performance indexes like prefix, composite, and clustering indexes, and how to inspect and maintain index information and fragmentation.
This article recounts a real-world incident where a client mistakenly imported 60,000 rows of erroneous Excel data into a MySQL production database, leading to an accidental bulk delete, and explains how the team used MySQL binlog to recover the lost data while offering practical safeguards to prevent similar disasters.
This article compares Fluent Mybatis, native Mybatis, and Mybatis Plus by implementing a student score aggregation query, showing code examples, generation settings, and a summary of advantages and drawbacks to help developers choose the most efficient ORM for complex SQL operations.
This article explains how MySQL implements trusted IP access control via user@host definitions, demonstrates creating and updating user accounts and associated metadata tables, and provides step‑by‑step procedures—including DCL commands, metadata updates, and handling of stored objects—to safely modify trusted IPs in production environments.
StarRocks 1.18 introduces Lateral Join + Unnest for elegant row‑to‑multiple‑row conversion, Fast Decimal with up to 38‑digit precision and 4× faster arithmetic, extensive Bitmap function enhancements, and a suite of import, storage, operator, and ecosystem optimizations that dramatically boost query performance and scalability.
The article examines a MySQL optimizer bug where a query with a WHERE clause on uid, ORDER BY id ASC, and LIMIT 1 incorrectly triggers a full table scan despite a usable composite index, explains the optimizer’s cost calculations, and offers workarounds such as FORCE INDEX or a harmless arithmetic trick to force index usage.
This article compares MySQL's INSERT INTO and LOAD DATA INFILE statements, explains their syntax, shows practical examples, highlights when each should be used for bulk data loading, discusses error handling with IGNORE, and explains why LOAD DATA INFILE is typically faster.
This article explains how MySQL 8.0 introduces strict JSON schema validation, demonstrates the json_schema_valid and json_schema_validation_report functions with array and object examples, and shows how to enforce schema checks in tables using CHECK constraints.
This article examines the business storage requirements of large‑scale advertising platforms and details how BaikalDB was architected as a cloud‑native, MySQL‑compatible distributed database that combines OLTP, OLAP, KV and full‑text capabilities while ensuring high reliability, low cost and linear scalability.
This guide explains how to deploy Apache Kyuubi on a CDH 6 cluster, replace HiveServer2 with Kyuubi, integrate Spark 3, apply necessary patches, configure environment and Spark settings, and optimize engine sharing for various workloads, providing complete code snippets and step‑by‑step instructions.
This article explains how to improve database insertion performance in Java by replacing per‑iteration inserts with MyBatis‑Plus’s saveBatch method, detailing dependency setup, schema creation, entity, controller, service, and mapper code for efficient bulk operations.
This article explains MySQL index fundamentals, B+‑tree implementation, the left‑most prefix rule, back‑to‑table issues, and provides three practical optimization cases with code examples to help developers design efficient composite indexes and avoid performance pitfalls.
This tutorial walks beginners through the fundamentals of SQL, explaining what it is, how to run simple SELECT statements, create tables, insert, update, delete records, use WHERE clauses, and even generate fun graphics like stars and calendars using advanced queries.
This article explains MySQL index fundamentals, different index structures, the concept of covering indexes and the left‑most prefix rule, and presents three real‑world optimization cases that illustrate how to design effective composite and partial indexes to avoid table scans and improve query performance.
The article analyzes a MySQL optimizer bug where a query with ORDER BY id ASC LIMIT 1 triggers a full‑table scan despite an applicable idx_uid_stat index, explains the cost‑based decision process, and presents two practical work‑arounds—using FORCE INDEX or a harmless arithmetic expression—to ensure the index is used.
This article outlines a comprehensive A/B testing workflow, covering historical issues, business test process improvements, detailed implementation steps, SQL validation scripts, data verification in analytics platforms, and practical notes to ensure accurate experiment data collection and analysis.
This article shares NetEase’s experience building a user‑profile platform with ClickHouse, detailing the business background, challenges of massive data and complex queries, core table designs, data ingestion, bitmap techniques, performance gains, and future plans for scaling and optimization.
The latest DB‑Engines monthly ranking shows the top nine databases unchanged, highlights a swap between Microsoft Access and Cassandra, notes declines for Microsoft SQL Server and MySQL, and analyzes popularity trends that suggest PostgreSQL and MongoDB may soon surpass SQL Server, illustrated with detailed charts.
This guide walks through exporting business databases, re‑initializing the MySQL instance, importing data, extracting GTID information, creating replication and monitoring accounts with helper functions, resetting logs, and finally re‑joining the node to a MySQL Group Replication cluster.
This article presents a concise guide to the most frequently used MySQL commands, covering how to list databases and tables, create and drop databases and tables, and perform essential CRUD operations such as inserting, querying, updating, and deleting records.
This article explains why GUI tools like phpMyAdmin may fail to display database lists when connecting to DBLE, outlines step‑by‑step troubleshooting using packet capture or the DBLE general log, and details the configuration parameters, commands, performance impact, and implementation mechanism of the general log.
This article summarizes the most useful MySQL 8.0 JSON features—including storage limits, indexing, in‑place updates, path expressions, merging, and table functions—providing clear code examples and guidance for developers who need to work with JSON data in MySQL.
The article explains why using LIMIT with a large OFFSET in MySQL causes severe performance degradation, demonstrates the issue with a 5‑million‑row table, and shows how rewriting the query as a join dramatically reduces I/O and execution time while also discussing buffer‑pool effects.
The September 2021 DB‑Engines ranking shows Oracle, MySQL and SQL Server plummeting while MongoDB, Snowflake and ClickHouse surge, highlighting a shift toward niche and cloud‑native databases, and the article also recommends ten essential database books for professionals.
This article details NetEase Games’ evolution of its Flink SQL platform, from the early StreamflySQL v1 template‑JAR approach to the v2 SQL‑Gateway architecture, discussing design decisions, challenges such as metadata persistence, multi‑tenant security, horizontal scaling, and job state management.
This article compares Elasticsearch and ClickHouse in terms of architecture, query capabilities, and performance for log analytics, presenting test setups, Docker‑compose configurations, query examples, and benchmark results that show ClickHouse generally outperforms Elasticsearch in most basic query scenarios.
The September 2021 DB‑Engines ranking shows major declines for Oracle, MySQL and SQL Server, rapid gains for Snowflake and MongoDB, and highlights emerging time‑series and key‑value databases like InfluxDB, Redis, and ClickHouse reshaping the database market.
The article explains ClickHouse's new Projection feature, how it overcomes MergeTree's single‑sort limitation and materialized view drawbacks, provides step‑by‑step commands to create, materialize, and query projections, demonstrates massive performance gains, and outlines the rules for automatic projection selection.
This article provides a comprehensive guide to Flink Table and SQL window semantics—including group, tumbling, sliding, and session windows—covers over windows, demonstrates how to define windows in SQL, explains built‑in functions, shows how to implement scalar, table, aggregate and table‑aggregate UDFs, and details Flink's integration with Hive, complete with Maven dependencies and runnable examples.
This article explains why duplicate rows appeared in production MySQL tables, demonstrates how to identify them with SELECT queries, and provides two SQL solutions—one to delete all duplicates and another to keep a single record per duplicated key—while preserving data integrity.
This guide explains how LEFT JOIN creates a temporary result set, compares the effects of applying a WHERE filter after the join versus embedding the condition in the ON clause, and shows practical SQL examples with sample tables and result analysis.
This article provides a comprehensive guide to Apache Flink's Table API and SQL, covering required dependencies, the differences between old and Blink planners, program structure, table environment creation, catalog registration, query execution, conversion between DataStream and Table, update modes, and time attribute handling, with Scala code examples throughout.
This guide explains how multi‑index support in Table Store enables SQL push‑down of filters, relational operators, aggregation functions, and LIMIT clauses, dramatically reducing query latency on non‑primary‑key columns and providing practical examples with a bike‑rental dataset.
This article explains the impact of slow SQL on database resources and user experience, outlines priority rules for addressing slow queries, details MySQL execution steps, identifies key performance factors, and provides concrete optimization methods including enabling slow‑query logs, indexing, I/O merging, and distributed architecture.
This article examines how Oracle, PostgreSQL, and MySQL implement and use database sequences for primary keys, covering explicit calls, trigger‑based, DEFAULT, and AUTO_INCREMENT methods, version support, and the effects of INSERT, UPDATE, DELETE, and TRUNCATE operations, concluding with a comparative summary.
This comprehensive guide covers essential SQL concepts, clause usage, aggregate functions, view creation, sequences, indexes, and a collection of classic interview questions with detailed solutions, helping readers deepen their database knowledge and ace SQL interviews.
This article explains why mastering SQL and PostgreSQL is essential for data scientists, outlines the core skills of the role, describes PostgreSQL’s features, lists its advantages and drawbacks for data science, and suggests resources for getting started.
This article explores TiDB performance optimization by examining layered approaches—hardware, software, and SQL—and scenario‑specific tactics such as read/write hotspot handling, OLAP challenges, and distributed transaction tuning, offering practical insights and real‑world case studies from industry experts.
This article provides a thorough introduction to MyBatis, including a complete beginner example with database schema, Maven configuration, XML and Java mapper files, and a demo program, followed by an in‑depth explanation of MyBatis’s lifecycle, core components such as SqlSession, Executor, StatementHandler, ParameterHandler, ResultSetHandler, and their interactions.
The article presents a step‑by‑step MyBatis tutorial that creates a sample user table, adds Maven dependencies, configures MyBatis, defines mapper XML and Java interfaces, demonstrates a test program, and explains core components such as SqlSession, Executor, StatementHandler, ParameterHandler, and ResultSetHandler.
Geohash encodes latitude‑longitude pairs into short base‑32 strings by recursively bisecting coordinate ranges and interleaving bits, allowing fast proximity queries via prefix matching, with precision controlled by string length, and is supported natively in Redis and useful for location‑based services.
This article explains the purpose and interpretation of each column in MySQL's EXPLAIN output, covering identifiers, select types, table sources, partition info, access types, possible keys, actual keys, key lengths, row estimates, filtering percentages, and extra execution details, with visual examples and code snippets.
The article explains why TiDB discourages large transactions, outlines the historical transaction limits, shows how to configure size thresholds, and provides concrete scripts for splitting big updates based on primary‑key characteristics to keep transaction size within safe bounds.
