This article explores the challenges of building layered, flexible database queries in Go, compares GORM, sqlx, and squirrel, and proposes a custom, option‑based query‑builder pattern that stays idiomatic and extensible for complex search scenarios.
This article explains how the MySQL 8.0 CLONE plugin simplifies creating reliable replica instances by installing the plugin, granting necessary privileges, executing the CLONE SQL statement, monitoring progress via performance_schema, and verifying successful completion of all clone stages.
This guide provides step‑by‑step Oracle commands for creating and dropping databases, performing full and selective backups, restoring data locally or remotely, managing tables—including creation, deletion, renaming, column alterations, primary keys, indexes, and views—as well as querying, inserting, updating, deleting, and copying data efficiently.
This article explains how MySQL's LATERAL join and recursive CTE can be used to compute the number of direct and indirect reports for each employee, demonstrating lateral external references, non‑lateral references, query execution details, and EXPLAIN output in MySQL 8.0.14 and later.
This article provides a comprehensive walkthrough of the order return and return‑reason database tables, detailing each field, status codes, and the corresponding admin and mobile UI screens that illustrate how the return process works in an e‑commerce system.
This article explains how to replace the nth occurrence of a substring in MySQL, comparing a custom stored function, an export‑sed‑import workflow, and the built‑in REGEXP_REPLACE and REGEXP_INSTR functions introduced in MySQL 8.0, with examples for both ASCII and multibyte strings.
This article presents a detailed performance comparison of ClickHouse using two real‑world cases, analyzes query timings, explores parameter tuning, schema and SQL adjustments, and summarizes practical conclusions for achieving sub‑second analytics on billion‑row datasets.
This article provides a detailed analysis of the order-related database tables—including orders, order items, operation history, and order settings—along with their fields, data types, and meanings, and showcases how these tables are presented in both admin and mobile interfaces of an e‑commerce system.
This article explains how to persist Spark DataFrames into Hive tables and specific partitions, covering the relevant write APIs, the need to select a database, and providing step‑by‑step Scala code examples for both Spark 1.6 and Spark 2.x versions, along with Hive table creation syntax.
This article walks through the core database tables that power product editing, SKU management, tiered pricing, full‑reduction discounts, member pricing, product reviews, verification records, and operation logs in an e‑commerce system, complete with SQL definitions and UI screenshots.
This article enumerates the typical reasons why MySQL returns error 1045 – such as connecting to the wrong host, using a non‑existent user, host‑based access restrictions, incorrect passwords, special characters in Bash, and missing SSL – and provides step‑by‑step commands to diagnose and resolve each issue.
This article presents a practical guide for Oracle DBAs to quickly diagnose and resolve application‑related database issues by checking OS load, analyzing wait events, querying session and object details, capturing diagnostic dumps, and using scripted commands to kill sessions or restart the database.
This article chronicles the origins and growth of Oracle, the colorful personality of its founder Larry Ellison, the early development of relational databases, the fierce competition among IBM, Sybase, Borland and other vendors, and the later impact of open‑source and cloud‑computing trends on the database market.
This article explains the differences between NULL and empty strings in MySQL, covering how NULL is stored, its impact on numeric and string operations, ordering, grouping, aggregation functions, and provides practical code examples and recommendations for schema design.
This article explains how MySQL 8.0 separates static and dynamic permissions, demonstrates replacing the powerful SUPER privilege with finer‑grained SESSION_VARIABLES_ADMIN and SYSTEM_VARIABLES_ADMIN rights using example commands, and shows the resulting grant output and deprecation warning.
This article introduces SQL fundamentals, explains DDL/DML/DCL categories, and provides practical PostgreSQL query optimization techniques—including index selection, condition rewriting, sub‑query removal, expression and partial indexes, DDL decomposition, CTE usage, and key configuration parameters—to help developers and DBAs improve performance and maintainability.
This article explains MySQL table fragmentation, how to detect it with SHOW TABLE STATUS, the reasons such as frequent DELETE and UPDATE operations, demonstrates its effects, and presents cleanup methods like OPTIMIZE TABLE and engine conversion, showing significant space savings and query speed improvements.
This article debunks the myth that COUNT(1) is faster than COUNT(*), explains how MySQL engines handle these functions, compares their performance with COUNT(column), and offers practical tips for avoiding costly full‑table counts.
The article introduces CB‑SQL, JD Retail’s next‑generation elastic database based on CockroachDB, and comprehensively explains its overview, layered architecture, storage replication, cluster management, MVCC, hybrid logical clocks, lock‑free distributed transactions, CDC changefeeds, and SQL interface support.
This article analyzes why MySQL LIMIT pagination becomes increasingly slow with larger offsets, presents six optimization techniques—including using primary keys, covering indexes, prepared statements, subqueries, and composite indexes—and provides experimental results that demonstrate dramatic performance improvements.
On June 29, the Apache Flink Beijing Meetup presented a comprehensive analysis of Flink 1.9’s major architectural changes, new Table API & SQL capabilities, runtime and core enhancements, and future roadmap, with slides and resources made available for download.
This article provides a comprehensive, step‑by‑step guide to MySQL performance tuning, covering the underlying query process, optimization philosophy, practical tools, system‑level diagnostics, hardware and OS recommendations, as well as detailed configuration parameters for both the server and storage engine.
This article provides a detailed technical analysis of Phoenix's native index creation and maintenance mechanisms, the underlying source code for index building, the role of coprocessors, and the complete SQL compilation pipeline from parsing to execution, highlighting how hints and optimizers influence index usage.
The article outlines 19 practical MySQL optimization methods—from using EXPLAIN and limiting IN clause size to proper pagination, index usage, and JOIN strategies—providing clear explanations and example SQL snippets to help developers improve query performance and reduce resource consumption.
This article explains why MySQL queries can become slow—covering occasional delays from dirty‑page flushing or lock contention and persistent slowness caused by missing, unused, or mis‑chosen indexes—while offering practical tips such as checking process lists, correcting index usage, and refreshing statistics.
This article demonstrates how to achieve ranking and pagination in MySQL using pre‑8.0 techniques such as session variables and GROUP_CONCAT, then shows the simpler native window function syntax introduced in MySQL 8.0, with complete examples, sample data, and queries for finding low‑scoring students.
This article provides an in‑depth overview of Flink SQL, covering its origins, the latest 1.7.0 and 1.8.0 enhancements, the underlying programming model, common operators and built‑in functions, and a complete end‑to‑end example that analyzes NBA scoring‑leader data using Flink SQL.
This article examines why MySQL LIMIT pagination becomes slower as the offset grows, presents six practical methods—including direct LIMIT, primary‑key indexing, ordered index scans, prepared statements, sub‑queries, and joins—provides benchmark results on large tables, and distills actionable guidelines for designing covering and composite indexes to achieve fast, stable pagination.
This article introduces Spark SQL fundamentals, including its architecture, DataFrame and Dataset abstractions, query methods, interoperability with RDD, user-defined functions, integration with Hive, data source handling, and provides step‑by‑step Scala code examples for loading data, performing aggregations, and solving common analytical tasks.
This article explains what stored procedures are, outlines their advantages and disadvantages, argues why they are often not the best choice for modern applications, and shares the author’s perspective along with multiple community viewpoints on using stored procedures in database design.
Open Distro for Elasticsearch is an AWS‑backed, fully open‑source distribution of Elasticsearch that adds enterprise‑grade security, alerting, SQL querying, and performance analysis features, includes Kibana for visualization, and follows a clear version‑release roadmap.
An in‑depth performance benchmark compares MySQL on CentOS 6 and CentOS 7 using sysbench with 10 tables of 5 million rows, revealing up to 40% higher QPS on CentOS 7, detailing test setup, results, and key configuration tweaks that dramatically improve throughput.
This article examines a wide range of factors that can cause MySQL queries to run slowly—including network latency, OS and hardware bottlenecks, storage I/O scheduling, CPU power settings, RAID cache behavior, index design, implicit type conversion, execution plan errors, large data volumes, metadata locks, concurrent row updates, data skew, inefficient pagination, table schema choices, InnoDB dirty‑page flushing, and undo log retention—offering concrete examples and mitigation strategies for each scenario.
This article explains how to handle 20,000+ concurrent SQL requests by moving from a simple single‑instance setup to multi‑instance sharding and finally to read‑write separation, detailing the architectural decisions, routing algorithms, and practical limits for high‑traffic e‑commerce systems.
This article examines common performance pitfalls in Oracle hierarchical (CONNECT BY) queries, compares filtering after tree generation versus during traversal, explains why CBO estimates can be wildly inaccurate, and demonstrates how to rewrite queries with pipelined table functions and parallel hints for dramatic speed gains.
This article presents twelve practical SQL optimization techniques—from using proper comparison operators and LIMIT clauses to indexing strategies and query consistency—to dramatically improve database query speed and overall application performance.
This article chronicles the origins, standardization process, historical milestones, learning resources, and conformance challenges of SQL standards, highlighting key ISO/IEC versions, the roles of major vendors, and the growing complexity of the language over more than three decades.
This article examines frequent MySQL performance problems such as inefficient LIMIT pagination, implicit type conversion, sub‑query updates, mixed ordering, EXISTS usage, condition push‑down limits, early result set reduction, and intermediate result set optimization, providing rewritten SQL examples that dramatically improve execution speed.
This article provides a comprehensive step‑by‑step guide for migrating data from Oracle to MySQL using Oracle GoldenGate, covering architecture overview, environment setup, table and data migration, configuration of source and target processes, handling of common issues, data validation, and reference resources.
Discover the most common MyBatis pitfalls—from # vs $ usage, handling nulls, resultMap quirks, and dynamic SQL issues—to master safe query practices, optimize performance, and avoid runtime errors, while comparing its strengths and weaknesses against Hibernate for effective Java backend development.
This tutorial explains how to use Navicat to back up a test database, delete and restore product data, and synchronize schema changes between test and production databases, providing detailed step‑by‑step instructions with screenshots.
Apache Phoenix is an open‑source SQL layer for HBase that lets developers use standard JDBC instead of the native HBase client API, offering features such as secondary indexes, transactions, and various SQL‑level optimizations while supporting full table creation, insertion, and querying.
This article explains MySQL 8.0's invisible index feature, outlines practical scenarios for its use, provides step‑by‑step SQL examples to create, hide, and test indexes, and shows how to enable the optimizer to consider invisible indexes for better performance and safer schema changes.
This article explains why a MySQL statement may run slowly—whether only occasionally or every time—by examining redo‑log flushing, lock contention, missing or misused indexes, optimizer mis‑estimates, and provides practical commands to identify and resolve each issue.
This article explains the new descending index feature in MySQL 8.0, compares its execution plans and resource usage against traditional ascending indexes, and provides practical examples and composite index strategies to improve query performance.
This article explains the difference between MyBatis #{ } and ${ } placeholders, how the misuse of ${ } can cause SQL injection and incorrect IN‑clause queries, and demonstrates a safe solution using the foreach tag to handle list parameters.
This article explains why MyBatis may execute unexpected additional SQL statements, reveals the default caching behavior, shows the required XML configuration and Serializable implementation, and offers practical steps to prevent cache clearing on inserts for more efficient backend data access.
This presentation by Alibaba multi‑model database expert Zhang Yun explains how Phoenix enables SQL‑style querying over HBase, covering its architecture, query examples, performance considerations, and typical application scenarios.
Starting with MySQL 8.0.16, the traditional mysql_upgrade command is deprecated and replaced by a new server upgrade method using the --upgrade option, which offers faster, simpler, and safer upgrades without restarting, and provides four modes (NONE, AUTO, MINIMAL, FORCE) to control data dictionary and system table updates.
This article explains the difference between MyBatis #{ } and ${ } placeholders, why using ${ } can cause SQL injection and IN‑clause errors, and how to safely resolve the issue with proper parameter handling and the foreach tag.
SQLFlow, an open‑source project from Ant Financial, bridges SQL engines and AI frameworks so that users can train and predict machine‑learning models with simple SQL statements, dramatically lowering the technical barrier for analysts and engineers across diverse data platforms.
SQLFlow is an open‑source project that connects diverse SQL engines (MySQL, Hive, SparkSQL, etc.) with AI frameworks (TensorFlow, PyTorch, XGBoost, etc.) through extended SQL syntax, enabling analysts to train and predict models using only a few SQL statements while aiming for high scalability and performance.
MySQL 8.0 introduces Index Skip Scan (ISS), allowing queries that filter on non‑leftmost columns of a composite index to use the index without creating additional indexes, improving performance especially when the leftmost column has few distinct values, as demonstrated with example tables and execution plans.
This article explains why MySQL lacked CHECK constraints for years, demonstrates the data quality issues caused by missing checks, shows how to work around them with unsigned types, and provides step‑by‑step examples of using the new CHECK constraint in MySQL 8.0.16+ while highlighting its absence in earlier versions.
Developers can boost efficiency by using Excel to concatenate column values into SQL IN clauses or C# List syntax, locate duplicate entries, split cell contents by delimiters, and permanently disable automatic hyperlink creation, with step‑by‑step visual guides illustrating each technique.
This article provides a comprehensive guide to Hive SQL performance tuning, covering optimization goals, common pitfalls, execution flow, table and job settings, map, shuffle, reduce, and query-level improvements such as join, bucket join, group‑by, and count‑distinct optimizations.
This article explains how MySQL 8.0 introduced WITH expressions, demonstrates their ease of use and efficiency with practical examples compared to traditional views, shows performance gains through query plan analysis, and illustrates functional use cases such as replacing temporary tables.
This article presents Meituan's real‑world experience with Apache Druid, detailing the platform’s current usage, the usability, security and stability challenges faced, the principles and architecture of Druid SQL, the enhancements made to schema inference, logging, query safety, and the custom security extensions implemented to achieve fine‑grained access control and SSO integration.
This article explains how to perform row-to-column transformations (pivot) in Hive using two methods: a traditional SQL approach mimicking Oracle/SQL Server pivot syntax and a more concise map-based technique, comparing their syntax, performance, and memory considerations.
This article provides a comprehensive overview of Hive as a Hadoop‑based data warehouse, explains its architecture, query‑to‑MapReduce translation, high‑availability design, and compares its batch‑oriented processing with Impala's low‑latency SQL engine for big data analytics.
This article explains MySQL's execution stages, defines SemiJoin, details how IN subqueries can be transformed into SemiJoin queries, outlines conversion constraints, provides example transformations, and describes the various execution strategies MySQL may use for SemiJoins.
This article explains how distributed middleware processes simple SQL, ER‑based joins, and cross‑database joins, analyzes their performance impacts, and provides practical recommendations for optimizing SQL statements by using shard conditions, limiting clauses, low‑cardinality columns, and table ordering.
This guide provides a concise review of fundamental database concepts—including definitions of databases, RDBMS, SQL, and tables—and walks through essential SQL query techniques, data manipulation commands, schema definition statements, and control language basics, offering practical examples for data analysts.
This article explains MySQL indexes—defining what they are, classifying them by structure, storage, and logic, illustrating InnoDB clustered and secondary index structures with examples, and covering advanced concepts such as covering indexes, the leftmost‑prefix rule, and index condition pushdown to improve query performance.
MySQL 8.0.14 introduces a read/write consistency feature for Group Replication, explaining why MGR can still be inconsistent, detailing the group_replication_consistency parameter with its five levels, their advantages, disadvantages, and appropriate application scenarios, plus example session settings.
This article explains MyBatis fundamentals including the difference between #{} and ${} placeholders, various pagination approaches, logical versus physical pagination, first‑ and second‑level caching, lazy‑loading mechanisms, executor types, pagination plugin principles, and how to create a custom MyBatis plugin with code examples.
The article examines why seemingly outdated technologies such as SQL remain valuable, critiques the overhyped promises of TypeScript and Docker, and urges developers to balance new trends with proven fundamentals for sustainable software engineering.
This article explains why Hibernate is used, defines ORM, shows how to view SQL logs, describes HQL/SQL/Criteria queries with code examples, discusses entity class design, caching layers, object states, session handling methods, and the requirement for a no‑argument constructor.
This article explains the main index structures used in MySQL—including hash tables, ordered arrays, search trees, and B+ trees—covers why InnoDB prefers B+ trees, the benefits of auto‑increment primary keys, and key concepts such as covering indexes, the left‑most prefix rule, and index‑pushdown for query optimization.
This article explains the concept of Temporal Table JOIN, its implementation in SQL Server and Apache Flink, provides DDL/DML examples, compares it with other join types, and discusses improvements to align Flink with ANSI‑SQL standards.
This article explains how Apache Flink's Interval Join solves time‑bounded join requirements more efficiently than unbounded joins, covering its syntax, semantics, state‑management considerations, and providing a complete Scala example with code and execution results.
This article explains the special JOIN LATERAL operator, compares it with INNER JOIN and correlated subqueries, shows how SQL Server implements it via CROSS APPLY, and demonstrates its support in Apache Flink using Calcite and user‑defined table functions with concrete code examples.
This weekly briefing introduces Apache Flink's continuous query mechanism, demonstrates how to integrate Kafka as a DataStream connector, provides an overview of Flink SQL features, explains the implementation and optimization of dual‑stream JOIN operators, and showcases the Table API with end‑to‑end examples.
This article provides a comprehensive tutorial on Apache Flink's Table API, explaining its concepts, core features, and a wide range of operators such as SELECT, WHERE, GROUP BY, UNION, JOIN, and various window functions, while offering complete Scala code examples, custom sources, sinks, and an end‑to‑end job that computes page‑view counts per region using event‑time tumbling windows.
This article explains the purpose and types of SQL JOIN operators, demonstrates their syntax and semantics with examples, compares traditional database joins to Apache Flink's streaming two‑stream join implementation, and discusses optimization techniques such as state management, shuffle handling, and join reordering.
This article provides an in-depth introduction to SQL, its history and ANSI standards, then details Apache Flink's SQL capabilities—including SELECT, WHERE, GROUP BY, UNION, JOIN, window functions, and user-defined functions—accompanied by extensive code examples and a complete end‑to‑end Flink job implementation.
This article reviews the shortcomings of MyCat in handling sharding, joins, inserts, and session variables, demonstrates how the DBLE middleware addresses those issues with better correctness, performance, security, and operational management, and discusses code‑quality improvements and automated testing practices.
Database indexes, akin to a dictionary’s table of contents, dramatically speed up data retrieval, and this article explains what indexes are, how composite indexes work, the left‑most prefix rule, and the difference between clustered and non‑clustered indexes, using clear analogies and examples.
This guide explains how to define duplicate rows, use GROUP BY and HAVING to locate them, and apply temporary tables and DELETE statements to remove duplicates while preserving the row with the smallest ID.
This article explains why the native streaming engine Apache Flink can offer a SQL API by showing that MySQL binlog events form a time‑stamped data stream that can be replayed as a dynamic table, establishing a lossless stream‑table duality useful for both batch and streaming queries.
This article explains the evolution of MySQL's data dictionary from pre‑8.0 scattered metadata to the unified InnoDB dictionary tables in MySQL 8.0, covering storage structures, dictionary caching, information_schema changes, serialized dictionary information (SDI), atomic DDL mechanisms, persistent autoincrement handling, upgrade considerations, and provides practical code examples.
An InnoDB deadlock occurs when concurrent UPDATEs on a table with a composite (c,d) index generate conflicting GAP and Next‑Key locks during internal delete‑plus‑insert operations, and the simplest resolution is to drop column d from the index, leaving a single‑column index to prevent the lock clash.
This article examines a MySQL 5.6 InnoDB deadlock scenario caused by concurrent batch INSERTs, detailing the table schema, test cases, lock acquisition phases, log analysis, lock compatibility matrix, and discusses why typical SQL‑level fixes are limited, suggesting index redesign or data sharding as mitigation.
When a critical Oracle 10g database on AIX spikes to 100% CPU, this detailed case study walks through emergency actions, AWR analysis, hidden bottlenecks, and practical tuning steps that reduced CPU usage by half and restored system stability.
This article introduces Apache Flink’s Table API and SQL, explains the TableEnvironment programming model, shows how to register tables and sinks, and provides two complete Java examples—WordCount and a file‑based aggregation—complete with code that can be downloaded for local testing.
This article shows step‑by‑step how to identify duplicate rows in a MySQL table—whether the duplication is based on a single column, multiple columns, or either column—and provides reliable SQL patterns, including GROUP BY with HAVING, UNION, sub‑queries, temporary tables, and a safe DELETE statement to clean the data.
A SAP month‑end CO module query suffered severe slowdown despite standard index hints, prompting a deep dive into execution plans, statistics, and a little‑known num_index_keys hint that dramatically improves IN‑list iterator access and reduces filter overhead.
The article explains seven SQL join techniques—including INNER, LEFT, RIGHT, FULL OUTER, and their exclusive variants—illustrated with diagrams and accompanied by complete SELECT statements that demonstrate how to combine rows from two tables based on matching keys.
An in‑depth MySQL case study shows how concurrent gap‑locks under REPEATABLE READ can cause a deadlock when two transactions delete non‑existent rows and then attempt inserts, includes full DDL, log excerpts, step‑by‑step analysis, and practical mitigation strategies.
This article explains the essential concepts, philosophy, participants, dimensions, tools, and step‑by‑step procedures for safely and effectively optimizing MySQL databases, covering hardware, system, application, and MySQL‑level tuning together with practical command‑line examples.
This article examines two MySQL deadlock scenarios under REPEATABLE READ isolation—one with a non‑unique index and another with a unique index—by presenting table definitions, test data, raw deadlock logs, and step‑by‑step analysis of lock acquisition and waiting, ultimately offering practical recommendations to avoid such deadlocks.
This article provides a comprehensive guide to MySQL indexes, covering fundamental concepts, various index types, creation and design principles, practical optimization techniques, and the underlying data structures, illustrated with example tables, SQL statements, EXPLAIN analyses, and visual diagrams.
An in‑depth guide to MySQL optimization covering the query execution process, risk‑aware philosophy, hardware and system tuning, essential diagnostic tools, practical SQL and index adjustments, and comprehensive configuration settings to boost performance while maintaining stability and security.
This article compiles 52 practical SQL performance optimization strategies—including indexing best practices, query rewriting, use of temporary tables, storage engine selection, data type choices, backup procedures, and diagnostic tools—to help developers write faster, more efficient database queries.
The article lists nineteen practical MySQL optimization methods—including using EXPLAIN, limiting IN clause size, avoiding SELECT *, using LIMIT 1, reducing unnecessary sorting, preferring UNION ALL, avoiding ORDER BY RAND(), distinguishing IN/EXISTS, applying proper pagination, segment queries, avoiding NULL checks, using full‑text indexes, preventing expression‑based WHERE clauses, respecting left‑most prefix, forcing indexes, handling range queries, and optimizing JOINs—to improve query performance and resource usage.
Data Mill is a Spark‑Streaming‑based real‑time computation framework that abstracts tasks as DataFrames, enables SQL‑driven development, and supports DSP business requirements by reducing latency to 15‑30 minutes while providing a scalable architecture, caching strategy, and automated fault handling.
This article walks through Youzan's real‑time platform architecture, explains why Flink was chosen over Spark Structured Streaming, details practical challenges such as container over‑provisioning and monitoring overhead, shares solutions for Spring integration and async caching, and outlines future directions for SQL‑based streaming and scheduler improvements.
TDH 5.2.3 introduces a series of stability and performance upgrades—including transaction and compaction optimizations, enhanced error handling, SQL length protection, improved Oracle‑compatible UDFs, default resource pool support, Guardian caching, TxSQL monitoring, and workflow and OLAP engine fixes—aimed at delivering a more reliable big‑data platform.
This article explains how Apache Ignite, a memory‑centric data platform with strong SQL support, can replace costly MySQL join queries, reduce data‑sync time from over 20 minutes to 15 minutes, improve performance fourfold, and outlines architectural changes, SQL advantages, current limitations, and future scaling plans.
This article outlines the philosophy, risks, participants, and step‑by‑step strategies for MySQL optimization, covering query flow, hardware and system tuning, configuration parameters, monitoring tools, and SQL‑level improvements to achieve stable, high‑performance database operations.
