This article explains why traditional OFFSET/LIMIT pagination becomes inefficient with large tables, illustrates its performance drawbacks with real examples, and introduces a high‑performance cursor‑based pagination method that leverages indexed primary keys to dramatically speed up data retrieval.
This article explains why ETL is a critical step in building data warehouses, introduces eight core ETL algorithms—including full delete/insert, upsert, append, and various link‑table models—describes their ideal use cases, and provides ready‑to‑run SQL code examples for each.
This article explores MySQL performance limits, concurrency settings, query‑time best practices, table design choices, index classifications, and a comprehensive set of SQL optimizations—including batch processing, operator rewrites, and pagination tricks—to help developers eliminate slow queries and build scalable database solutions.
This article explains the SQL standard restriction that only grouped keys, aggregate functions, or constants may appear in the SELECT clause of a GROUP BY query, demonstrates the resulting error in MySQL, and discusses how SQL modes like ONLY_FULL_GROUP_BY enforce this rule.
This article explains, from source‑code analysis, why MyBatis mapper methods cannot be overloaded, shows the resulting BeanCreationException, walks through the MyBatis‑SpringBoot auto‑configuration that builds SqlSessionFactory, and demonstrates how to trace a mapper method to the exact SQL statement defined in the XML mapping file.
This article explains the InnoDB logical storage hierarchy of segments, extents, and pages, then details MySQL horizontal partitioning concepts, types (RANGE, LIST, HASH, KEY), example DDL and DML, and discusses how partitioning impacts performance for OLTP and OLAP workloads.
This article announces a monthly giveaway of the newly upgraded 5th edition of Ben Forta's SQL Essentials book, outlines its features, author background, and target readers, and provides a detailed tutorial on using the SELECT statement to retrieve single and multiple columns with example code.
This article introduces MyBatis's underlying architecture by explaining the six core interfaces—SqlSession, Executor, StatementHandler, ParameterHandler, ResultSetHandler, and TypeHandler—detailing their roles, key methods, caching mechanisms, and how they are instantiated within a Spring Boot environment.
OPPO's data platform engineer Zhang Jun shares the design and implementation of OPPO's real‑time data warehouse built on Apache Flink, covering background, top‑level architecture, practical deployment, and future directions such as enhanced SQL development, resource scheduling, and automated configuration.
This article explains the fundamentals of SQL Server locking, illustrates how row‑level locks can automatically escalate to table‑level locks during large batch operations, and provides practical techniques—such as batching deletes, adding appropriate indexes, and holding intent locks—to avoid lock escalation and improve concurrency.
This article explains MyBatis dynamic SQL, describing each core XML tag (if, choose/when/otherwise, where, foreach, set, sql, include), showing practical code snippets, and offering advanced tips such as avoiding magic numbers and referencing SQL fragments across mapper files.
This article explains MyBatis result mapping techniques—including alias mapping, camel‑case automatic mapping, explicit resultMap definitions, and advanced one‑to‑one and one‑to‑many associations—providing code examples, configuration details, and performance considerations such as the N+1 query problem.
This article explains how ClickHouse's MergeTree engine creates partition directories based on a partition key, details the naming convention PartitionID_MinBlockNum_MaxBlockNum_Level, and describes the automatic and manual merge processes that consolidate partitions for efficient storage.
This tutorial explains how to use MyBatis dynamic SQL tags such as if, choose, trim, foreach, and bind to build flexible SELECT, UPDATE, INSERT, and batch statements, demonstrating data preparation, conditional query construction, selective updates, conditional inserts, and advanced tag usage with complete Java test examples.
This article shows how to use MyBatis XML mappers to write MySQL queries that count employees meeting specific criteria, such as those in the current month or before today, while explaining required aliasing and date‑format handling.
This article compiles a comprehensive set of MySQL interview questions covering lock types, storage engines, data types, indexes, transactions, SQL functions, and optimization techniques, providing concise explanations and practical examples to help readers master essential database concepts.
This article analyzes a real‑world incident where using INSERT INTO SELECT to migrate large order data caused full‑table scans and locking in MySQL, explains the underlying lock behavior, and presents a solution that adds an index and forces its use to avoid the problem.
BeetlSQL provides a unified, high‑performance database access framework supporting traditional, big‑data, and time‑series databases, outlines its Maven dependency, and presents detailed M3‑phase performance benchmarks across various query methods and ORM tools.
This article outlines twenty practical principles for optimizing MySQL indexes, covering topics such as LIKE wildcards, UNION/IN/OR usage, negative conditions, composite index prefix rules, range queries, function avoidance, covering indexes, prefix indexes, pagination strategies, and common misconceptions, all illustrated with SQL examples and performance tips.
This article explains the performance differences between IN, EXISTS, NOT IN, and NOT EXISTS in SQL queries, provides guidance on when to use each construct based on table sizes and indexes, and demonstrates the concepts with practical code examples.
The article explains why SQL is essential for data scientists, introduces PostgreSQL as a powerful open‑source relational database suited for large‑scale data science, outlines its key features, advantages and disadvantages, and provides practical learning resources for beginners.
The article explains why traditional OFFSET and LIMIT pagination becomes inefficient for large tables due to full‑table scans, demonstrates the performance gap with real‑world examples, and presents keyset (cursor‑based) pagination as a fast, index‑driven alternative with practical query patterns.
This tutorial walks through building a three‑node ClickHouse cluster, configuring host mappings and XML files, setting up ZooKeeper for internal replication, creating local and distributed tables, testing data insertion and queries, and demonstrating how to achieve high availability with replicated tables.
This article explains how MySQL's EXPLAIN command reveals the execution plan of a query, detailing each column such as id, select_type, table, type, possible_keys, key, key_len, ref, rows, extra and filtered, while also covering its limitations and a practical case study.
This article demonstrates how to leverage ClickHouse’s parametric aggregate and higher‑order functions to perform path matching, intelligent path detection, ordered funnel conversion, retention calculation, and session statistics for user behavior analysis in a big‑data environment.
This article provides a comprehensive interview‑style overview of MySQL fundamentals, covering relational database concepts, data types, storage engines, SQL syntax, version checking, normal forms, views, stored procedures, and performance tips, all essential for backend developers and database professionals.
This article details how to transform a painfully slow SSRS report that scanned billions of rows into a high‑performance solution by analyzing the original SQL, fixing indexing and partitioning mistakes, and rewriting the logic as a flexible stored procedure that runs in seconds.
This guide explains what MySQL temporary tables are, how they exist only for the current connection, provides step‑by‑step SQL examples for creating, inserting, querying, and dropping a temporary table, and discusses practical scenarios such as high‑concurrency writes.
Apache Calcite 1.25.0, a dynamic data management framework for Hadoop, has been released, introducing support for spatial functions and SQL interval expressions, removing deprecated methods from the previous version, and providing updated Maven dependency coordinates for the core library.
This article investigates the practical limits of joining more than three tables in MySQL by designing experiments with up to 1.5 billion rows, comparing indexed and non‑indexed queries, and contrasting the results with Oracle's performance, while providing full SQL scripts for data generation and analysis.
This article demonstrates how to implement an advertising business data statistics pipeline using Spark and Kudu, detailing metric requirements, Scala processing code, complex SQL aggregations, schema design, and data sinking for verification.
This article walks through the complete implementation of an advertising statistics pipeline using Spark and Kudu, covering requirement analysis, Scala code development, SQL queries, schema definition, and data sinking, with full code snippets and execution results.
This tutorial walks through a Spark‑Kudu advertising project that computes province‑city distribution statistics using SQL, defines the necessary schema, and demonstrates how to write the aggregated results back to a Kudu table for persistent storage, complete with Scala code examples.
This tutorial explains how to obtain geographic coordinates, store them as MySQL POLYGON types following OGC standards, insert campus area data, and query with spatial functions and SPATIAL indexes to determine whether a user is within a defined check‑in region.
This article explains how to construct SQL INSERT statements for string, numeric, date, and boolean fields, demonstrates variable concatenation for dynamic values, and provides a comprehensive example that combines multiple data types into a single query.
The article describes how a news‑list page became sluggish due to large base64‑encoded images stored in the database, details the investigation using Chrome dev tools and SQL queries, and presents a step‑by‑step solution that moves images to a FastDFS file server and updates the content references.
This article explains why SQL optimization is a cost‑effective way to boost system performance, outlines fundamental principles, common pitfalls such as leading wildcards, IN/OR/NULL misuse, and provides practical tips on indexing, execution order, hints, DML batching, pagination, and table design for MySQL databases.
This article explains the SQL standard limits on SELECT columns after GROUP BY, demonstrates how MySQL's ONLY_FULL_GROUP_BY mode enforces those limits, explores various SQL modes, and clarifies why aggregated queries cannot reference non‑grouped columns directly.
This article explains how MySQL's AUTO_INCREMENT generates unique identifiers, the constraints on its use, how deletions affect the sequence, the role of LAST_INSERT_ID in concurrent environments, and best practices for managing auto‑increment columns in InnoDB tables.
This article explains how ClickHouse's MergeTree engine creates partition directories, the naming convention for partitions, and the automatic and manual merging processes that consolidate parts while tracking block numbers and merge levels.
This article provides a detailed collection of SQL Server commands and techniques covering database creation, table and index management, common DML statements, advanced set operators, various join types, grouping, pagination, maintenance tasks, replication setup, linked‑server usage, and a synchronization stored procedure.
This article explains why using NULL columns in MySQL can hurt performance and increase storage, debunks common myths with official documentation, and provides practical reasons and code examples for preferring NOT NULL constraints in table design.
This article introduces Confluent KSQL, a SQL‑based real‑time stream processing engine for Kafka, covering its architecture, stream vs table concepts, query lifecycle, Docker‑based setup, DDL commands, example joins, windowed aggregations, connectors, and its advantages and limitations.
This article explains how to install, configure, and test MySQL 8.x query‑rewriter and DDL‑rewriter plugins, demonstrating environment setup, sample SQL scripts, rule creation, and the impact on query performance and CREATE TABLE statements.
This article explains the evolution of Apache Flink's SQL support, detailing the Blink Planner architecture, the end‑to‑end Flink SQL workflow, logical and physical planning, code generation, stream‑specific optimizations such as retraction and mini‑batch, and future development directions.
This article explains MySQL 8.0's new GROUPING() function, demonstrates how it works with GROUP BY ... WITH ROLLUP to differentiate regular NULL values from roll‑up summary rows, and provides multiple SQL examples and usage tips.
This tutorial walks Python developers through using the built‑in sqlite3 library to create, query, and manage SQLite databases, demonstrates how to connect with SQL clients like DBeaver, and shows seamless integration with Pandas data frames for advanced data handling.
This article explains the four frequently used MySQL data‑insertion statements—INSERT, INSERT SELECT, REPLACE INTO, and INSERT … ON DUPLICATE KEY UPDATE—detailing their syntax, options, behavior, and performance considerations for reliable database operations.
This article explains what MySQL binlog is, how to enable row‑based logging, and provides step‑by‑step Linux commands using mysqlbinlog and grep to extract historical SQL statements such as DELETE operations for troubleshooting and debugging purposes.
This article explains MySQL's B‑Tree index structure, describes various index types such as primary, unique, normal, full‑text, and composite indexes, and provides practical techniques for creating efficient indexes, choosing column order, using prefix and covering indexes, and optimizing query sorting.
This article explains how to locate a MySQL statement that holds a lock but does not appear in SHOW PROCESSLIST, by enabling performance_schema, examining INNODB_LOCK_WAITS, INNODB_LOCKS, INNODB_TRX, and using performance_schema.threads and events_statements_current to retrieve the blocking SQL.
The article explains that when checking for the existence of records, using SELECT count(*) is inefficient and proposes replacing it with SELECT 1 … LIMIT 1, showing Java code examples and highlighting performance gains and reduced index usage.
This article explains the meaning of Java OOM errors, presents a real‑world case where an Oracle database lock caused a slow memory leak leading to OOM, details the lock types and modes, provides SQL to identify and kill offending sessions, and offers best‑practice tips to avoid lock‑related performance issues.
This article introduces Apache Phoenix, an open‑source SQL layer for HBase that enables JDBC‑based table creation, data insertion and querying, while supporting secondary indexes, transactions and various optimizations, and outlines a series covering its syntax, tools, best‑practice and real‑world use cases.
Discover the true execution sequence of SQL queries, why SELECT runs fifth, how window functions and GROUP BY interact, and the impact of engine optimizations, illustrated with diagrams and code examples spanning aliases, LINQ, and pandas.
This article breaks down MySQL’s architecture into its five key parts—connector, parser, optimizer, executor, and storage engine—explaining each component’s role from establishing connections and handling permissions to query parsing, execution planning, and data storage, with example commands and code snippets.
This article presents a collection of practical PostgreSQL DBA techniques—including selective updates, bulk‑load optimizations, CTE‑based deduplication, partial and BRIN indexes, correlation tuning, and transactional DDL tricks—to help developers reduce database bottlenecks, improve query speed, and avoid common performance pitfalls.
This article explains Hive's bucket sampling syntax, demonstrates how to use the TABLESAMPLE clause with various bucket parameters, provides concrete SQL examples, and clarifies the underlying hash‑based mechanism that determines which rows are returned.
This article explains the concepts of the first, second, and third normal forms in relational databases, shows concrete table examples, discusses why normalization is important for consistency and storage efficiency, and explores denormalization trade‑offs and modern JSON support in MySQL and NoSQL systems.
This article explains why using SELECT 1 with LIMIT 1 is a more efficient alternative to COUNT(*) when checking for the existence of rows, shows the common and optimized SQL/Java code patterns, and discusses the performance benefits and impact on index usage.
This article explains the TPC‑C benchmark, covering its purpose, data model, database schema, the five transaction types, and a detailed step‑by‑step walkthrough of the New‑Order operation with accompanying SQL illustrations, while also summarizing workload characteristics and providing reference links.
This article explains the concepts of the first, second, and third normal forms, shows how to identify violations with concrete table examples, discusses why normalization is important for consistency and storage efficiency, and explores anti‑normalization techniques that trade redundancy for performance.
The article explains that using SELECT 1 with LIMIT 1 instead of COUNT(*) to test whether rows exist can dramatically improve query performance, shows the typical COUNT(*) approach in SQL and Java, and provides a streamlined alternative with code examples and practical benefits.
This article explains why and how to create prefix indexes for string fields in MySQL, compares full‑column and prefix indexes with concrete examples, shows how to pick an optimal prefix length using cardinality statistics, and discusses alternative solutions such as reverse storage and hash fields.
The article explains why using SELECT 1 with LIMIT 1 is a more efficient alternative to COUNT(*) for existence checks in SQL and shows how to simplify the corresponding Java code for better performance.
This article presents a thorough overview of MySQL optimization, covering the query execution process, philosophical principles, risk assessment, required participants, optimization dimensions, commonly used diagnostic tools, emergency and regular tuning procedures, hardware and system level adjustments, as well as detailed configuration parameters for both the server and the database engine.
This article provides a detailed walkthrough of JetBrains DataGrip, covering installation, connection setup, UI customization, SQL editing features, data import/export, and numerous productivity shortcuts, helping developers efficiently manage and interact with various relational databases.
This article demonstrates how to enable MySQL's performance_schema, generate test data, run an ALTER TABLE operation, and continuously query a custom SQL statement to estimate the operation's progress and remaining time, providing practical insight into DDL monitoring.
This article details how Alibaba’s Data Experience team builds a comprehensive stack—from a high‑performance SQL editor and low‑code BI platform to sophisticated data visualizations, intelligent analytics, digital art, Web3D rendering, and robust data‑security measures—showcasing the architecture, design principles, and future directions of their end‑to‑end data‑driven product ecosystem.
This article explains why MySQL data can become garbled due to mismatched character sets during insertion or retrieval, explores various scenarios such as client‑side encoding errors, mixed encodings within tables, and LATIN1 storage, and provides detailed solutions including setting proper client encodings, using strict SQL_MODE, and exporting/importing data to unify character sets.
This article introduces PostgreSQL, the powerful open‑source object‑relational database, highlights its rising popularity in DB‑Engines rankings, compares its advanced features and performance advantages over MySQL, and outlines many of its unique capabilities such as rich data types, sophisticated indexing, and robust replication.
This tutorial walks through the test environment and demonstrates how InnoDB Cluster's built‑in functions—such as get_cluster, describe, list_routers, set_option, options, set_instance_option, set_primary_instance, switch_to_multi_primary_mode, switch_to_single_primary_mode, status, and dissolve—simplify the deployment, monitoring, and administration of MySQL group replication, complete with code snippets and visual results.
This guide walks through using Oracle Real Application Testing (RAT) Database Replay to capture production workloads, preprocess them, and replay them on a new Oracle 19c environment, highlighting setup steps, common pitfalls, and monitoring techniques.
This guide details recommended MySQL versions for Zabbix, proper grant statements for read/write and read‑only users, essential MySQL configuration tweaks, SQL techniques for item and function updates, read‑write splitting, upgrade procedures, backup strategies, and comprehensive partition maintenance scripts to improve performance and manage data growth.
This article explains various MySQL pagination methods—including simple LIMIT queries, sub‑query optimizations, ID‑range filtering, and temporary‑table approaches—provides detailed performance measurements on a large order_history table, and concludes with practical recommendations while also promoting a comprehensive interview‑question PDF collection.
This article explains Hive's role as a Hadoop‑based data warehouse, its integration with HBase, the advantages and drawbacks of that combination, introduces Apache Phoenix as a high‑performance SQL layer on HBase, and describes the open‑source NewSQL database Lealone, providing practical usage scenarios and performance comparisons.
This article compiles frequent Spark SQL, Spark Core, PySpark, and Streaming problems—such as filesystem errors, configuration pitfalls, memory limits, shuffle failures, and version incompatibilities—along with concise explanations of their causes and step‑by‑step remediation methods for big‑data environments.
This article provides a thorough, step‑by‑step guide to MySQL, covering how to install and start the server, create databases and tables, use DDL/DML/DQL statements, understand numeric, date/time and string data types, apply arithmetic, comparison, logical and bitwise operators, and leverage common built‑in functions for efficient data manipulation.
This article provides a comprehensive overview of relational database fundamentals and SQL syntax, covering basic concepts, statement categories (DDL, DML, TCL, DCL), CRUD operations, subqueries, joins, unions, functions, ordering, grouping, constraints, transactions, permission management, stored procedures, cursors, and triggers with illustrative code examples.
This article explains PostgreSQL's auto‑increment mechanism, covering built‑in serial data types, manual sequence creation, default value assignment, step‑by‑step code examples, and how to query indexes, offering a clear comparison with MySQL's auto_increment feature.
This article provides a thorough overview of MySQL performance tuning, covering proper table design according to the three normal forms, various index types and their creation, partitioning strategies, read/write separation, storage engine selection, and practical SQL tips for improving query efficiency.
This article explains MySQL transaction isolation concepts, the four standard isolation levels, their effects on dirty, non‑repeatable, and phantom reads, practical configuration steps, when to use repeatable read, how InnoDB implements isolation, and tips for avoiding long transactions and planning backups.
This guide compiles essential MySQL optimization techniques—including index cardinality, function indexes, EXPLAIN usage, covering indexes, ICP, slow‑query log analysis, online schema changes, and common SQL pitfalls—to help developers efficiently tune database performance.
The article explains why using SELECT 1 with LIMIT 1 to test for record existence is more efficient than counting rows with SELECT count(*), and shows how to rewrite both SQL and Java code for better performance.
To streamline Oracle SQL script reviews, this guide shows how to adapt the open‑source SOAR tool—originally built for MySQL—by defining custom rules, installing Go and Git, compiling the source, and integrating a web UI, enabling automated, 90% coverage of Oracle SQL checks.
The article explains why using COUNT(*) to test record existence is inefficient and demonstrates an optimized approach that replaces COUNT(*) with SELECT 1 LIMIT 1 in SQL and a corresponding null‑check in Java, improving database performance especially when many rows match the condition.
This tutorial demonstrates how to create a VARBINARY test table in HBase using Phoenix, serialize its data with RoaringBitmap, implement a custom Spark aggregation function to merge bitmap values, and query the table via Spark SQL, showcasing a practical big-data processing workflow.
SQLFlow is an open‑source system that lets users describe and execute end‑to‑end AI tasks—including data extraction, preprocessing, model training, evaluation, prediction, and explanation—entirely with SQL, simplifying workflow construction across multiple databases and machine‑learning engines while supporting scalable execution on Kubernetes.
The article shares practical experiences on running test cases concurrently using Java multithreading and a custom thread pool, and outlines MySQL database naming, null handling, and schema design guidelines, including full code examples for the case runner and thread pool implementation.
This article examines the role of foreign keys in relational databases, comparing their consistency guarantees and performance overhead with manual simulation approaches, and provides practical guidance on choosing RESTRICT, CASCADE, or no foreign‑key strategy in high‑concurrency applications.
This article explains MySQL's storage engines, their features and selection criteria, guides on choosing appropriate data types such as CHAR, VARCHAR, TEXT, BLOB, numeric and date types, and discusses character sets and related SQL commands.
This article demonstrates how to create and register custom scalar UDFs, untyped and type‑safe aggregate functions (UDAF and Aggregator) in Spark SQL, and how to apply window functions such as ROW_NUMBER, providing complete Scala code examples and execution results.
This guide explains how to use MySQL binary logs and GTID to restore accidentally deleted data, covering configuration changes, command‑line operations, extracting relevant log segments, and verifying the restored tables step by step.
This article explains how to specify and manage character sets and collations when creating or altering MySQL databases, tables, views, triggers, and columns, illustrating the impact on stored procedures and data retrieval with practical SQL examples.
Apache Flink 1.11.0 introduces a suite of major enhancements—including unaligned checkpoints, a unified source interface, CDC support in Table API/SQL, performance‑boosted PyFlink, a new application deployment mode, and numerous UI, Docker, and catalog improvements—aimed at increasing usability, scalability, and integration across streaming and batch workloads.
This article explains how to resolve foreign key constraint violations that occur when migrating data from multiple source databases into a target PostgreSQL/Greenplum database by disabling, dropping, or deferring constraints and then re‑enabling them to ensure full data integrity.
The article explains why a SELECT COUNT(*) on a 5‑million‑row InnoDB table was extremely slow, analyzes the execution plan, demonstrates that the query used the primary (clustered) index, and shows how creating a small secondary index reduces the runtime from dozens of seconds to under a second.
This article analyzes the various system‑level and database‑level reasons why SELECT, UPDATE, INSERT, or DELETE statements become slow, covering network issues, OS and hardware constraints, MySQL configuration, indexing, execution plans, and query design, and offers practical mitigation strategies.
This article introduces Spark Thrift Server, explains how to deploy it by copying configuration files and required JARs, details its architecture and SQL execution flow, compares it with HiveServer2, and discusses its advantages, limitations, and practical suitability.
This article explains MySQL’s internal workflow, covering the Server layer components, Storage Engine layer, connection handling, query cache behavior, lexical and syntax parsing, optimization decisions, and execution steps, including tips for long‑connection management and common pitfalls.
