This article explains why a MySQL join query may ignore an index because of implicit character‑set conversion, demonstrates how to diagnose the issue with EXPLAIN and schema queries, and provides a fix by converting the column to UTF8 in the SQL statement.
Learn ten practical strategies—from avoiding chat tools for SQL to using logical deletes, backups, and proper field management—to dramatically reduce the risk of costly database mishaps in production environments, ensuring safer data operations and easier recovery.
This comprehensive guide explains what MyBatis is, its advantages and drawbacks, suitable use cases, differences from Hibernate, parameter handling, dynamic SQL, pagination, caching, lazy loading, and how to use mapper interfaces, XML mappings, and plugins with clear code examples.
The article shows how to generate a Node.js V8 heap snapshot with v8.getHeapSnapshot, explains the JSON‑like .heapsnapshot structure of nodes, edges, and strings, and demonstrates using the heapquery tool to import the data into SQLite for SQL queries that locate and trace memory‑leaking objects such as a HugeObj instance.
This article explains the upsert‑kafka connector in Flink, its configuration parameters, step‑by‑step usage with SQL examples, and demonstrates a complete pipeline that reads Kafka streams, aggregates page view metrics, and synchronizes the results to TiDB in real time.
A developer discovers that massive OFFSET values in MySQL pagination cause full‑table scans and severe latency, then reproduces the issue with simulated data, benchmarks the slowdown, and presents three practical solutions—including index‑covering subqueries, cursor‑based pagination, and offset limits—to restore query performance.
This article explains what a prefix (partial) index is in MySQL, why it can reduce index size and improve query speed, outlines suitable scenarios and limitations, and demonstrates performance differences through a series of tests with varying prefix lengths on a large test table.
This article explains how to use PostgreSQL's row-level and column-level security features to restrict user access to specific rows and columns, providing step‑by‑step examples, code snippets, and discussion of policies, BYPASSRLS, and performance considerations.
This article explains the different SQL join types—including equi‑join, natural join, inner join, self‑join, outer joins, and cross join—detailing their syntax, behavior, and practical usage with example queries and optimization tips.
This article explains how to manage database write and query operations in PHP using PDO, covering initialization, error handling, and providing complete code examples for inserting and selecting records.
This article explains how MySQL versions prior to 8.0 automatically sorted GROUP BY results, why the optimizer added this hidden ordering, how the behavior was removed in MySQL 8.0, and what developers must watch for when migrating, illustrated with concrete SQL examples and EXPLAIN output.
This article explains how Flink SQL can be used to construct a unified stream‑batch ETL pipeline for data warehouses and data lakes, covering data integration, CDC support, streaming writes to Hive and Iceberg, and various join techniques such as regular, interval, and temporal joins.
This article reviews the history of MySQL, introduces MariaDB as its open‑source fork, and presents a series of benchmark tests—including single‑row inserts, batch inserts, and various query scenarios—showing that MariaDB generally outperforms MySQL in speed while using more memory.
An in-depth case study describes how to dramatically improve the performance of a massive MES reporting query—reducing execution from over half an hour to seconds—by analyzing inefficient SQL, adding proper indexes, avoiding full table scans, redesigning with stored procedures, temporary tables, and selective use of NOLOCK.
During a holiday‑time alert, a seemingly harmless pair of UPDATE statements on the same InnoDB table caused a deadlock, and the article walks through log analysis, reproduction steps, lock mechanics, and practical ways to avoid such deadlocks in MySQL.
This article explains in detail how ClickHouse processes a user‑submitted SQL query, covering the server’s request handling, parsing, query rewrite, optimization, interpreter execution, and result transmission, while illustrating key source code snippets and architectural components.
This article provides a comprehensive overview of ClickHouse's table engines, explaining the purpose and characteristics of each engine series—including Log, Integration, Special, and the various MergeTree families—while offering detailed SQL examples and guidance on when to use each engine for different data workloads.
This article compiles fifty‑two actionable tips for improving SQL query performance, covering index creation, query rewriting, use of temporary tables, proper data types, server configuration, and best practices for MySQL and other relational databases.
This article translates and expands on a HikariCP wiki post, explaining why smaller database connection pools often yield better performance, presenting benchmark videos and data, describing the underlying CPU, disk, and network constraints, and offering a simple formula to calculate an optimal pool size for typical server configurations.
This article explains how incorrectly placed double‑quotation marks in MySQL UPDATE and SELECT statements cause implicit type conversion, turning intended string assignments into zero values and making WHERE clauses always true, leading to massive data corruption and unexpected query results.
This article examines several MySQL pagination techniques—including basic LIMIT, subquery‑based, ID‑range, and temporary‑table methods—provides detailed performance measurements on a multi‑million‑row table, and offers practical recommendations for fast and scalable data retrieval.
This article introduces Apache IoTDB, a high‑performance time‑series database for IoT, covering its key features, overall architecture, installation procedures, command‑line operations, and JDBC usage with example code to help newcomers quickly start storing and querying sensor data.
The article explains the three main types of SQL statements—DDL for defining database objects, DML for manipulating data, and DCL for controlling access—detailing their purposes, common keywords, and typical usage scenarios in relational database systems.
This article explains how MySQL treats NULL values, why columns defined with NULL can invalidate indexes, demonstrates the behavior of IS NULL, IS NOT NULL, the spaceship operator, IFNULL, COUNT, DISTINCT, GROUP BY and ORDER BY with NULLs, and offers practical recommendations for schema design.
This article compiles fifty‑two practical SQL tuning strategies—including proper indexing, query rewriting, avoiding full table scans, using temporary tables, managing locks, and choosing the right storage engine—to help developers dramatically improve database query performance and scalability.
This article introduces DBeaver, a free open‑source, Java‑based universal database client that supports a wide range of databases via JDBC, outlines its rich features, and provides step‑by‑step installation and connection setup instructions for Windows, macOS, and Linux.
The article details Apache Iceberg 0.11.0's core enhancements—including partition changes, SortOrder, extensive Flink and Spark integrations, CDC/Upsert support, hash‑based write distribution to reduce small files, and upcoming 0.12.0 roadmap—while providing practical SQL and API examples for data‑lake practitioners.
This article compiles 52 practical SQL tuning strategies—including index best practices, query rewriting, use of temporary tables, proper data types, and server configuration tips—to dramatically improve MySQL query speed and overall database efficiency.
This article analyzes the performance bottlenecks of PageHelper's pagination in MyBatis, demonstrates how large offsets degrade MySQL query speed, and presents a more efficient sub‑query join solution along with suggestions for refactoring the interceptor to improve backend pagination efficiency.
This article explains Presto’s core architecture and low‑latency query execution process, describes how Youzan adopts Presto for various data‑platform scenarios, discusses the evolution of its deployment, and outlines the performance challenges and future enhancements such as Alluxio integration and session property management.
This article compiles 52 practical SQL performance‑tuning strategies—including indexing rules, query‑writing shortcuts, join optimizations, lock handling, temporary tables, storage‑engine choices, data‑type recommendations, backup tips, and useful code examples—to help developers dramatically improve query speed and overall database efficiency.
This article demonstrates how to create a test table, build indexes, and analyze their effectiveness across multiple query scenarios using MySQL's EXPLAIN output, highlighting the best‑left‑prefix rule, range condition impacts, ORDER BY considerations, and common pitfalls such as Using filesort and temporary tables.
This article traces the fifty‑year history of relational databases, from early storage systems and the IDS and CODASYL network model to Codd’s relational model, the rise of SQL, its standards, competitors, and the principles that have kept SQL dominant in modern data management.
This article explains why a small table should drive a large table in SQL queries, demonstrates data preparation, compares IN and EXISTS performance when the small table is either the inner or outer query, and concludes with practical guidelines for choosing between IN and EXISTS.
This article traces MySQL’s evolution, introduces MariaDB’s origins, and presents a comprehensive benchmark comparing insert and query performance of MySQL 8.0.19 and MariaDB 10.4.12 on identical hardware, highlighting speed differences with and without indexes.
This article presents a comprehensive collection of 52 practical tips for optimizing SQL queries, covering index usage, query rewriting, data type selection, storage engine choices, transaction handling, and backup strategies to improve database performance and reduce resource consumption.
This article introduces DBeaver, a free open‑source, cross‑platform database management tool that supports numerous databases via JDBC, outlines its rich features such as data editors, ER diagrams, import/export, and provides step‑by‑step installation instructions for Windows, macOS, and Linux, plus tips for creating connections.
This guide explains how to download, install, and configure DBeaver, connect to various relational and NoSQL databases via JDBC, manage drivers manually when needed, and generate entity‑relationship diagrams directly from existing database schemas.
Testing shows that omitting single quotes around a string primary key in MySQL queries can cause a full table scan and increase execution time by about 100‑fold, while quoting the value enables index usage and dramatically improves performance, a difference that disappears when the column is an INT.
This article investigates why the Alibaba Java Development Manual advises against joining more than three tables by designing and executing large‑scale MySQL and Oracle experiments, analyzing query performance, indexing effects, and data‑generation scripts to determine the practical limits of multi‑table joins.
This guide walks through setting up Apache Zeppelin as a low‑cost, SQL‑centric development platform for Flink, covering environment preparation, installation, interpreter configuration, execution modes, verification, common pitfalls, dimension‑table joins, custom UDFs, Redis integration, and dual‑stream join techniques.
This article, presented by Tencent senior engineer Du Li, details the current state of Flink SQL, compares Jar, Canvas, and SQL modes, introduces window‑function extensions, retract‑stream optimizations, and outlines future roadmap plans for cost‑based optimization and new features in the real‑time computing platform.
This article describes how JD's Energy Management Platform leverages ClickHouse as a high‑performance, MPP‑based OLAP engine to provide real‑time, multi‑dimensional analytics on IoT energy data, covering business background, technology selection, system architecture, data ingestion, storage, replication, and a generic query interface with code examples.
This article explains why correlated subqueries are a performance bottleneck, introduces the Apply (Correlated Join) operator, and presents a comprehensive set of transformation rules—including basic unnesting, Project/Filter push‑down, Aggregate handling, and Set‑operation rewrites—to convert subqueries into efficient join plans for modern SQL engines.
This article explains three ways to run raw SQL in Django, demonstrates why the built‑in extra method is discouraged, shows how to use the raw manager and the low‑level connection cursor, and provides helper functions that return query results as dictionaries for easier processing.
The article explains why a small database connection pool combined with a large queue of waiting threads yields better performance, illustrates this with Oracle and PostgreSQL benchmark data, and provides a practical formula for calculating the optimal pool size based on CPU cores and effective disks.
This article demonstrates how GPT‑3 can translate plain‑English questions into SQL statements, starting with simple counts and progressing to multi‑table joins, while highlighting prompting techniques, limitations, and the impact of model variants such as GPT‑3 Instruct.
This article explains the difference between logical and physical pagination, demonstrates how MySQL's LIMIT clause works, compares two query methods on a large MEMORY table, analyzes their performance with indexes and EXPLAIN, and provides practical pagination examples and Java code for efficient data retrieval.
The article analyzes a production incident where dozens of MySQL UPDATE statements unintentionally set a column to zero because misplaced quotation marks caused MySQL to interpret the expression as a boolean comparison with implicit type conversion, and it explains how to detect and prevent such errors.
The article investigates the Alibaba Java Development Manual's rule against joining more than three tables by designing and executing extensive MySQL and Oracle experiments, generating massive test data, measuring query performance, and concluding that the restriction stems from join scalability limits on large datasets.
This article explains how to design and implement a flexible, accurate data reporting platform using Elasticsearch aggregation, covering architecture, data synchronization, metric definition, SDK integration, and future scaling considerations for enterprise analytics.
This article introduces the classification of SQL statements and provides practical DDL and DML commands for creating, altering, and querying databases and tables, including examples of creating databases, showing database information, and modifying table structures.
The article reviews Flink 1.12's major enhancements, including comprehensive SQL capabilities, deep integration with Hive via catalog and streaming support, and a practical code example that demonstrates how to write streaming data into Hive tables while handling partition commits and small‑file merging.
This article breaks down nine core MySQL index concepts—including index types, B+Tree advantages, search steps, covering indexes, index invalidation, left‑most prefix rules, index push‑down, and safe index creation on large tables—to help candidates answer interview questions about tree‑search operations and performance trade‑offs.
The article analyzes a real‑world incident where a series of MySQL UPDATE statements with incorrectly placed quotation marks caused the entire source_name column to be set to zero, explains MySQL's implicit type conversion behavior, and shows how to recover the data using binlog analysis and flashback SQL.
This article explains how JD’s Energy Management Platform uses ClickHouse as an MPP‑based OLAP engine to ingest, store, and provide multi‑dimensional real‑time analytics on energy consumption data, covering architecture decisions, data pipelines, replication, sharding, and a generic query interface.
Facing a massive 1500‑node Flink 1.4.2 cluster handling over 12,000 tasks and 30 trillion daily events, we migrated to Flink 1.10, detailing new DDL/Catalog support, SQL enhancements, memory tuning, compatibility patches, extensive testing, and engine optimizations such as task‑load metrics and balanced sub‑task scheduling.
This article provides a detailed walkthrough of ThinkPHP's Db class, explaining how it combines connectors, query builders, and SQL generators to perform database operations, including method calls like __callStatic, table handling, where clause parsing, getLastSql retrieval, and the underlying execution flow.
This article provides an in‑depth overview of Apache Superset, covering its recent graduation to a top‑level Apache project, key visualization features, extensive data source support, step‑by‑step installation via pip or Docker, PostgreSQL connection, role‑based permission model, and practical usage insights.
This article explains MySQL 8.0 window function FRAME clause syntax, differentiates ROWS and RANGE units, and demonstrates various frame boundaries such as CURRENT ROW, UNBOUNDED PRECEDING/FOLLOWING, and expression‑based preceding/following with concrete query examples.
This article explains the background, challenges, and architectural design of a big‑data OLAP platform that integrates Apache Kylin with a BI system, detailing pre‑computation strategies, cube construction, user authentication, storage engines, and query mechanisms to achieve sub‑second analytics on massive datasets.
This article introduces DBeaver, a free open‑source, Java‑based database management and development tool that supports a wide range of databases via JDBC, outlines its rich features, provides installation instructions for Windows, macOS, and Linux, and walks through creating and using a database connection with screenshots.
A developer’s production update accidentally set every row’s source_name to 0 because misplaced double‑quotation marks altered the SQL semantics, and the article explains how MySQL interprets such malformed statements and how to recover the data.
ClickHouse is an open‑source column‑oriented OLAP database developed by Yandex that offers real‑time queries, high performance compared to many traditional databases, a rich set of features, and a modular architecture including columns, data types, blocks, parsers, functions, and various table engines.
This article explains the concept, creation, and performance benefits of MySQL prefix indexes, demonstrates how to choose an optimal prefix length using selectivity calculations, and presents practical techniques for handling both prefix and suffix search patterns.
This article introduces DBeaver, a Java‑based, cross‑platform, open‑source database client that supports a wide range of JDBC and non‑JDBC data sources, outlines its key features, provides installation steps for Windows, macOS, and Linux, and shows how to create and use database connections.
The article explains why SELECT * slows down MySQL queries by increasing parsing cost, network traffic, and preventing index covering, and demonstrates how explicit column selection and well‑designed composite indexes can dramatically reduce I/O and improve query speed.
When you combine ORDER BY with LIMIT in MySQL, the result set may appear in a different order than expected, especially if the ordered column contains duplicate values, and understanding the optimizer's behavior and adding deterministic columns can resolve this issue.
This article explains the data structures behind MySQL indexes, demonstrates how B+Tree indexes are built, and shows why the optimizer may choose full‑table scans over index scans in various scenarios such as pattern matching, OR conditions, type conversion, and NULL handling.
This article explains how to list MySQL databases, perform full, multiple‑database, single‑database, and table‑specific backups using mysqldump, and restore them correctly, highlighting the differences when using the --databases option and common pitfalls.
This article provides a comprehensive introduction to ClickHouse, an open‑source column‑oriented OLAP database, covering its high‑performance benchmarks, core architectural components, query processing model, deployment patterns, Java client usage, and real‑world implementations at large enterprises.
This article introduces DBeaver, a free open‑source, Java‑based universal database management and development tool, outlines its rich features, supported databases, and provides step‑by‑step installation and connection setup instructions for Windows, macOS, and Linux environments.
The article explains how incorrectly placed quotation marks in MySQL UPDATE statements can cause expressions like "str_col=\"xxx\" = \"yyy\"" to evaluate to zero, leading to unintended data overwrites and highlights the importance of testing SQL in safe environments.
DiDi upgraded its massive real‑time computing engine from Flink 1.4.2 to Flink 1.10, implementing a transparent migration across 1500 machines, adding native DDL, binary rows, MiniBatch, improved scheduling and window functions, and establishing a rigorous testing pipeline that achieved 99.9 % compatibility while preventing OOM issues.
This article explains the functional and performance differences among SQL count(1), count(*), and count(column) expressions, including how they handle NULL values and when each method is most efficient, illustrated with a MySQL example.
This article explains several efficient methods—including shell scripts, generated series tables, JSON functions, and MySQL 8.0 recursive CTEs—to quickly locate missing auto‑increment primary key values in a MySQL table, comparing their performance and suitability.
This article walks through three progressively better implementations for a complex e‑commerce search API—starting with a monolithic SQL query, then splitting the query and adding indexes, and finally using Redis sets and sorted sets to cache filter results, handle pagination, and achieve production‑grade performance.
This guide explains MySQL GTID concepts, how to enable GTID-based replication, configure the server, create replication users, initialize a slave, and verify replication using command-line examples and configuration steps, including transaction commit and rollback testing.
This guide explains how to integrate Flink 1.12 with Hive using HiveCatalog, covering required dependencies, Blink planner configuration, SQL dialect switching, Hive UDF support, temporal table joins, and provides complete code snippets for a streaming‑batch unified data warehouse solution.
This article explains why SQL performance is critical for fast‑response web services, presents database‑agnostic optimization techniques—such as preferring EXISTS over IN, using JOINs, avoiding unnecessary ORDER BY, leveraging UNION ALL, and applying indexes—to dramatically reduce query latency and resource consumption.
This article provides a step‑by‑step tutorial on configuring MySQL master‑slave replication, covering the underlying principles, master and slave configuration files, user creation, data backup, log synchronization, and verification with example commands and code snippets.
This guide provides step‑by‑step Oracle commands for creating and dropping databases, configuring users and tablespaces, performing full and table‑level backups with expdp/impdp, handling version compatibility, and cleaning up residual files and sessions.
This article compiles 52 practical SQL performance‑tuning strategies—including index best practices, query rewrites, use of temporary tables, proper data types, and server configuration tips—to help developers dramatically speed up MySQL and other relational database queries while avoiding common pitfalls.
This article compiles 52 practical SQL optimization strategies—including index usage, query rewriting, avoiding full table scans, proper data types, and server configuration tips—to help developers dramatically improve query speed and overall database performance.
This guide explains MySQL's three levels of user permissions, shows how to create accounts with various host specifications, demonstrates how to delete users, list existing accounts, and display a specific user's granted privileges using SQL statements.
This article presents five practical rules—return only needed rows, use proper indexes, avoid subqueries, replace OFFSET pagination with key‑based limits, and understand SQL's logical execution order—to help developers write efficient, high‑performance SQL queries across major relational databases.
This article introduces MySQL index basics, covering index concepts, advantages and disadvantages, various index types such as B‑Tree, B+Tree, hash, and full‑text, their storage structures, creation syntax, left‑most prefix and covering indexes, and practical design guidelines for effective indexing.
This guide explains how to become a professional software tester by covering test case creation, essential testing methods, protocol basics, when to adopt automation, key testing tools, UI and API automation scripting, SQL proficiency, and Linux basics for effective testing workflows.
This article explains why SQL query optimization matters and presents five practical rules—return only needed rows, use the right indexes, avoid subqueries, replace OFFSET‑based pagination, and master the logical execution order—to help developers write faster, more efficient database queries across major relational systems.
This guide explains how Flyway automates database schema migrations using version‑controlled SQL scripts, covering its core concepts, naming conventions, command‑line usage, Maven integration, and seamless Spring Boot configuration to keep databases in sync with application releases.
This article presents a comprehensive collection of 52 practical SQL performance optimization techniques, covering indexing, query rewriting, use of EXISTS, avoiding full table scans, proper handling of NULLs, OR/IN clauses, temporary tables, storage engine choices, backup procedures, and other best practices to improve database efficiency.
This article presents 21 practical SQL habits covering safe delete/update usage, testing changes, backups, transaction handling, performance tuning with EXPLAIN and indexes, concise SELECT statements, proper data types, batch operations, and clean coding conventions to make your database work more reliable and efficient.
This article presents five practical rules for optimizing SQL queries—including selecting only needed columns, ensuring proper indexes, avoiding subqueries, using keyset pagination instead of OFFSET, and understanding the logical execution order—along with concrete examples and execution‑plan analysis for major relational databases.
This article explains a real‑world MySQL GROUP_CONCAT truncation problem caused by the default length limit, demonstrates how it affected a doctor‑scheduling system, and provides two practical solutions: increasing the group_concat_max_len setting or adjusting the business logic to reduce concatenated data.
This article explains five common issues caused by NULL values in MySQL columns—data loss with COUNT, DISTINCT, and inequality queries, NULL‑induced NULL results in SUM leading to NPE, increased query complexity, and index behavior—while providing practical SQL solutions and best‑practice recommendations.
This article provides a comprehensive overview of database system design, covering its historical evolution, classification of relational and NoSQL databases, key architectural patterns, consistency models, indexing techniques, storage formats, compression methods, and practical considerations for selecting the right database solution.
This article walks through a MySQL 5.6 scenario where a query that originally took over 30,000 seconds is optimized using proper indexing, sub‑query to join conversion, and composite indexes, demonstrating dramatic performance gains and offering practical tips for future query tuning.
Learn how to design a simple book management database, define Django models for authors, publishers, books and their many‑to‑many relationships, and perform common ORM operations such as retrieving all records, filtering with conditions, using first(), get(), and inspecting the generated SQL.
This article explains the challenges of returning large result sets in APIs, compares traditional offset‑based pagination with cursor‑based approaches, shows performance differences through SQL examples, and discusses why cursor pagination is more efficient for deep paging and concurrent data modifications.
This article explains the concept of historical chain (slowly changing dimension) tables in data warehousing, demonstrates how to create source and target tables, provides a PL/pgSQL stored procedure to handle inserts, updates, and deletions, and shows step‑by‑step testing with sample SQL scripts.
