This tutorial demonstrates, using MySQL 8.0.32 InnoDB source code, how REPEATABLE-READ and READ-COMMITTED isolation levels affect the locking behavior of SELECT ... FOR SHARE statements on primary‑key rows, including preparation, execution, and detailed lock analysis.
The article recounts a failed ticket change on a high‑speed train, introduces the newly launched 12306 in‑train seat‑swap feature, shares passenger reactions, provides a detailed SQL implementation for segment‑based seat allocation, and reflects on its impact and future suggestions.
This guide explains PostgreSQL table partitioning, covering its definition, benefits, drawbacks, the three partition types (RANGE, LIST, HASH), step‑by‑step SQL commands for creating partitions, indexes, and managing them, and concludes with practical recommendations for effective use.
This article explains what MySQL deadlocks are, outlines the four necessary conditions that cause them, provides a concrete example with SQL code, and offers practical strategies such as lock ordering, timeout settings, and MySQL’s built‑in detection to prevent and resolve deadlocks.
This guide walks through ten essential advanced SQL techniques—including CTEs, recursive CTEs, temporary functions, CASE‑WHEN pivots, EXCEPT vs NOT IN, self‑joins, ranking functions, delta calculations with LAG/LEAD, cumulative sums, and date‑time manipulation—to help data professionals ace interview challenges and write cleaner, more powerful queries.
This article explains why a LEFT JOIN always returns all rows from the left table, clarifies the difference between ON and WHERE clauses, demonstrates the behavior with multiple SQL examples and code snippets, and highlights the special handling of LEFT/RIGHT/FULL joins versus inner joins.
This article explains why adding conditions after a LEFT JOIN's ON clause does not filter rows, contrasts ON and WHERE behavior, and demonstrates the effect with multiple SQL examples and visual illustrations of temporary tables and join semantics.
This article walks through converting a SpringBoot + MyBatisPlus project from MySQL to PostgreSQL, detailing driver setup, JDBC changes, common SQL pitfalls, type‑conversion issues, and provides helper scripts for bulk schema adjustments.
The article traces MariaDB’s evolution from its MySQL roots and community‑driven development, through its NYSE listing, financial performance, restructuring, and the recent K1 Investment Management acquisition, while highlighting key figures, product features, and industry reactions.
This article investigates whether defining a VARCHAR column with a larger length (e.g., VARCHAR(500) vs VARCHAR(50)) changes storage consumption and query speed in MySQL by creating two identical tables, inserting one million rows, measuring space usage, and benchmarking various SELECT and ORDER BY operations.
This article reviews the SQL enhancements added to SQLite between versions 3.22.0 and 3.26.0, covering boolean literals, window functions, the FILTER clause, INSERT … ON CONFLICT (upsert), column renaming, and related API updates.
This guide details essential MySQL upgrade considerations, backup warnings, version‑specific changes, and step‑by‑step procedures for both in‑place and logical migrations, helping administrators safely move from MySQL 5.6 to 5.7 while avoiding common pitfalls.
This guide provides a comprehensive collection of MySQL command‑line scripts covering database and table export/import, creation, deletion, schema inspection, data manipulation, user privilege management, and common DDL operations, all with practical examples.
This article provides a comprehensive overview of MySQL indexing, covering index classifications, B+Tree and Hash structures, primary and secondary (clustered and non‑clustered) indexes, covering index push‑down, index merge, covering indexes, cost‑based index selection, and common pitfalls that cause index inefficiency.
The TDS platform, central to Baidu MEG’s Turing 3.0 ecosystem, unifies data development, warehouse management, monitoring, and resource control through Spark‑based TDE, a visual studio, and AI‑enhanced tools like Smart Diagnosis and Text2SQL, enabling standardized workflows, scalable scheduling, and handling over 30 k daily tasks.
This article explains why the number in MySQL int(N) does not restrict the column’s numeric range, demonstrates that int(1) can store the full unsigned range, and shows how ZEROFILL combined with a display width pads numbers with leading zeros.
This article explains Doris's data partitioning, the four join methods (Broadcast, Shuffle, Bucket Shuffle, and Colocate), runtime filter mechanisms and types, join reorder strategies, and practical tuning recommendations to improve query performance in the Doris MPP database.
The VS Code 1.93 release brings a major shift to ESM modules, fixes SQL language highlighting, adds configuration file UI improvements, introduces Django test support, and enhances IntelliSense and experimental network inspection for JavaScript and Node.js developers.
This tutorial explains how to connect to a MySQL database using PHP's mysqli extension, execute queries, and retrieve results with mysqli_fetch_assoc, providing step-by-step code examples for connection, querying, and a complete script.
This article walks through a real‑world MySQL InnoDB deadlock case, detailing log analysis, reproducing the issue with test data, explaining gap and insert‑intention locks, and presenting a practical solution that checks existence before updating or inserting to prevent deadlocks.
This guide explains how to export MongoDB collections to JSON, load them into DuckDB, generate compatible table schemas, and then transfer the data efficiently into MySQL using DuckDB as an intermediate processing engine.
This tutorial walks you through the fundamentals of Apache Iceberg, its architecture and key features, explains why it’s advantageous for lakehouse workloads, and provides a step‑by‑step Docker‑Compose setup to integrate Iceberg with StarRocks for fast, ACID‑compliant analytics on real‑world taxi data.
This article explains what database deadlocks are, illustrates typical deadlock scenarios, outlines common causes such as resource competition and unordered locking, and presents practical solutions including timeout mechanisms, consistent lock ordering, deadlock detection, lock granularity reduction, and optimistic concurrency control, complemented by example SQL code.
This article explains why high‑cardinality GroupBy queries are slow, describes SLS's underlying aggregation pipeline, and shows how adjusting session parameters and enabling high‑cardinality optimizations can reduce query times from dozens of seconds to just a few seconds across three real‑world test scenarios.
This article examines how InnoDB acquires shared record locks during a duplicate‑primary‑key INSERT, explains the underlying lock‑checking mechanism, demonstrates lock behavior with concurrent DELETE and INSERT transactions, and provides SQL examples and performance_schema queries to illustrate the process.
This article explains the challenges of executing full‑table UPDATE statements on large MySQL tables using row‑based binlog replication, analyzes deep pagination issues, and presents a batch‑processing strategy with FORCE INDEX, SQL_NO_CACHE and controlled rate limiting to safely migrate data at scale.
The article examines Oracle’s comprehensive SQL management capabilities—such as parsing, plan caching, process tracing, execution plan handling, optimization, and runtime monitoring—and compares them with the features offered by major Chinese and open‑source databases, highlighting gaps, strengths, and practical recommendations for adopting domestic solutions.
This article demonstrates how a non‑unique indexed column that allows NULL values behaves with the != operator in MySQL, showing why null rows are omitted from result sets, how index usage changes, and how to rewrite queries with OR or UNION to obtain correct results while preserving performance.
The article analyzes a production out‑of‑memory crash triggered by a pagination bug that misused the OFFSET parameter, explains why the error escaped testing and code review, and presents corrected pagination techniques for Oracle, MySQL and MyBatis to prevent similar failures.
This article compiles a series of technical interview questions and answers covering C++ and Go language differences, thread communication methods on Linux and Windows, stack versus heap memory, orphan processes, read‑write locks, Go goroutine concurrency, TCP three‑way handshake, and a sample SQL query for gender‑based grouping.
GaussDB for MySQL introduces a Limit‑Offset pushdown optimization that moves offset calculation to the InnoDB storage engine, eliminating unnecessary row transfers and index‑back‑table lookups, thereby dramatically reducing query latency for large offsets as demonstrated by TPC‑H benchmarks.
This article recounts L's attempt to change a train ticket, analyzes seat availability across four segments, and presents a minimal SQL model with table creation, data insertion, and a query that determines feasible seat combinations, demonstrating how segment‑wise seat allocation can enable complete journey booking.
This article presents six hands‑on Python generative‑AI projects—ranging from a Llama 2 chatbot built with Streamlit and Replicate to natural‑language‑to‑SQL conversion using LlamaIndex and SQLAlchemy—complete with environment setup, required code snippets, deployment tips and resource links for further exploration.
This article provides a step‑by‑step analysis of MySQL 8.0.32 InnoDB deadlock logs, covering preparation, log extraction, auxiliary information, transaction details, row‑lock structures, and the final summary, with full SQL examples and code snippets for deep understanding.
This article provides a comprehensive guide to using MyBatis dynamic SQL tags such as foreach, if, choose, trim, selectKey, and sql fragments, illustrating best practices with detailed code examples for building flexible, error‑free queries and updates in Java backend applications.
This article explains the challenges of performing full‑table updates on large MySQL tables using row‑based binlog replication and presents a step‑by‑step batch‑update strategy that leverages SQL_NO_CACHE, FORCE INDEX, and controlled pagination to minimize binlog volume and maintain performance.
This guide walks through practical MySQL 8.0 security hardening steps using SQL statements to enforce strong passwords, enable SSL, configure connection control, manage user authentication, set file permissions, and verify encryption of logs and tables.
This article explains MySQL index fundamentals—including hash tables, ordered arrays, and B+‑tree structures—covers index maintenance, demonstrates how indexes improve query execution, discusses best practices such as left‑most prefix, covering indexes, index push‑down, and unique indexes, and provides practical tips for index selection, avoiding index loss, and using EXPLAIN for performance tuning.
This article examines the advantages and disadvantages of using stored procedures in relational databases, discusses common pitfalls and vendor‑specific issues, provides guidance for Oracle stored procedures, and offers practical recommendations on when and how to apply them effectively.
This guide provides a comprehensive overview of Apache Paimon’s architecture, key features, and advantages, explains how to integrate it with StarRocks for real‑time lakehouse analytics, and walks through a complete quick‑start setup including component installation, Flink and Kafka deployment, data ingestion, table creation, and query execution with time‑travel support.
This article explains the challenges of loading historical chat messages in an instant‑messaging app and demonstrates why traditional offset pagination fails, then introduces cursor‑based pagination with SQL examples and best‑practice guidelines for reliable incremental loading.
Pinterest migrated its Partner Insights analytics from Druid to StarRocks, achieving a 50% reduction in p90 latency, a six‑fold cost‑performance improvement, and simplified data ingestion, illustrating the benefits of a modern MPP database for real‑time ad analytics.
Despite the hype around NoSQL, this article argues that SQL and relational databases remain the superior choice for transactional data, tracing two decades of research, highlighting the limitations of NoSQL, and showing how modern databases now incorporate the best features of both worlds.
This article introduces an AI‑driven Smart Report Assistant Bot that automatically converts uploaded Excel files into recommended charts, allows users to customize reports, and details the underlying workflow—including Excel parsing, LLM‑generated SQL, dynamic table creation, chart rendering with ECharts, and image‑merging plugins.
This article provides an in‑depth overview of MySQL fundamentals, covering join types, normalization, data types, index structures, locking mechanisms, transaction isolation, MVCC, replication, sharding, and practical performance‑tuning techniques for large‑scale databases.
This article details the step‑by‑step process of switching a SpringBoot‑MyBatisPlus application from MySQL to PostgreSQL, covering driver integration, JDBC configuration changes, numerous SQL and type‑conversion pitfalls, and provides ready‑to‑run PostgreSQL scripts for bulk column adjustments and default values.
This article provides a detailed tutorial on database sharding (分库分表), covering concepts, common sharding strategies, a step‑by‑step MyCAT configuration, Java JDBC implementation, and key considerations such as data distribution, complexity, transaction handling, and development cost.
This article introduces the PHP mysqli_query function, demonstrates how to connect to a MySQL database, execute SELECT, INSERT, UPDATE, and DELETE statements, and explains result handling with example code, providing a concise guide for backend developers working with relational databases.
This comprehensive guide explains how to boost WordPress site speed and SEO by applying PHP‑based strategies such as query optimization, regular database maintenance, custom tables, option‑table tuning, indexing, and caching, complete with practical code examples.
This article reviews two decades of database research, analyzing the rise and decline of various data models—from hierarchical and relational to NoSQL, vector, and graph databases—while highlighting how AI, cloud, and hardware advances are reshaping DBMS architecture and predicting which approaches will dominate tomorrow’s data landscape.
This article introduces Easy‑Query, a Java ORM that provides a strongly‑typed, chainable query syntax covering basic CRUD, pagination, joins, sub‑queries, native SQL fragments, group‑by, custom VO mapping and high‑performance encryption, illustrated with extensive code examples and usage notes.
This article provides a comprehensive guide to MySQL's DQL, DML, and DDL sections, explaining execution order, core clauses, and practical code examples for creating, altering, and managing databases and tables.
This article explains what constitutes a slow SQL query, how to detect and analyze it using MySQL tools, and provides practical optimization steps—including indexing, business logic review, caching, scheduling, and partitioning—to dramatically reduce execution time.
This article examines why a scalar subquery in an OceanBase 3.2.3.3 SQL statement takes over 1000 seconds, breaks down the execution plan, identifies the costly nested-loop behavior, and presents a rewrite using WITH and LEFT JOIN that reduces the cost from 788 million to 3.6 million and cuts runtime to about 10 seconds.
This article presents twenty practical SQL optimization techniques—including index usage, selective column queries, avoiding functions in WHERE clauses, replacing subqueries with joins, limiting result sets, using EXISTS, batch inserts, covering indexes, proper data types, pagination, and transaction management—to significantly improve query performance and database efficiency.
This tutorial walks through designing a complete logical and physical database model for a Google Calendar clone, covering entities, attributes, relationships, handling all‑day and time events, recurrence rules, slots, and SQL table creation, while illustrating minimal‑modeling techniques.
This article examines why MySQL pagination using large LIMIT OFFSET values becomes increasingly slow, explains the underlying scanning behavior, and presents six practical optimization techniques—including indexed subqueries, cursor‑based pagination, column selection, partitioning, caching, and hardware considerations—to dramatically improve query performance on massive tables.
GaiaDB 4.0 introduces cloud‑native parallel query execution, allowing the same MySQL‑compatible cluster to handle both OLTP and OLAP workloads with up to fourteen‑fold speedup on complex queries, configurable via global parameters or query hints, and adds columnar indexes for further analytical acceleration.
This article explains MySQL’s composite index leftmost‑prefix rule, demonstrates how the Skip Scan Range Access Method introduced in MySQL 8.0.13 can use indexes without following the rule, provides step‑by‑step examples, code snippets, and discusses its implementation, benefits, and limitations.
During a phone screening I noticed a candidate’s delayed yet suddenly polished answers, especially a textbook‑style SQL query, which matched GPT output exactly, leading me to suspect and confirm the use of AI in the interview.
Covering workflow history, this article explains BPMN standards, outlines core modeling concepts, and demonstrates a practical SmartEngine integration that deploys BPMN 2.0 XML, highlights visual execution benefits, notes limitations in error handling and concurrency, and advises its use for moderately complex, frequently changing processes.
GaiaDB 4.0 introduces a zero‑ETL parallel query engine that leverages multi‑core CPUs to run OLTP and OLAP workloads on a single cluster, dramatically improving complex query performance while offering configurable parallelism and a rich set of parallel operators.
This article explains how to speed up SQL queries while keeping statements concise, covering the query processing workflow, optimization goals, and practical tips such as selecting specific columns, avoiding DISTINCT, using proper indexes, preferring EXISTS over COUNT, limiting result sets, favoring WHERE over HAVING, and replacing correlated subqueries with joins.
This article investigates whether MySQL VARCHAR(50) and VARCHAR(500) occupy identical storage and how column length impacts query performance, providing step‑by‑step table creation, bulk data insertion, storage queries, and detailed benchmark results with analysis of the underlying causes.
A single runaway SQL caused a 12‑hour outage of a core Oracle system; the article walks through the fault symptoms, AWR and ASH analysis, abnormal SQL identification, execution‑plan changes caused by partition operations, and the step‑by‑step remediation that restored performance.
This article explains how to obtain the SQL ID, create an outline bound to that ID, and correctly verify its effect in OceanBase, highlighting common pitfalls such as using the wrong schema or relying on EXPLAIN, and provides practical code examples for each step.
This article presents a real‑world case of optimizing a high‑concurrency transaction‑statistics API, detailing diagnosis of a 30‑second latency, SQL refactoring with array_agg and unnest, Java‑level improvements, and Caffeine caching, while also sharing promotional offers for ChatGPT services and a developer community.
This comprehensive ODPS (MaxCompute) development guide serves as a mini‑encyclopedia, detailing common parameter tuning, built‑in SQL functions, step‑by‑step Java UDF creation, job lifecycle insights, and practical performance‑optimization techniques such as parallelism adjustment, map‑join hints, and small‑file mitigation.
This article explains MySQL's hash join algorithm, compares it with nested‑loop joins, shows how to read execution steps and EXPLAIN output, discusses extended hash‑join scenarios, and weighs the trade‑offs of performing multi‑table joins inside the database versus in application code.
This article explains how the LIMIT clause can cause massive I/O and CPU overhead in MySQL queries, analyzes the underlying B+‑tree index structure and execution plans, and presents practical optimization techniques such as covering indexes, sub‑queries, and partitioning.
This guide walks through installing pypika and demonstrates how to construct SELECT, WHERE, GROUP BY, JOIN, subqueries, set operations, INSERT, UPDATE, and database‑specific adaptations using Python code, showcasing concrete examples and the full query‑building workflow.
This guide provides a comprehensive, beginner‑to‑advanced reference for ODPS (MaxCompute) SQL, covering table creation, DDL/DML commands, query syntax, join hints, MySQL‑to‑ODPS synchronization, one‑click and custom imports into Hologres, and scheduling variables for automated data pipelines.
This article explains the motivation behind creating the Easy-Query ORM for Java, demonstrates its fluent query capabilities—including single‑record, list, pagination, joins, sub‑queries, group‑by, native SQL and dynamic condition features—provides extensive code examples, and shares repository links for further exploration.
The article outlines a cross‑cluster upgrade to StarRocks 2.5.13, evaluating resource and stability costs, and presents two migration schemes—using external tables and a Flink connector—along with planning, parallel execution, validation steps, and results showing successful migration of over 10 TB at 2 Gb/s across ten nodes, while noting future automation and CDC enhancements.
This article presents ten crucial intermediate‑to‑advanced SQL concepts—including CTEs, recursive queries, temporary functions, CASE pivots, EXCEPT vs NOT IN, self‑joins, ranking functions, delta calculations, cumulative totals, and date‑time manipulation—each explained with clear examples and code snippets.
This guide explains how to enable row store, configure short query path, and use PreparedStatement in Doris to reduce I/O and CPU overhead for high‑concurrency primary‑key point queries, including DDL examples, JDBC usage, row cache settings, performance tips, and verification methods.
The article reveals that MyBatis’s OGNL treats a single‑character literal like '1' as a Character, causing equality checks against String values in if tags to fail, and shows how using double quotes, toString(), or proper type conversion resolves the comparison pitfall.
This article explains how implicit type conversions in PostgreSQL can cause index loss, drastically misestimate row counts, and force inefficient join strategies, illustrating the issue with concrete examples, detailed execution plans, and step‑by‑step optimizations that restore index usage and boost performance thousands of times.
This article presents a detailed experimental study of OceanBase's Arbitration Service in a 2F1A (two full‑function replicas plus one arbitration node) configuration, examining how the system behaves when one or both full‑function replicas fail, how log‑stream degradation and permanent offline mechanisms work, and how normal service is restored after node recovery.
Huolala’s data testing platform tackles massive data volume, complexity, and quality challenges by automating test case generation, execution, monitoring, and alerting across multiple storage systems, dramatically reducing testing cycles from five days to 1.5 days and saving over 800 person-days.
This article explains why upgrading MySQL from 5.7 to 8.0 can cause an "Illegal mix of collations" error in FIND_IN_SET queries, analyzes the root cause, and presents three practical solutions—adjusting server parameters, altering table collations, or rewriting the SQL—to restore correct operation.
This guide explains the fundamentals of Druid as a Java database connection pool, how to add required Maven dependencies, configure various Druid properties and filters for SQL and web monitoring, enable slow‑SQL logging, integrate Spring AOP monitoring, remove the default Alibaba advertisement, and retrieve monitoring data via the DruidStatManagerFacade API.
This guide explains how to integrate Alibaba's Druid connection pool into Spring Boot, covering basic concepts, Maven dependencies, essential configuration properties, built‑in filters, monitoring pages, SQL and slow‑query logging, Spring AOP monitoring, ad removal, and programmatic access to Druid statistics.
This article explains why new data systems are needed, introduces Apache Arrow and its columnar in‑memory format, describes dynamic read‑time modeling, outlines the system’s execution flow, storage and indexing strategies, and shares practical tips and extensions for building scalable big‑data solutions.
The article walks through three Flink streaming pitfalls—data‑skew‑induced back‑pressure, lost watermarks after interval joins, and ineffective group‑by causing duplicate rows—and shows how to resolve them with two‑stage distinct aggregation, hash‑based key distribution, processing‑time windows or split jobs, and mini‑batch buffering.
This article explains StarRocks' compaction process that merges multiple data versions into larger files to reduce I/O, details the scheduler and executor roles, shows how to monitor and control compaction via SQL commands, and provides tuning parameters and best‑practice recommendations for optimal performance.
This article details how to switch a SpringBoot + MybatisPlus + MySQL project to PostgreSQL by adding the driver, updating JDBC settings, handling numerous SQL and type‑conversion pitfalls, and provides ready‑to‑run PostgreSQL scripts for bulk column modifications and default‑value settings.
This article explains why MySQL columns defined as INT(10) appear as INT after upgrading from 5.7 to 8.0, demonstrates the change with reproducible examples, and discusses the storage limits, proper selection, and display‑width behavior of integer types including ZEROFILL.
This guide details how to use mysqldump and related commands to back up single or multiple MySQL databases, specific tables, compress backups, include drop statements, export only schema, and automate the process with scripts and cron jobs, plus instructions for restoring data.
This article presents twenty practical SQL optimization techniques—including index usage, selective column queries, avoiding functions in WHERE clauses, replacing subqueries with joins, limiting result sets, using prepared statements, proper data types, and transaction handling—to improve query performance and overall database efficiency.
An in‑depth comparison of PostgreSQL and MySQL covers their histories, ACID compliance, performance, scalability, advanced features, security, backup strategies, installation on Linux, and provides practical SQL code examples to help full‑stack developers choose the right open‑source relational database for their projects.
This article explains nine common reasons why MySQL indexes become ineffective and offers concrete solutions—including query rewrites, data type alignment, index redesign, and maintenance tips—to help developers improve database query performance.
This article walks through a real‑world OceanBase OMS migration case where adding an OCP association triggers a “Server Internal Error”, detailing log analysis, metadb schema inspection, OAT deployment checks, the impact of the MySQL “-f” flag, a temporary fix, and the root‑cause conclusion.
This article explains how to design a MySQL table for product sales statistics, initialize massive test data, and compare several query strategies—including loop queries, OR‑concatenated dynamic SQL, mixed filtering, and the SQL‑92 row‑comparison technique—to efficiently retrieve sales figures for multiple business units and their products without violating development constraints.
MySQL offers a comprehensive set of string functions—including lower, upper, concat, substring/substr, length, char_length, instr, lpad, rpad, trim (with leading, trailing, both options), replace, repeat, reverse, and format—that enable case conversion, concatenation, extraction, padding, trimming, substitution, repetition, reversal, and numeric formatting of textual data.
This article compares Doris and ClickHouse across architecture, table creation, ecosystem integration, management tools, query performance, and join capabilities, offering practical guidance on how to choose the right database based on your specific data processing and operational requirements.
On June 3, 2024 DuckDB announced its 1.0.0 "Snow Duck" release, highlighting a custom single‑file storage format with full transaction semantics, backward compatibility, massive community adoption, and a roadmap focused on stability, extensions, and continued open‑source development.
This article explains how to use PHP's mysqli_num_rows function to retrieve the number of rows returned by a MySQL query, including a complete example with connection handling, error checking, and output of the row count.
This article explains why plain LIKE searches become slow on large MySQL tables, shows how prefix patterns and string functions like LOCATE, POSITION, INSTR, and FIND_IN_SET can leverage indexes, and provides a comprehensive list of thirty practical SQL optimization techniques to dramatically improve query performance.
This article presents a configurable OLAP framework that streamlines multi‑dimensional, multi‑metric analysis by separating core and interaction domains, using metadata‑driven modeling, runtime request handling, and Maven‑based integration to accelerate development and improve query performance.
