The article explains why using IN and NOT IN in SQL queries can lead to poor performance and incorrect results, especially with large tables or NULL values, and demonstrates how replacing them with EXISTS, NOT EXISTS, or appropriate JOINs yields faster, reliable outcomes.
This article compiles thirty practical SQL performance tips, covering index usage, avoiding full table scans, proper handling of NULLs, OR/IN clauses, functions, temporary tables, cursors, and transaction size, each illustrated with clear example queries and recommended rewrites.
This guide explains how InnoDB implements full‑text search with inverted indexes, shows how to create and drop full‑text indexes, demonstrates MATCH() AGAINST() syntax across natural language, boolean, and query‑expansion modes, and covers relevance scoring, stopwords, token size limits, and real‑world query examples.
The article provides an in‑depth technical analysis of DongSQL V1.1.0, detailing its RETURNING clause, Hint extensions, CCL concurrency control, Statement Outline, single‑point query bypass, thread‑pool redesign, and benchmark results that show performance gains up to 215% in e‑commerce workloads.
This article explains how implicit type conversion can silently invalidate database indexes, outlines the underlying rules and type‑precedence logic, provides real‑world MySQL examples, shows how to detect the issue with EXPLAIN, and offers concrete best‑practice solutions.
This guide explores the fundamentals of database indexing, answering ten common questions about index design, performance pitfalls, composite indexes, covering indexes, NULL handling, and maintenance, while providing practical SQL examples, B+‑tree concepts, and best‑practice recommendations for MySQL and PostgreSQL.
This article presents ten practical MySQL interview questions covering consecutive‑login detection, efficient pagination, top‑salary per department, customers without orders, cumulative sales, recursive cycle detection, pivoting rows to columns, duplicate removal, and user retention, each with complete SQL statements, underlying principles, and performance considerations.
Developers, data analysts, and ops engineers can eliminate the hassle of juggling multiple database clients by adopting DBeaver, an open‑source, cross‑platform tool that supports over 100 databases, offers powerful SQL editing, visual design, data migration, and backup features—all for free.
This article analyzes the five typical pitfalls when designing DWS aggregation tables—from chimney‑style schemas to performance blind spots—explains their consequences, and provides concrete, production‑ready recommendations, code examples, and design principles to create reusable, efficient data‑warehouse layers.
This article examines why MySQL queries can become sluggish by analyzing seven typical situations where indexes become ineffective, provides step‑by‑step testing procedures, performance comparisons, troubleshooting tips, and best‑practice recommendations to restore optimal query performance.
With MySQL 8.0 reaching end‑of‑life in April 2026, DBAs must migrate to the LTS 8.4 release, updating tools and replacing removed system variables, deprecated options, and old commands to avoid breakage in applications, monitoring, and replication setups.
This guide walks through constructing an AI‑powered multimodal data analysis platform on Hologres 4.0, covering PDF ingestion, vector and full‑text indexing, AI functions, stored procedures, and hybrid retrieval queries to enable enterprise‑level search and insight extraction.
This guide explores common pagination bottlenecks and presents practical solutions for single‑database and sharded environments, covering keyset pagination, bidirectional paging, join‑based paging, index design, middleware rewriting, approximate paging, and two‑phase query techniques.
The article explains why test engineers need solid SQL and Linux skills to verify data, pinpoint backend issues, automate test data handling, and efficiently manage test environments, then provides detailed command references and a hands‑on troubleshooting scenario.
This article explains why using IN and NOT IN in SQL queries often leads to poor performance and incorrect results, demonstrates common pitfalls with examples—including NULL handling—and shows safer alternatives such as EXISTS, NOT EXISTS, and JOINs.
This article explains why offset‑based pagination can cause duplicate or missing records when data changes, compares three practical solutions—including cursor pagination with timestamps and IDs, fixed‑window timestamp filtering, and Elasticsearch search_after—and provides concrete SQL and code examples along with their trade‑offs and suitable scenarios.
This guide walks you through the fundamentals of MySQL performance tuning—covering the optimization pyramid, how to read EXPLAIN plans, practical index design, safe online DDL for large tables, and interview‑ready case studies that turn SQL expertise into a powerful hiring advantage.
This article explains five streaming ETL patterns—filter, route, transform (extract, normalize, anonymize), aggregate, and trigger—showing how each can be implemented with SQL statements and illustrating their use cases with concrete code examples.
This article explains MySQL's supported character sets and collations, shows how to list them, demonstrates table‑level and column‑level definitions, clarifies naming conventions, and provides practical examples for verifying and altering collations to ensure correct string comparison and storage.
When UI checks can be misleading, this guide shows testers three practical ways—getting ready‑made queries from developers, using friendly graphical database clients, and mentally mapping UI actions to DB changes—to directly validate data in the database without deep SQL expertise.
Effective data modeling is crucial for system stability, and this guide walks you through core principles, twelve common table patterns—from batch and log tables to hierarchical and bitmap structures—detailing design rules, trade‑offs, usage scenarios, and practical examples to help you avoid costly redesigns.
This article presents seven practical DuckDB SQL patterns—querying files directly, treating partition folders as tables, deduplicating with QUALIFY, computing rolling metrics with window functions, pivot/unpivot, handling JSON arrays, and exporting results to Parquet—plus tips and a mini case study that show how to turn a notebook into a lightweight OLAP engine without leaving the Python environment.
October 2025’s SCALE report introduces Ant Bailing’s trillion‑parameter models Ling‑1T and Ring‑1T, evaluates them across three dimensions—SQL optimization, dialect conversion, and SQL understanding—reveals Ling‑1T’s strength in domestic database conversion and Ring‑1T’s balanced performance, and provides expert commentary on their implications for AI‑driven database solutions.
This article explains MySQL's four transaction isolation levels, the concurrency problems they address, how MVCC works, and provides practical guidance on selecting the appropriate level based on business requirements and performance trade‑offs.
This article explains why using IN and NOT IN in SQL queries often leads to poor performance and subtle bugs, especially with large tables and NULL values, and demonstrates safer alternatives such as EXISTS, NOT EXISTS, and JOIN with clear code examples.
This article explains the fundamentals of database indexing, covering what indexes are, their advantages and drawbacks, underlying structures such as hash tables, B‑tree and B+‑tree, different index types (clustered, non‑clustered, primary, secondary) in MySQL, and practical examples with code snippets.
The article outlines a comprehensive data‑warehouse quality assurance framework covering data lineage, layer‑level testing (timeliness, completeness, accuracy), application‑level checks, downgrade and backup strategies, and future plans for automated data shape reporting and static SQL analysis.
This comprehensive guide walks you through MySQL InnoDB buffer pool optimization—from assessing current settings and calculating optimal sizes for 32 GB to 256 GB servers, to configuring instances, enabling pre‑warming, tuning dirty‑page flushing, monitoring key metrics, and troubleshooting common issues—to achieve up to a 300 % throughput increase in production environments.
StarRocks 4.0 introduces FlatJSON, a columnar storage and execution engine that converts high‑frequency JSON fields into native columns, dramatically reducing I/O and CPU costs and enabling JSON queries to run with performance close to that of traditional columnar data.
This article explains how transforming data‑validation rules into configurable SQL statements eliminates lengthy development‑test‑release cycles, improves cost efficiency, and boosts performance through architecture features such as plug‑in event triggers, memory‑snapshot isolation, data‑fingerprint grouping, and an online SQL debugger.
This article presents a comprehensive set of SQL performance guidelines, covering index creation, avoiding costly operators, rewriting predicates, using proper joins, limiting temporary objects, and best practices for query design to prevent full table scans and improve overall database efficiency.
This article walks through nine powerful MyBatis dynamic SQL tags—including foreach, if, choose, selectKey, trim, and sql fragments—explaining their attributes, common pitfalls, and providing complete XML and Java examples to help developers write cleaner, error‑free queries and updates.
This article explains how to configure and customize Hibernate and Spring Data JPA logging in Spring Boot 3, covering SQL output, formatting, highlighting, comment inclusion, statistics, slow‑query detection, transaction logs, parameter binding, cache logs, and related performance diagnostics.
This article explains why database expenses balloon, then walks through ten practical techniques—including smart indexing, selective column queries, caching, partitioning, archiving, data‑type tuning, simplifying joins, regular maintenance, query monitoring, and right‑sized hardware—to cut costs up to 60% while preserving or even boosting query speed.
The article explains why the SQL IN and NOT IN operators often cause poor performance and incorrect results—especially with large tables or NULL values—and demonstrates safer alternatives such as EXISTS, NOT EXISTS, and JOIN with clear code examples.
This article provides a comprehensive analysis of the ADS (Application Data Service) layer in a data‑warehouse architecture, covering its core positioning, design goals, modeling strategies, dimension‑optimization techniques, API services, typical challenges, and practical best‑practice recommendations for high‑performance, flexible, and secure data delivery.
This guide explains what LangChain toolkits are, why they simplify building domain‑specific AI agents, lists common built‑in toolkits, and walks through the step‑by‑step process of instantiating a toolkit, retrieving its tools, and creating an OpenAI‑powered agent, illustrated with a SQL database example.
The article explains how Dimensional Query Language (DQL) replaces traditional multi‑table JOIN syntax with object‑dot notation and dimension‑alignment concepts, offering simpler, more business‑focused queries for BI scenarios, and demonstrates its implementation through concrete SQL examples and a real‑world engine integration.
This article explains why a tenant in OceanBase may experience frequent mini compactions, shows how to locate the responsible DML statements by querying merge info and tablet IDs, demonstrates the process with test tables, and provides conclusions and references for further investigation.
This article explains how using bound (parameterized) queries in SQL Server improves performance by enabling execution plan reuse, reducing compilation overhead and memory usage, while also preventing SQL injection, and discusses potential pitfalls like parameter sniffing and how to mitigate them with OPTION (RECOMPILE).
This comprehensive guide compares MySQL, PostgreSQL, and MongoDB across core features, transaction models, indexing, scalability, and real‑world use cases, offering a step‑by‑step selection method, detailed pros and cons, and a practical decision flowchart to help engineers pick the right database for their projects.
This article explains how the Ecotone framework lets PHP developers map classes to database tables, hide low‑level SQL behind repository interfaces, customize parameter conversion with converters and expression language, and efficiently query, update, and stream large result sets while keeping business logic clean.
This article provides a detailed overview of MySQL’s four‑layer architecture—including the client, server, storage engine, and system file layers—explaining each layer’s responsibilities, key modules, and the role of various built‑in and third‑party storage engines.
This article explains the Hibernate @Formula annotation, covering its purpose, basic and advanced usage with code examples, performance considerations, and best practices, while also announcing a Spring Boot 3 PDF collection of 181 practical cases with free ongoing updates.
This comprehensive guide walks you through downloading and installing the Dameng database, creating databases and users, using the client tools, mastering basic and advanced SQL syntax, comparing it with MySQL, Oracle, and SQL Server, and highlighting its performance, security, and compatibility advantages.
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.
