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 provides a detailed technical overview of Presto 2.0, the native C++ query engine built on the Velox library, covering its motivation, vectorized architecture, memory management, performance benchmarks from Meta and IBM, and deployment practices for large‑scale data warehouses.
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.
This article presents ten essential advanced MySQL concepts—including CTEs, recursive CTEs, temporary functions, CASE‑WHEN pivots, EXCEPT vs NOT IN, self‑joins, ranking window functions, delta calculations, running totals, and date‑time manipulation—each explained with clear examples and practical SQL snippets.
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.
This article details how to switch a SpringBoot‑MyBatisPlus‑MySQL application to PostgreSQL, covering driver addition, JDBC configuration changes, numerous SQL and type‑conversion pitfalls, helper scripts for bulk column updates, and essential migration notes.
The article defines large database transactions, explains their performance and replication risks, offers practical guidelines to avoid them, demonstrates a MySQL example that creates and repeatedly inserts data to illustrate growing execution time, and discusses challenges and solutions for handling massive tables in high‑traffic environments.
A daily million‑order e‑commerce platform only generates about 30 TPS, making inventory‑deduction and oversell concerns trivial and solvable with a single atomic SQL update rather than complex distributed solutions.
This article explores how to retrieve sales statistics for multiple business units and their associated products in MySQL, compares loop‑based, OR‑concatenated, mixed‑filter, and SQL‑92 row‑comparison techniques, and explains why the row‑comparison method was ultimately chosen for its performance and compliance with development constraints.
This guide walks you through using Alibaba Cloud EMR Serverless Spark to ingest OSS‑HDFS audit logs, create source, detail, and summary data‑warehouse tables with Spark SQL, orchestrate the tasks in a workflow, and schedule daily IP‑traffic analysis, complete with code snippets and UI screenshots.
This article walks through a real‑world Oracle index failure, explains why indexes become UNUSABLE, shows how to clean residual metadata, rebuild the index safely, outlines common causes for both regular and partitioned tables, and provides SQL scripts for monitoring invalid indexes.
Learn how to create a Go-based command-line tool that accepts user-supplied parameters to connect to an Oracle database using the loginWithServiceName function, execute arbitrary SQL queries, and display results, complete with full source code, flag parsing, and error handling.
The article compares MySQL and PostgreSQL, covering their histories, architecture hierarchies, benchmark performance on SELECT/INSERT/UPDATE, suitable use cases, and summarizes PostgreSQL’s advantages and disadvantages relative to MySQL, providing guidance for choosing the appropriate database.
Amid the rise of vector databases, AI, and cloud computing, this article explains how PostgreSQL’s flexibility, extensibility, and open‑source community are reigniting SQL’s relevance in 2024, uniting OLTP and OLAP workloads and positioning PostgreSQL as the versatile backbone for modern data management.
SQLite 3.46.0 introduces extensive PRAGMA enhancements, new date‑time modifiers, support for underscore digits in numeric literals, a json_pretty() function, significant query planner optimizations, improved JSON handling, and the ability to use large hexadecimal literals as column defaults, all while maintaining its public‑domain, self‑contained nature.
The article explains what constitutes an SQL problem, presents a typical case of performance degradation caused by mismatched character sets, and outlines how the SQLE platform can help developers, testers, and DBAs systematically manage and improve SQL quality throughout the software lifecycle.
This article explains the concept of MySQL master‑slave replication, outlines its advantages and drawbacks, and provides a step‑by‑step guide—including downloading MySQL 8.0, configuring my.cnf on master and slave servers, creating replication users, setting up replication parameters, and using Laravel for read‑write splitting—complete with all necessary shell and SQL commands.
This article presents a comprehensive guide to MySQL table design, covering naming conventions, appropriate data types, primary key strategies, field length choices, logical deletion, common fields, indexing rules, normalization trade‑offs, storage engine selection, time type recommendations, and SQL optimization tips.
This article demonstrates how to diagnose and resolve repeated UPDATE failures in OceanBase by extracting information from Top SQL, log files, and the gv$sql_audit view, identifying error codes such as 6005, 6003, and 6212, and applying corrective actions like adjusting timeout settings, killing lock‑holding sessions, and checking lock statistics.
This article explains the different types of InnoDB row locks—including shared, exclusive, gap, next‑key, and insert‑intention locks—by showing preparation steps, SQL examples, lock‑mode details, and how they interact under repeatable‑read isolation in MySQL 8.0.32.
The article explains why using IN and NOT IN in SQL queries can lead to poor performance, index bypass, unexpected results with NULL values, and subtle bugs, and it recommends safer alternatives such as EXISTS, NOT EXISTS, and JOIN constructs.
This article presents a systematic benchmark comparing ClickHouse local (single‑node) tables and distributed tables across three data volumes—≈60 billion, 5 billion and 50 million rows—using a variety of aggregation and filter queries, and reveals that distributed tables dominate at large scale while the gap narrows as the dataset shrinks.
This article presents a detailed case study of refactoring a complex, performance‑critical SQL query used in a JDL routing system, demonstrating step‑by‑step formatting, hierarchical decomposition, and optimization techniques such as layer merging, predicate push‑down, join simplification, and index tuning to dramatically reduce execution time.
This article demonstrates how to refactor a complex, production‑level SQL query used in a routing system by formatting, decomposing into layers, merging temporary tables, pushing predicates, optimizing joins, and validating performance, ultimately reducing nesting from four levels to one and cutting execution time from 4.75 s to 0.6 s.
This article explains common scenarios of implicit type and charset conversions in MySQL, demonstrates how they cause index loss and performance degradation through detailed examples and EXPLAIN output, and provides practical guidelines to prevent such conversions in production environments.
This article provides an in‑depth tutorial on the sqlx package for Go, covering installation, type design, database connection methods, query execution, named parameters, transaction handling, prepared statements, unsafe scanning, field‑mapping customization, and practical code examples to simplify working with relational databases.
A quiet Saturday turned into a disaster when a simple UPDATE query accidentally deleted every order in production, prompting a rapid, step‑by‑step recovery, a post‑mortem analysis of the root causes, and a set of hard‑won operational lessons for any engineering team.
This article presents a comprehensive collection of MySQL performance guidelines, covering query writing, index usage, join strategies, data types, batch operations, and execution plan analysis to help developers write faster and more reliable SQL statements.
This article presents UCloud's USDP‑based streaming data lake warehouse solution that leverages Flink for real‑time processing and Paimon for lake storage, detailing its architecture, advantages, practical scenarios, and providing complete SQL and Flink CDC code snippets for end‑to‑end implementation.
This article explains how InnoDB full‑text search can replace LIKE% fuzzy queries by using inverted indexes, covering index structures, creation syntax, MATCH() AGAINST() modes, practical demos, query expansion, and index removal, while noting when to prefer external search engines.
This article presents a series of B2B testing scenarios—upload validation, rule retrieval, rule‑group matching, and similar rule conditions—demonstrating how targeted code review, precise test case design, and SQL verification can dramatically reduce test effort while uncovering hidden bugs.
This article explains MySQL's transaction concepts, the four isolation levels defined by the SQL standard, how each level prevents dirty reads, non‑repeatable reads, and phantom reads, and provides practical commands and example experiments to configure and observe their behavior in InnoDB.
This comprehensive guide explains MySQL transaction fundamentals, defines dirty reads, non‑repeatable reads and phantom reads, compares the four isolation levels, shows how to query and set them, and details the underlying locking mechanisms such as MVCC and next‑key locks.
Oracle announced its upcoming AI coding assistant, Code Assist, tailored for Java, Oracle Database, NetSuite scripts, and Oracle Cloud, offering chat-driven code suggestions, documentation generation, and security scanning across VS Code and JetBrains IDEs, while emphasizing multi‑language support and data privacy.
Donald Chamberlin, co‑author of SQL, explains why he now supports the NoSQL movement, describing how modern web applications demand scalability that traditional relational databases struggle to provide, and discusses emerging query languages like SQL++ (PartiQL) that bridge the gap between relational and document models.
This article explains how Lazada’s internal selection platform leveraged Hologres and the RoaringBitmap compression algorithm to dramatically reduce storage costs, accelerate set operations, and break the 200,000‑item pool limit, achieving up to a 99% speed improvement in scheduling.
This article explains how MySQL table‑level locks and metadata locks (MDL) can block both reads and writes, describes the two lock implementations, their types, the interaction with LOCK TABLES and FLUSH TABLES, and provides practical queries for diagnosing lock‑related blocking issues.
The OceanBase community offers a detailed, eight‑session DBA tutorial covering the platform’s architecture, deployment, migration, performance tuning, diagnostic tools, and ecosystem components, complemented by live streaming, hands‑on exercises, certification incentives, and real‑world user testimonials to help developers and DBAs master this distributed database.
Oracle’s latest release, Database 23ai, upgrades its 23c platform with AI-driven vector search, RAG capabilities, and enhanced JSON and graph querying, positioning the database as a unified, secure, and scalable solution for handling structured, semi‑structured, and unstructured data across cloud and on‑premises environments.
This article analyzes common anomalies caused by the PageHelper pagination plugin in a Java backend project—such as duplicate user registration, limited query results, and password‑reset errors—traces them to ThreadLocal misuse, explains the internal workflow of startPage, getLocalPage, and clearPage, and offers practical recommendations to avoid these pitfalls.
This article explains eight key reasons why SQL and relational database systems remain the dominant data management solution half a century after their invention, covering performance, maturity, community, simplicity, popularity, open‑source options, powerful query capabilities, and their continued relevance alongside newer technologies.
This article explains how to create a custom annotation and a MyBatis‑Plus interceptor that automatically injects data‑permission WHERE clauses into SQL statements, supports role‑based scopes, and shows both basic and advanced implementations with full Java code examples.
This guide explains how MySQL's InnoDB engine handles transactions and ACID properties, demonstrates exclusive and shared locking mechanisms, shows how to use connection pools, and provides reusable Python code for robust database operations, all illustrated with clear SQL and Python snippets.
This article outlines eight key reasons why SQL and relational databases remain the dominant data management solution half a century after their invention, covering processing power, proven reliability, community support, simplicity, widespread adoption, open‑source growth, practical query power, and their role alongside NoSQL technologies.
This article explains the purpose and inner workings of various database indexing algorithms—including B‑Tree, Hash, Bitmap, and Full‑Text indexes—illustrates their strengths and weaknesses with SQL examples, and provides guidance on when to choose each type for optimal query performance.
This article explains how to dramatically improve MySQL insert speed by adjusting database settings, choosing the MyISAM engine, and applying batch insertion methods such as concatenated SQL, transaction‑wrapped inserts, and MyBatis‑Flex saveBatch, complete with test code and performance results.
This article explains MySQL's architecture, how to view storage engines and variables, demonstrates creating various indexes, describes why SQL optimization is needed, details the EXPLAIN plan keywords and index types, and provides practical best‑practice tips for single‑ and multi‑table query performance improvements.
This guide compiles essential MySQL interview topics—including definitions, differences from Oracle and SQL Server, index types and usage, B‑tree vs B+‑tree structures, deadlock handling, and view concepts—providing a systematic overview for developers preparing for database interviews.
This article walks through the end‑to‑end lifecycle of a SQL task in a big‑data environment, covering creation, scheduling metadata, instance generation, resource allocation, ODPS execution, and final processing on the Fuxi distributed engine.
This article explains the concepts, formulas, and step‑by‑step implementation of a user‑retention analysis model, covering both Hive‑based offline processing and ClickHouse‑accelerated real‑time queries, complete with SQL examples, architecture diagrams, and practical optimization tips.
This article introduces Flyway as a database migration tool, explains its migration types and common commands, demonstrates how to integrate Flyway with Spring Boot using Maven and YAML configurations, provides example SQL migration scripts and logs, and briefly mentions related community promotions.
This guide explains the step‑by‑step SQL execution sequence, offers practical tips for optimizing multi‑table joins, and provides comprehensive indexing recommendations to improve query performance and reduce resource consumption.
This article introduces the Perfetto web UI, explains its four main sections, walks through essential navigation shortcuts, and shares practical techniques such as viewing wake‑up sources, critical paths, pinning threads, inspecting CPU info, buffer usage, timeouts, and logs to help Android developers efficiently analyze performance traces.
This article explains why using IN and NOT IN in SQL queries often leads to poor performance and incorrect results, and demonstrates safer alternatives such as EXISTS, NOT EXISTS, and JOINs with practical code examples.
This article demonstrates how the MySQL LIKE clause can give misleading results when filtering triggers, walks through a step‑by‑step lab that creates tables and triggers, shows the pitfalls of LIKE, and presents a safer WHERE‑based method for retrieving trigger definitions.
This article explains why using OFFSET and LIMIT for pagination becomes inefficient on large tables, illustrates the performance impact with examples, and proposes a cursor‑based pagination alternative that leverages indexed primary keys to achieve faster, scalable queries.
A Java developer recounts a production MySQL outage caused by a faulty batch insert, walks through failed delete and lock‑free attempts, then details a series of table‑recreation, truncate, and index‑rebuilding steps that finally restored data integrity and performance.
This article explains the logical execution order of SQL statements—including FROM‑JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY and LIMIT—illustrates each step with examples and diagrams, and clarifies common misconceptions about clause sequencing.
