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.
This article demonstrates how to create two MySQL tables, populate them with sample data, and use various SELECT statements—including inner joins, filtered joins, and UNION operations—to retrieve combined information across the tables.
This guide lists useful MySQL client shortcuts, explains the help command usage, provides common mysqladmin administration examples, and shows how to diagnose high CPU usage by tracing the offending SQL statements through process and performance schema queries.
This comprehensive guide explains MySQL's server and storage engine layers, SQL execution order, BinLog/RedoLog/UndoLog mechanisms, index structures and design principles, transaction isolation levels, lock types, MVCC, buffer pool behavior, table slimming, join strategies, statistics, random queries, and practical optimization techniques for high‑performance database operations.
This article explains why MySQL uses B+‑tree indexes, describes the principles of B‑tree and B+‑tree structures, compares MyISAM and InnoDB index implementations, and provides practical optimization tips such as using auto‑increment primary keys, the left‑most prefix rule, and proper configuration settings.
This article presents a thorough overview of MySQL performance tuning, covering index usage, query rewriting, SELECT statement ordering, DML batch operations, hint directives, and table design recommendations to help developers write efficient SQL for large‑scale systems.
This article explains how proper index creation boosts MySQL query performance, detailing the mechanics of indexes, why B+Tree is chosen over binary and balanced trees, the storage differences between MyISAM and InnoDB, and practical guidelines for designing effective single‑column, composite, and covering indexes.
This article explains data warehouse fundamentals, reviews classic warehouse models such as ER, dimensional, Data Vault and Anchor, then dives deep into dimensional modeling concepts, star and snowflake schemas, and demonstrates a practical e‑commerce scenario with SQL examples and trade‑offs.
This article explains the purpose and usage of MySQL character set system variables, illustrates how they affect client‑server communication and file handling, and provides practical examples and diagrams to help developers correctly configure character sets for optimal performance.
This article presents five practical rules for writing efficient SQL queries—including selecting only needed rows, using proper indexes, avoiding subqueries, replacing OFFSET pagination, and understanding the logical execution order—supported by examples, execution plans, and performance tips for various relational databases.
This article explains how to build a real‑time data processing platform using Flink, covering the Lambda architecture, design approaches, SQL and custom‑Jar task definitions, UI drag‑and‑drop, cluster resource management on Yarn and Kubernetes, submission modes, scheduling, permission and metadata handling, logging, and monitoring with Prometheus and Grafana.
This article explains the fundamentals of SQL parsing, covering lexer and parser mechanisms, context‑free grammar concepts, LL(1) and LR(1) parsing techniques, and demonstrates how Apache ShardingSphere implements a flexible, multi‑dialect SQL parser with features such as formatting, parameterization, and extensibility.
This article presents five practical rules for writing efficient SQL queries—including selecting only needed rows, using proper indexes, avoiding costly subqueries, replacing OFFSET‑based pagination with key‑set pagination, and mastering the logical execution order of SQL clauses—to help developers and DBAs improve query performance across major relational databases.
This article explains MyBatis dynamic SQL features—including if, choose, trim, foreach, and bind tags—by showing how to set up a Maven project, create a student table, write mapper methods, and run tests that demonstrate conditional queries, updates, inserts, batch operations, and database‑agnostic expressions.
Even when a MySQL query uses an index, it can still become a slow query; this article explains index structures, explains why full‑index scans and poor selectivity cause performance issues, and presents step‑by‑step optimization techniques such as index condition pushdown, virtual columns, and better filtering.
This article introduces MyFlash, an open‑source MySQL flashback utility, explains its limitations, provides step‑by‑step installation commands, details the available command‑line options for specifying databases, tables, time ranges and SQL types, and demonstrates a complete rollback test case with binlog parsing and execution.
This article explains how to identify slow‑query symptoms, gather problematic SQL statements, apply common writing and indexing tips, read execution plans, and walk through a complete MySQL optimization case, enabling readers to become proficient database performance engineers.
This article explains the two forms of SQL CASE expressions, demonstrates how to use CASE for concise data aggregation and conditional updates, and provides practical examples with customer and order tables to simplify queries and avoid pitfalls.
The author shares his personal journey from first encountering Flink to becoming an Apache Flink Committer at ByteDance, detailing community involvement, code contributions, bug fixes, lessons learned, advice for newcomers, and concluding with promotional offers for Flink services.
This article explains how MySQL's InnoDB engine applies row‑level locks for DELETE and SELECT statements under various index configurations and isolation levels, covering MVCC, snapshot vs current reads, two‑phase locking, gap and next‑key locks, and the impact of primary, unique, non‑unique, and missing indexes.
This guide provides comprehensive Oracle database commands for creating and deleting databases in 11g/12c, configuring users and tablespaces, and performing full or selective backup and restore operations with Data Pump, along with practical tips for managing sessions, processes, and cleanup.
This article provides concise, interview‑style explanations of MyBatis core concepts—including the difference between #{} and ${}, additional XML tags, DAO interface mechanics, pagination, plugin development, batch operations, executors, lazy loading, enum mapping, and internal configuration mapping—helping developers ace backend Java ORM interviews.
The DBLE 3.20.10.0 release notes detail the open‑source distributed middleware's project overview, major enhancements such as management‑port metadata access and read/write splitting, a comprehensive list of new features, bug fixes, backward‑compatibility changes, and references to past versions and documentation.
This article explains MySQL primary key design principles, compares auto‑increment, UUID and uuid_short approaches, provides SQL examples and performance benchmarks, and offers guidance on choosing business‑related versus unrelated key columns for optimal data integrity and query efficiency.
This article explains the hidden costs of using SELECT * in MySQL queries, explores how unnecessary columns, large data types, and loss of covering‑index opportunities degrade performance, and shows how composite indexes and proper indexing strategies can dramatically speed up data retrieval.
This guide explains how to filter data in MySQL using various query conditions such as WHERE, comparison operators, BETWEEN, IN, NULL checks, logical operators, short‑circuit evaluation, and operator precedence, with clear examples and visual illustrations.
This article examines MySQL pagination strategies for large tables, detailing preparation, general LIMIT‑based pagination, subquery and ID‑range optimizations, performance test results, and practical recommendations for improving query speed on millions of rows.
This article explains common misconceptions about index selectivity and filtering, demonstrates how composite indexes can avoid slow queries, and shows why order‑by may still trigger filesort, providing practical tips and SQL examples for better MySQL index design.
This article presents five practical rules for writing efficient SQL queries—including using precise WHERE clauses, creating appropriate indexes, avoiding costly subqueries, replacing OFFSET‑based pagination with key‑based limits, and understanding the logical execution order of SQL clauses—to help developers improve database performance across major relational systems.
This article provides an extensive overview of MySQL, covering database creation, renaming, and deletion, table creation and alteration, column and index management, data queries, inserts, updates, deletes, operator usage, view handling, functions, stored procedures, triggers, sequence generation, user management, and permission configuration, with practical examples and code snippets.
This article explains Change Data Capture (CDC) and how Flink CDC can be used for incremental data synchronization, real‑time materialized views, audit logging, and CDC‑based joins, providing code examples, Maven dependencies, and SQL/Java snippets for MySQL and Kafka integrations.
This article explains how MyBatis loads its global configuration, parses mapper files, builds SqlSessionFactory and SqlSession, and executes SQL statements through a series of internal components such as Executor, StatementHandler, and caches, illustrated with detailed code snippets.
