This article explains how PostgreSQL and MySQL store IP addresses using native data types and functions, showing example table definitions, insertion and query statements for both IPv4 and IPv6, and highlights the advantages of using specialized types over plain VARCHAR.
This article explains why using LIMIT on sharded tables can crash databases, then introduces three practical pagination tricks—anchor pagination, shard‑mark method, and reverse pagination—detailing their workflows, performance benefits, and common pitfalls for backend developers.
The article explains how using IN and NOT IN in SQL can lead to full‑table scans, ignore indexes, produce incorrect results when columns mismatch or contain NULLs, and shows safer alternatives like EXISTS, NOT EXISTS, and JOIN with concrete code examples and benchmark timings.
This article explains how MySQL OR conditions can cause index loss, demonstrates typical scenarios with multi‑field, range, and dynamic filters, and provides practical solutions such as using UNION/UNION ALL or adding appropriate indexes to restore efficient query performance.
This article explains how veDB-Search provides a one‑stop hybrid search service that combines vector, full‑text, and scalar queries using only SQL, reducing development complexity and operational overhead for AI agents and large‑scale AI applications.
This article introduces the BIRD-CRITIC benchmark, a comprehensive SQL diagnostic dataset spanning multiple dialects, evaluates large language models' ability to repair real-world database queries, and discusses its design, multi‑dialect support, data quality processes, and experimental results.
The article explains how Spring Boot’s @Sql annotation lets you declaratively run SQL scripts before or after test methods to set up or clean up test data, covering placement, default behavior, configuration options, execution timing, transaction handling, and common pitfalls.
September 2025 SCALE released its latest SQL‑LLM leaderboard, adding Moonshot AI’s Kimi‑K2‑Instruct‑0905 model, detailing its scores on SQL understanding, optimization and dialect conversion, unveiling platform upgrades for fine‑grained metric ranking and visual model comparison, and offering expert analysis of strengths and weaknesses.
This article explains why storing plain‑text sensitive fields such as phone numbers is unsafe, then demonstrates how to use a custom MyBatis TypeHandler to automatically encrypt data on insert and decrypt it on query, complete with code samples, configuration steps, and test results.
This article explains how to use Spring Boot's @Sql annotation for integration testing, covering script execution, statement injection, execution phases, grouping, merge modes, and configuration with clear code examples and screenshots.
A senior backend engineer recounts how early database abstraction layers caused massive performance regressions across three real‑world systems, illustrates the pitfalls with Go and Node.js code examples, and shares concrete guidelines for letting databases do what they do best.
This article explains the five MySQL temporal column types—DATE, DATETIME, TIMESTAMP, YEAR, and TIME—detailing their storage size, valid ranges, typical use‑cases, timezone behavior, and the 2038 problem, helping developers pick the most suitable type for any scenario.
This guide provides a comprehensive collection of essential MySQL commands covering database creation, table schema management, data manipulation, user permissions, performance monitoring, and backup/restore procedures, offering a practical reference for developers and administrators.
This article explains how MySQL's OR conditions can cause index loss, provides concrete query examples, analyzes why the optimizer falls back to full table scans, and offers practical solutions such as rewriting with UNION or adding missing indexes to restore efficient index usage.
This article compares MySQL's INT, TIMESTAMP, and DATETIME column types, explaining their limits, default behaviors, timezone handling, performance implications, and best‑practice recommendations for storing temporal data in modern applications.
This article explains the structure of KingbaseES license files, distinguishes commercial and non‑commercial license types, describes the actual behavior of the database when a license expires, and provides step‑by‑step workarounds such as OS date tweaking, license inspection, file replacement, and server reload commands.
This article explains MySQL’s native JSON data type introduced in 5.7, covering its binary storage format, built‑in functions, generated‑column indexing, performance advantages over string storage, and detailed CRUD examples with SQL syntax and practical code snippets.
Learn how to efficiently handle upserts in MySQL by leveraging the native DUPLICATE KEY UPDATE feature, simplifying code with a custom MyBatis‑Plus AbstractMethod injector, complete with thread‑safe locking, SQL generation, and integration steps for seamless backend development.
PostgreSQL’s index system, officially called a B‑Tree, actually implements B+‑tree functionality, storing only TIDs in leaf nodes, using linked leaf pages for efficient range queries, and includes optimizations like deduplication, index‑only scans, and reverse‑key indexes to boost performance.
This article explains why using traditional LIMIT pagination on sharded databases can cause full‑shard scans and memory‑intensive sorting, and introduces three practical alternatives—anchor pagination, shard‑mark pagination, and reverse pagination—detailing their implementation steps, performance benefits, and common pitfalls to avoid.
This article explains how MySQL processes the KILL command, detailing the interaction between threads, the role of the kill flag, and the is_killed() function, all illustrated with diagrams and code examples to help readers grasp thread termination internals.
SQLite, often dismissed as a toy, proves its robustness by adding powerful SQL capabilities in 2018—including boolean literals, window functions, FILTER clauses, upsert syntax, and column renaming—making it a viable choice for production workloads despite lacking a network layer.
This article walks through MindsDB’s Knowledge Base feature, showing how to map data sources, create vector indexes, perform semantic search, combine multiple sources with SQL joins, automate updates via jobs, and construct powerful RAG pipelines and Data Agents for AI‑driven query answering.
This article explains the functional differences, performance considerations, and practical use cases of MySQL's SELECT DISTINCT and GROUP BY clauses, providing syntax details, example tables, query results, and a comparison table to help developers choose the right approach for deduplication or aggregation tasks.
Learn how to use PHP’s mysqli_query function to connect to MySQL, execute SELECT queries, handle results, and perform INSERT, UPDATE, and DELETE operations, with a complete code example and best‑practice tips for managing database connections and error handling.
This article explains how to verify whether MySQL uses an index with EXPLAIN, outlines eight common reasons why an index may be ignored—including better alternatives, low selectivity, stale statistics, full‑table scans, structural limitations, function usage, invisible indexes—and provides practical commands and hints to resolve each issue.
This guide lists the 15 most common MyBatis pitfalls—from SQL injection and mismatched field mappings to N+1 queries and improper pagination—paired with concrete best‑practice solutions, code snippets, and configuration tips to make your persistence layer safer, more efficient, and easier to maintain.
This comprehensive guide walks you through installing PostgreSQL on Linux, Windows, macOS, and Docker, configuring users, databases, and remote access, using daily psql commands, performing backups, applying advanced features like tablespaces and replication, and optimizing performance with key tuning parameters.
This article explains why the binlog Position may not advance after executing SQL on an OceanBase MySQL tenant, outlines two main causes, and provides step‑by‑step commands to verify binlog task health and identify unsupported statements.
This article walks through installing MindsDB, configuring its unified data access layer, and demonstrates how to query across relational databases, files, and vector stores while injecting AI models—including traditional ML, LLMs, and embedding models—directly into SQL for intelligent data retrieval and analysis.
This article introduces PostgreSQL's powerful features and provides detailed, cross‑platform instructions for preparing the host, configuring network interfaces, adjusting system settings, and installing PostgreSQL via native packages or official repositories on major Linux distributions.
This guide explains how to design clear and maintainable database schemas by using meaningful prefixes, full English names, consistent suffixes for booleans, timestamps, and foreign keys, and by applying best‑practice naming conventions, redundancy strategies, and relationship modeling to improve readability and performance.
This article explains how a migration from MySQL to GoldenDB can cause an INSERT statement to fail due to differing ANSI_QUOTES settings, demonstrates the problematic SQL, and shows how to enable or disable ANSI_QUOTES to resolve the issue and improve SQL portability.
This article explores SQLite's 2018 enhancements—including boolean literals, window functions, FILTER clauses, upsert syntax, and column renaming—detailing how each feature works, its syntax, and practical code examples, while also clarifying limitations compared to other major database systems.
This article compares MySQL and PostgreSQL by outlining their histories, architecture differences, benchmark results for SELECT, INSERT, and UPDATE operations, and discusses which scenarios each database best fits, helping readers decide which system to adopt for their projects.
Learn how MySQL window functions—row_number, rank, and dense_rank—can efficiently rank and rank‑tie data such as class scores or sales amounts, with clear syntax examples, differences in handling duplicate values, and practical SQL queries illustrated with real‑world scenarios.
Bun's latest releases (v1.2.21 and v1.2.22) introduce a unified SQL client for MySQL/MariaDB, SQLite and PostgreSQL, native YAML parsing, massive performance improvements such as a 500‑fold postMessage boost, async stack trace support, enhanced security tools, executable compilation features, and extensive Node.js compatibility fixes.
This article reviews SQLite's 2018 enhancements, covering Boolean literals, window functions, the FILTER clause, upsert syntax, column renaming, and related API changes, with code examples and comparisons to other databases, highlighting its reliability and suitability for production use.
Learn how to diagnose and permanently fix MySQL character set problems—like garbled Chinese text or missing emoji—by checking current settings, altering databases and tables to utf8mb4, updating the my.cnf configuration, and avoiding common pitfalls, all with clear command examples.
This article explores the latest SQLite SQL enhancements introduced between versions 3.22.0 and 3.26.0 in 2018, covering boolean literals, window functions, FILTER clauses, upsert syntax, column renaming, and related API changes, with code examples and practical usage notes.
This article explains the core purpose of MySQL indexes, enumerates twelve typical situations that cause index failure, provides concrete SQL examples and corrections, and shows how to use EXPLAIN to diagnose and verify index usage for better query performance.
Understanding MySQL's clustered indexes—including their definition, selection rules, customization via primary key design, practical creation examples, and key design considerations—helps developers optimize query performance and storage efficiency in InnoDB tables for various workloads.
This article analyzes a real‑world OceanBase‑to‑Oracle incremental sync failure caused by VARCHAR2 length limits, explains the role of NLS_LENGTH_SEMANTICS and MAX_STRING_SIZE, and provides three practical solutions—including extending string size, using CLOB fields, and PL/SQL workarounds—to reliably insert data exceeding 4000 bytes.
This article compares MySQL and PostgreSQL by outlining their architectures, presenting benchmark results that show PostgreSQL’s superior throughput and latency, and discussing the scenarios where each database shines to help developers decide which one fits their project best.
MySQL 8.0 will lose official support in April 2026, prompting a migration to the LTS 8.4 release, which removes several system variables and functions while deprecating others, so DBAs must adjust configurations, tools, and scripts to avoid compatibility issues.
This article explains the N+1 query performance issue, demonstrates a naïve Python/SQLite implementation that triggers N+1 queries, compares its runtime, and then shows how using SQL JOINs, GROUP BY, and nested data structures can dramatically improve query efficiency and reduce latency.
This comprehensive guide introduces MySQL’s core concepts, architecture, and features, then walks you through step‑by‑step installation and configuration on popular Linux distributions—including Rocky, AlmaLinux, CentOS, Ubuntu, Debian, and openSUSE—covering network setup, repository configuration, and post‑install security tweaks.
This article explains why using IN and NOT IN in SQL queries often leads to poor performance and incorrect results, demonstrates common pitfalls with real examples, and shows safer alternatives such as EXISTS, NOT EXISTS, and JOIN to write efficient and reliable database queries.
This guide walks through MySQL's join operations—inner, left, right, and full outer—using a simple grocery‑items schema, showing how to create tables, write correct queries, avoid common pitfalls like unintended cross joins, apply aliases, and filter unassociated rows with WHERE and UNION.
This article analyzes a JD e‑commerce dataset using Python and MySQL to calculate key metrics such as PV, UV, conversion rates, attrition, daily activity, hourly trends, user‑behavior funnels, purchase intervals, retention rates, product sales, and RFM segmentation, and then offers data‑driven recommendations to improve traffic, conversion, and user loyalty.
This article explains the main directory layout of MySQL 8, the locations of data files, configuration files, system databases, and how InnoDB and MyISAM storage engines represent tables and indexes on the file system, including the use of system and file‑per‑table tablespaces.
The article explains how using OFFSET for pagination can cause severe performance degradation as data grows, and demonstrates how switching to keyset (seek) pagination, optionally combined with cursor encoding, index‑only OFFSET, or materialized views, dramatically reduces query latency.
A recent Stack Overflow survey shows PostgreSQL surpassing MySQL in popularity, and the article explains the technical advantages, real‑world performance tests, and evolving use cases that make PostgreSQL the preferred choice for many developers while still acknowledging MySQL’s enduring role for beginners.
The August 2025 SCALE benchmark evaluates new AI models—including the GPT‑5 family, DeepSeek‑V3.1, and the SQLShift tool—across SQL understanding, optimization, and dialect conversion, revealing distinct strengths, weaknesses, and the growing advantage of specialized tools over generic large language models.
This article provides a comprehensive overview of Go's powerful GORM ORM library, covering its core concepts, architecture, internal logic, common pitfalls, practical tips, and advanced features such as soft deletes, transaction handling, batch operations, and integration with trpc-go, helping developers master GORM's inner workings.
This article explains why a MySQL GROUP BY query can degrade from seconds to minutes as data grows, shows how to locate the bottlenecks with EXPLAIN and profiling tools, and provides practical indexing, query rewriting, temporary‑table tuning, batch aggregation, and distributed‑database strategies to restore performance.
This article walks through a real‑world OceanBase ODP binlog connection failure, explains the error message, analyzes possible causes, shows how to trace logs and configuration, and provides a concrete fix by correcting the binlog_service_ip setting and restarting ODP.
This article presents a collection of practical techniques—such as using SELECT TOP, avoiding DISTINCT, preferring IN over OR, indexing key columns, and leveraging stored procedures—to dramatically improve SQL query speed and reduce database load.
The article explains why mastering database backup and recovery—including failure types, logical vs. physical backups, flashback, full and partial restores, a weekly backup strategy, RMAN configuration, shell scripts, and monitoring queries—is essential for avoiding costly mistakes and passing a new‑job probation.
In this detailed case study, the author walks through a real‑world MySQL slow‑query incident, analyzes execution plans, logs, and profiling data, and applies a four‑step optimization strategy—including index creation, query rewriting, covering indexes, and materialized views—to reduce execution time from 20 seconds to 200 milliseconds, achieving over 100× speedup.
This guide explains Apache Flink 2.1's new AI model DDL and ML_PREDICT function, showing step‑by‑step how to create, manage, and invoke AI models in streaming SQL, configure resources with Little's Law, and process risk‑assessment results in real time.
This article explains the fundamentals of database indexes, how they work, common pitfalls such as slow queries and index misuse, and provides practical solutions, best‑practice design principles, and differences between MySQL and PostgreSQL, all illustrated with clear SQL examples and diagrams.
This article compiles 18 essential interview topics for data warehouse engineer roles, covering self‑introduction, architecture layering, dimensional modeling, HDFS operations, Spark vs MapReduce, join implementation, SQL challenges, OLAP selection, real‑time quality assurance, and job transition considerations.
This guide explains how to restore mistakenly deleted MySQL rows by leveraging binary logs, covering binlog activation, mode configuration, locating relevant log files, extracting the original INSERT statements or generating reverse DELETE statements, and using open‑source tools to automate the recovery process.
This article compiles the complete set of interview questions used in ByteDance's data warehouse engineer hiring process, covering three interview rounds with topics ranging from self‑introduction and window functions to data skew, shuffle mechanisms, warehouse architecture, data quality, and governance, plus interviewers' feedback and recommended preparation tips.
The MyBatis SQL Viewer plugin streamlines MyBatis development by converting XML to real SQL, offering parameter mocking, compliance and index checks, execution, stress testing, and both MyBatis and non‑MyBatis modes, all without repeatedly restarting the application.
This article explains three techniques for performing cumulative queries in SQL—using a subquery, a Cartesian product with grouping, and a window function—detailing when each method is appropriate, especially for MySQL versions lacking native window support.
This article examines LeetCode problem 185 – the hardest SQL challenge – by preparing the Employee and Department tables, presenting two solution approaches (a subquery and a window‑function version), benchmarking their performance on large test data, and explaining why window functions are the superior choice in MySQL 8+.
This article details the challenges of migrating 100,000+ SQL statements from BigQuery to MaxCompute for a leading Southeast Asian tech group, explains the new MaxCompute SQL syntax, auto‑partition and ingestion‑time tables, enhanced built‑in functions, and the BigQuery compatibility mode that ensures seamless query behavior.
This comprehensive guide walks you through MySQL fundamentals and advanced features, covering database architecture, storage engines, data types, CRUD operations, complex queries, transactions, indexing, performance tuning, replication, JSON handling, full‑text search, and best practices for security and backup.
This guide explains how to efficiently preprocess, index, and query massive JSON logs in Alibaba Cloud Log Service (SLS) using flattening, ingestion processors, data processing tasks, powerful JSON functions, unnesting, and the AI‑driven SQL Copilot to turn raw log data into actionable insights.
Facing gaps in database education, Beijing Information Technology University launched a GaussDB‑based textbook project to blend theory with hands‑on practice, producing a four‑part, thirteen‑chapter resource that covers fundamentals, SQL, design, and management, while supporting curriculum reform and industry collaboration.
This article provides a comprehensive guide to essential SQL concepts for backend developers and data engineers, covering query execution order, core commands, various join types, common table expressions (including recursive CTEs), window functions, and performance optimization techniques such as table scans and indexing, complete with practical code examples and visual illustrations.
This article explains how to gather JuiceFS access logs using the LogCollector agent, parse and structure them with TLS, design index fields, build analytical dashboards, run advanced SQL queries for write‑IO distribution, sequential‑read ratios, overwrite detection, file‑lifecycle analysis, and set up real‑time monitoring and alerting for performance anomalies.
This tutorial walks you through installing MySQL REST Service on Oracle Linux, configuring the metadata schema, using MySQL Shell in VS Code, loading the component, managing variables, handling X‑protocol settings, creating services, and authenticating via MRS, MySQL Internal, cookie or JWT, with full command‑line examples and code snippets.
This article details the migration of Southeast Asia's leading tech group GoTerra from Google BigQuery to Alibaba Cloud MaxCompute, explaining the performance challenges, the MaxQA accelerator architecture, optimization techniques, resource‑quota strategies, and future enhancements that together double query efficiency while reducing costs.
This guide explains how to efficiently process and analyze massive JSON logs in Alibaba Cloud Log Service (SLS) by flattening data before storage, configuring indexes, leveraging powerful JSON extraction functions, using unnest for array analysis, and employing the AI‑driven SQL Copilot to generate optimal queries, all with practical code examples.
This article explains how PolarDB IMCI integrates vector indexing and embedding directly into the database kernel, offering a unified, transactional, and real‑time vector lifecycle management service that lets developers build RAG knowledge bases and AI applications using only standard SQL, dramatically reducing development and operational complexity.
This article examines how the parameter count of large language models influences their ability to generate and understand SQL, comparing small (1.5B), medium (7B), and large (32B) models through a complex query case study, and highlights the trade‑offs between accuracy, reasoning depth, and resource consumption.
The July 2025 SCALE report evaluates the latest AI large models on advanced SQL tasks, introduces new entrants like Claude 3.5 Sonnet and Gemini 2.5 stable releases, upgrades the benchmark with large‑SQL and domestic database conversion metrics, and provides detailed rankings and analyses of model performance across optimization, dialect translation, and understanding.
This article explains why cross‑system data inconsistencies occur, defines key reconciliation metrics—completeness, timeliness, and automatic repair—and provides step‑by‑step designs for both real‑time (seconds‑to‑minutes) and offline (hour‑to‑day) reconciliation, including message‑driven triggers, batch processing, and SQL examples for detecting and fixing mismatches.
This article explains the performance differences among MySQL's COUNT(*), COUNT(1), and COUNT(column) forms, showing that COUNT(*) and COUNT(1) are equivalent and generally faster than counting specific columns, and provides practical recommendations for accurate row counting.
This article details the real‑world migration of a leading Southeast Asian tech group from BigQuery to MaxCompute, exposing the three major challenges, the data‑driven performance‑optimization methodology, and the concrete techniques—Auto Partition, UNNEST redesign, large‑query graph optimizations, and intelligent tuning—that delivered dramatic cost reductions and query‑speed gains.
This article walks through installing KingbaseES's SQL Server compatible edition, evaluates core T‑SQL features such as GO, PRINT, RAISERROR, THROW, statement terminators, and trailing commas in CREATE TABLE, provides practical code examples, and offers solutions to common Windows installation errors.
This guide walks through diagnosing OceanBase tenant memory exhaustion by tracing error codes, extracting TRACE_IDs, analyzing OBServer logs, querying virtual tables for memory allocation details, and applying a version upgrade to resolve the underlying memory leak.
This article summarizes the major Apache Flink 2.0 updates released in the first half of 2025, covering architecture separation, cloud‑native deployment, AI‑driven agents, SQL enhancements, data integration, operational tools, and performance optimizations for real‑time intelligent computing.
This article uses everyday analogies—herbal shops, libraries, and refrigerators—to demystify databases, covering relational tables, primary/secondary keys, foreign keys, DBMS, and SQL in a clear, beginner‑friendly way.
This article presents ten essential advanced SQL techniques—including EXPLAIN, window functions, CTEs, bulk operations, index optimization, transactions, JSON handling, temporary tables, proper NULL handling, and recursive queries—to help developers write more efficient, maintainable, and powerful database code.
This guide walks you through using JetBrains DataGrip on AlmaLinux to connect to a PostgreSQL server, then demonstrates step‑by‑step how to insert, modify, and delete records via the graphical interface and optional SQL console, highlighting key settings and safety tips.
The article explains MySQL’s table lookup (回表) and index condition pushdown (索引下推), illustrating their mechanisms with sample tables, SQL statements, execution plans, performance drawbacks, and practical techniques such as covering indexes to reduce I/O and improve query efficiency.
This guide explains how to integrate Alibaba Druid as a Java database connection pool, add Maven dependencies, configure core properties, enable SQL and slow‑SQL monitoring, set up WebStatFilter and Spring AOP monitoring, remove the built‑in advertisement, and retrieve monitoring data via DruidStatManagerFacade.
This guide presents more than twenty practical MySQL 8.0 optimization recommendations—including hardware tuning, InnoDB configuration, index design, query rewriting, security hardening, monitoring, backup strategies, and benchmarking—to help engineers dramatically improve database throughput, latency, and stability.
This article examines eight typical situations that cause MySQL indexes to become ineffective—such as violating the leftmost‑prefix rule, leading‑wildcard LIKE patterns, OR conditions, functions, calculations, implicit type conversions, large result sets, and mismatched ORDER BY directions—providing SQL examples, execution‑plan screenshots, and practical explanations.
The article examines a real-world migration of a major Southeast Asian tech group from Google BigQuery to Alibaba Cloud MaxCompute, highlighting the challenges of complex data types, the columnar storage and execution engine redesigns, and the resulting performance gains that often surpass BigQuery.
This article presents ten practical Hive SQL advanced scenarios—including session segmentation, funnel conversion, median calculation, array explosion, hierarchical recursion, deduplication, small‑file merging, conditional aggregation, approximate statistics, and data‑quality checks—each with full SQL code, key technical points, and optimization tips for Hive 3.x.
This article explains the concepts of table lookups (回表) and index condition pushdown (索引下推) in MySQL, demonstrates how they work with example tables and queries, discusses their performance impact, and provides practical techniques such as covering indexes and selective column queries to minimize costly table lookups.
This tutorial shows how to use PyCharm’s built‑in database tool to connect to a MySQL server, configure connection details, download drivers, display all schemas, run SQL queries, and remove connections, with clear step‑by‑step instructions and screenshots.
This step‑by‑step tutorial shows how to deploy StarRocks and MinIO in a decoupled storage‑compute architecture using Docker Compose and Kubernetes, configure local caching, create storage volumes, load public datasets, and run SQL queries to explore the combined data.
This article explains why using variable assignment expressions inside ORDER BY clauses can trigger severe OCP alarms, analyzes the underlying source code in OceanBase, demonstrates unpredictable query results with examples, and offers safer alternative patterns.
This article presents a comprehensive set of practical SQL optimization techniques—including proper indexing, selective column retrieval, efficient joins, query planning, and database‑specific features—to dramatically improve query speed, reduce resource consumption, and enhance overall application performance.
