The article explains that using IN and NOT IN in SQL can cause severe performance degradation and produce incorrect results, especially with large tables, missing indexes, or NULL values, and recommends using EXISTS, NOT EXISTS, or JOINs as safer alternatives.
The article presents a cloud‑native distributed Data Warebase that unifies relational, NoSQL, search and analytical capabilities into a single horizontally‑scalable system, reducing data‑access barriers and improving developer productivity while addressing consistency, latency, cost and operational complexity.
This guide introduces MyCLI, a Python‑driven interactive command‑line client for databases, covering its key features, installation methods, configuration steps, connection commands, query execution, syntax‑highlighting options, and practical usage examples.
This article analyzes a slow Join query on OceanBase 3.2.3.3, examines execution plans and table statistics, proposes two rewrite methods—adding a filter condition and using a hash‑join hint—and demonstrates how these changes reduce execution time from over 500 seconds to just a few seconds.
This article explains how MySQL InnoDB implements full‑text search, covering the underlying inverted index structures, how to create and drop full‑text indexes, the MATCH…AGAINST syntax, and detailed examples of natural language, boolean, and query‑expansion search modes with practical SQL demos.
This article recounts how a production database deadlock was uncovered during testing, explains the use of the EasyBI monitoring tool to collect and visualize error and claim statistics, and shares the step‑by‑step configuration, analysis, and lessons learned for preventing similar issues in future systems.
This article explains why new data systems are needed, introduces Apache Arrow and its columnar in‑memory format, describes read‑time modeling and dynamic schema handling, and shows how Arrow can be used to build a complete data processing pipeline with indexing, SQL planning, and zero‑copy data exchange.
This article explains MySQL 8.3’s new ability to tag GTIDs, describes the original and extended GTID formats, demonstrates how to enable and use tagged GTIDs with mysql-shell, and shows how tagged and non-tagged transactions appear in replication logs.
Chat2DB is a free open‑source multi‑database client that adds AI‑driven natural‑language‑to‑SQL conversion, optimization suggestions, and team collaboration features, offering Windows, macOS, Linux, and web deployments along with Docker installation and detailed usage examples.
An in‑depth comparison of MySQL and PostgreSQL covers their histories, architectural differences, benchmark results showing PostgreSQL’s superior SELECT, INSERT, and UPDATE throughput, and guidance on selecting the appropriate database based on application complexity, scale, and performance requirements.
After a weekend support ticket led to a reckless UPDATE that erased all orders in a production PostgreSQL database, the author details the rapid recovery steps, analyzes the human errors behind the disaster, draws lessons from Chernobyl, and outlines concrete post‑mortem improvements to prevent future data loss.
This article explains why using OFFSET and LIMIT for pagination becomes a performance bottleneck on massive tables, illustrates the cost of full‑table scans, and introduces a cursor‑based pagination technique that leverages primary‑key ordering for efficient data retrieval.
This article explores practical techniques for mitigating data skew in massive SQL join operations, detailing MapJoin, handling special/empty values, hotspot dispersion, SkewJoin, and the novel DistMapJoin approach, complete with code snippets and performance results from Alibaba's payment data pipeline.
This guide explains MySQL's native JSON data type, its storage format, advantages over text columns, and provides step‑by‑step examples for creating tables, inserting JSON, querying with path operators, using built‑in functions, and handling conditional searches.
This guide explains step‑by‑step how to manually set up fine‑grained disaster recovery for GaussDB(DWS) in a cloud‑based dual‑cluster environment, covering preparation, configuration files, backup/restore operations, verification, and removal to achieve reliable, cost‑effective data protection.
This article compares MySQL and PostgreSQL, covering their histories, architecture differences, benchmark results, suitable application scenarios, and a concise summary of PostgreSQL's advantages and disadvantages relative to MySQL.
This guide explains how to implement a searchable user‑story interface using PostgreSQL tables with btree and gin indexes, Java record models, Spring Boot data access, and REST controllers to support range and full‑text queries efficiently.
This article explains how to use PHP's mysqli_query function to perform MySQL operations such as SELECT, INSERT, UPDATE, and DELETE, providing a step‑by‑step example that creates a connection, runs a SELECT query, processes results, and handles errors and cleanup.
This article explains why setting NULL as a column's default in MySQL can lead to index inefficiencies, unpredictable query results, and performance issues, and demonstrates proper handling of NULL with examples, operators, functions, and indexing considerations.
This article explains why using OFFSET and LIMIT for pagination on large tables leads to costly full‑table scans and poor performance, and introduces keyset (cursor) pagination as a faster, index‑driven alternative while discussing its requirements and trade‑offs.
This article explains why defining columns with a default NULL in MySQL is misleading, demonstrates how NULL interacts with indexes, operators, aggregate functions, and sorting, and provides practical examples and recommendations to avoid performance and correctness issues.
This article explains MyBatis caching mechanisms, detailing the scope, implementation, configuration, and differences between first-level (SqlSession) and second-level (mapper) caches, and how they improve query performance while outlining their default behaviors and usage considerations.
This article explains why hash joins usually outperform nested‑loop joins, how to force hash joins in SQL, the data‑type restrictions that prevent hash joins, and practical tips for optimizing join performance in TD and Oracle compatibility modes.
This article reviews SQLite’s 2018 SQL enhancements—including Boolean literals, window functions, the FILTER clause, upsert syntax, and column‑renaming—explaining each feature, showing concrete code examples, comparing behavior with other databases, and noting remaining limitations.
This article explains how MySQL InnoDB implements full‑text search using inverted indexes, covers the creation and usage of FULLTEXT indexes, demonstrates various MATCH…AGAINST query modes such as natural language, boolean, and query expansion, and shows how to manage and delete full‑text indexes with practical SQL examples.
This article explains why a SQL query tool is essential for on‑premise systems, describes the challenges of remote access and data safety, and provides a step‑by‑step implementation covering frontend encryption, backend whitelist checks, decryption, and query execution with code examples.
This guide shows how to use Zabbix to monitor database SQL results, binlog health, master‑slave replication, and how to clean up old Zabbix history with practical Bash scripts for MySQL/GreatDB and ClickHouse.
iQIYI's Magic Mirror platform, evolving from 1.0 to 3.0, addresses the growing data analysis demands of the internet industry by empowering self‑service analytics, introducing multi‑stage architectures, advanced computation engines, customizable SQL, and visual dashboards, thereby improving efficiency, scalability, and data security for business users.
This tutorial explains how to use PyCharm’s integrated database tool to create, configure, test, and manage a MySQL connection, including driver installation, schema selection, SQL execution, and removal of connections, with detailed visual instructions.
An ultra‑large hedge fund with over $10 billion AUM replaced ksqlDB and Flink with RisingWave, leveraging its PostgreSQL‑compatible streaming SQL to achieve sub‑10 ms latency, lower learning and operational costs, rich connectors, advanced operators, and comprehensive observability for real‑time trade data processing.
This article experimentally compares the efficiency of using IN clauses versus JOIN operations in MySQL by testing small, medium, and large datasets, measuring query costs, execution times, and highlighting the scalability limits of IN statements.
This guide explains MySQL’s most useful date and time functions—including CURDATE(), NOW(), DATE_FORMAT(), DATEDIFF(), DATE_ADD(), DATE_SUB(), and DATE()—with clear syntax examples and sample results, helping developers manipulate temporal data efficiently in their databases.
This guide walks you through the most commonly used MySQL date functions—CURDATE, NOW, DATE_FORMAT, DATEDIFF, DATE_ADD, DATE_SUB, and DATE—explaining their purpose, syntax, and example results so you can handle date and time data efficiently in your databases.
This article explains how to efficiently insert massive amounts of data (over 100,000 rows) into a MySQL table by comparing plain JDBC single‑row inserts, JDBC batch inserts, MyBatis/MyBatis‑Plus foreach dynamic SQL, and the built‑in MyBatis‑Plus saveBatch method, while highlighting configuration tips and performance results.
Easy-Query is a Java ORM framework offering strong-typed, database-agnostic CRUD operations, complex SQL generation, multi-table joins, group awareness, and extensive function support, demonstrated through numerous code examples covering queries, joins, subqueries, pagination, and advanced features like enum handling and anonymous type alternatives.
This article explains how to incorporate the SQLE SQL auditing tool into a DevOps workflow using GitLab, GitLab‑Runner, and Docker, detailing environment setup, CI/CD configuration, and automated scanning to ensure compliance and reduce production risks.
This article explains the complete lifecycle of a MySQL SELECT statement, covering the server and storage‑engine layers, the roles of the connector, query cache, parser, optimizer and executor, and provides concrete command‑line and SQL examples.
This article explains how InnoDB determines transaction types based on the first SQL statement, describes the internal state changes when a transaction starts, and details the identification, ID allocation, and handling of read, read‑only, read‑write, and internal transactions in MySQL 8.0.32.
This article explains why database indexes often become ineffective—covering fuzzy searches, type mismatches, function use, NULL values, arithmetic operations, leftmost column rules, and full‑scan decisions—and provides practical guidelines for designing useful indexes.
An in‑depth tutorial walks through the five core SQL language families—DDL, DML, DQL, DCL, and TCL—explaining their purposes, key commands, and practical examples, while highlighting differences and usage tips for database schema definition, data manipulation, querying, permission control, and transaction management.
This guide demonstrates how Elasticsearch 8.11's new ES|QL query language matches the traditional DSL syntax, covering basic searches, sorting, wildcard matching, aggregations, REST API nuances, and practical examples that show equivalent DSL and ES|QL statements with visual results.
This article walks through the challenges of environment‑specific data isolation in a Java project, explains why adding an env column to every table is impractical, and presents a clean solution using a MyBatis interceptor that rewrites SQL and custom annotations to control filtering, ensuring safe, maintainable code across pre‑release, gray, and production environments.
This article explains essential SQL concepts and common pitfalls—such as overusing SELECT *, choosing appropriate data types, respecting column order, handling NULL differences, leveraging database processing, using connection pools, and applying batch operations—to help developers write efficient, cost‑effective queries and advance their careers.
This guide explains MySQL stored procedure flow‑control constructs—including IF, CASE, LOOP, WHILE, REPEAT, LEAVE and ITERATE—detailing their syntax, usage patterns, and practical examples such as salary adjustments and conditional logic, helping developers implement complex database operations efficiently.
This article explains MySQL event scheduler fundamentals, including enabling/disabling the scheduler, creating, altering, and dropping events, scheduling syntax, and practical examples such as generating real-time sales data and periodic statistics, providing code snippets and best‑practice guidance for database automation.
This article demonstrates how MySQL silently discards trailing spaces in VARCHAR columns during queries, shows example tables and queries where spaces are ignored, and explains how to use the BINARY keyword to enforce strict matching of trailing spaces.
This article provides a detailed walkthrough of using the SQLE platform to create users, configure data sources, submit SQL tickets, perform reviews, and execute releases, illustrating each step with screenshots and practical tips for effective database change management.
This article examines the many syntactic forms of MySQL's BEGIN statement, explains which variations are valid or cause syntax errors, and details how BEGIN commits any existing transaction and prepares a new one without immediately starting it, all based on MySQL 8.0.32 InnoDB source code.
This tutorial demonstrates how to set up a PHP script that connects to a MySQL database, executes a SELECT query, stores the result rows in an array, and displays the data in an HTML table using loops such as while and foreach.
The article explains how the data team adopted the open‑source machine‑learning platform MindsDB to simplify data integration, enable SQL‑based model training and inference, manage model versions, and dramatically shorten recommendation system development cycles, achieving up to 30% efficiency gains.
This guide walks you through a 5‑minute challenge that shows how to enable Hologres real‑time lakehouse capabilities, configure OSS, DLF, and Hologres services, create external and internal tables, run TPCH Q11 queries, and submit results for prizes.
This article walks through a real‑world case of a slow MySQL query that took 8 seconds, explains how the original use of EXISTS and NOT EXISTS caused performance bottlenecks, and details two successive rewrites—first using JOIN + GROUP BY, then replacing NOT EXISTS with NOT IN—bringing execution time down to 0.7 seconds while highlighting key indexing and testing lessons.
This newsletter announces the SQLE 3.2401.0-pre1 release with automatic data masking, shares recent community development work, outlines upcoming plans, and curates several technical articles covering MySQL EXPLAIN enhancements, transaction fundamentals, hardware considerations for database stability, consistent backup techniques, and efficient JDBC connection testing.
This article explains MySQL event scheduler fundamentals, its advantages, common commands for managing events, detailed syntax for creating events, and provides practical examples such as generating real‑time sales data and scheduled statistics, helping developers automate repetitive database tasks.
Discover how Manticore Search, an open‑source C++‑based high‑performance search engine, claims to outperform Elasticsearch by up to 15‑times, offering cost‑effective, multi‑threaded full‑text search, SQL compatibility, Docker deployment, and extensive language client support for diverse data workloads.
This guide explains MySQL stored procedures, covering their definition, benefits, parameter types, creation syntax, delimiter handling, multiple practical examples, calling conventions, debugging tips, and hands‑on exercises, enabling developers to efficiently write, test, and manage reusable SQL routines.
This article provides a comprehensive, step‑by‑step analysis of MyBatis, covering its core features, three‑layer architecture, configuration file parsing, dynamic proxy generation, and the detailed process of executing SQL statements within the framework.
This article details practical methods for testing BI reports built on Hive and StarRocks, covering the report creation workflow, testing characteristics, SQL writing techniques, impact analysis, data warehouse simplification, and the application of data quality tools to ensure accurate and efficient reporting.
To achieve fast bulk inserts in MyBatis, open a session with ExecutorType.BATCH and insert records individually (letting JDBC batch them), limit foreach‑generated statements to about 20‑50 rows, or use MyBatis dynamic‑SQL or plain JDBC batch, avoiding massive SQL parsing and CPU overload.
This article explains why using NULL columns in MySQL can lead to extra storage, index complications, query errors, and performance degradation, and provides concrete reasons and code examples showing how to replace NULL with meaningful defaults for more reliable database design.
This article explores the challenges of traditional data‑warehouse ETL, compares push and pull models, and presents an LLM‑driven architecture that generates both on‑demand SQL queries and streaming ETL code with automatic error‑feedback loops, dramatically improving cost, accuracy, and maintainability.
This article explains three MySQL data‑migration techniques—logical migration using mysqldump, file‑based migration with SELECT … INTO OUTFILE and LOAD DATA INFILE, and physical migration by copying InnoDB files—detailing commands, performance considerations, and practical steps for each method.
The article reviews Apache Flink's rapid development over the past decade, highlighting its 2023 community growth, SIGMOD award, major releases, streaming SQL enhancements, incremental checkpointing, batch maturity, cloud‑native scaling, and integration with the emerging Lakehouse architecture.
This article shows how flexible, divergent thinking combined with basic MaxCompute (ODPS) SQL syntax can solve complex data problems such as generating sequences, splitting intervals, performing permutations and combinations, and analyzing continuous activity, providing step‑by‑step examples, SQL code snippets, and practical results.
Manticore Search is a modern, open‑source C++‑based full‑text search engine that claims up to 15‑times faster performance than Elasticsearch in certain scenarios, offering SQL compatibility, multi‑threaded architecture, columnar storage, Docker deployment, and extensive client language support.
This article explains the nature of NULL in MySQL, demonstrates why comparisons with = or != fail, shows the correct IS NULL/IS NOT NULL syntax, and discusses how NULL behaves with DISTINCT, GROUP BY, ORDER BY, and aggregate functions.
This guide introduces Spring Boot 3.2's new JdbcClient, explains how to add the dependency, inject it, and perform fluent, chainable CRUD operations—including queries by ID, custom conditions, named parameters, map parameters, row mapping, and inserts—while highlighting its advantages over traditional ORM tools.
This article explains the concept, principles, performance benefits, and trade‑offs of MySQL indexes, illustrating with a 200‑million‑row example that shows how indexing reduces query time from days to under a second compared to full table scans.
This article explains the most common MySQL table join types—including INNER, LEFT, RIGHT, FULL OUTER, and exclusive joins—by creating sample order and customer tables, inserting data, visualizing each join with diagrams, and providing the exact SQL statements and query results.
This article compares the performance of JOIN and IN in MySQL queries, explains their basic syntax, provides example code, discusses scenarios where each is preferable, and offers additional optimization tips such as indexing, schema design, and engine selection.
This article presents a step‑by‑step investigation of a SELECT statement that became 1000× slower in OceanBase 3.2.3 BP8, explains why the optimizer chose an inefficient I5 index, describes the plan‑expiration logic, and provides reproducible scripts and practical recommendations for fixing the problem.
The article explains that MySQL's sysdate() returns the exact execution time of a statement, making it non‑deterministic and preventing index usage, whereas now() returns a constant timestamp at statement start, allowing the optimizer to treat it as a constant and use indexes effectively.
This article provides a step‑by‑step guide for installing the open‑source SQLE 3.0 SQL quality management platform using both RPM packages and Docker containers, covering environment preparation, configuration file adjustments, service startup, common issues, and troubleshooting tips.
This article examines the challenges of LLM‑based Text‑to‑SQL such as hallucinations, data‑security risks, and user input errors, and presents prompt‑engineering strategies, fine‑tuning comparisons, prompt types, code examples, and experimental results to improve reliability and cost‑effectiveness.
The article explains why analytical databases struggle with real‑time data changes due to columnar storage, complex indexes and distributed processing, and then details StarRocks' primary‑key model, adaptive update mode, bitmap indexes, row/column partial updates, and practical SQL upsert techniques to achieve low‑latency updates without sacrificing query performance.
This article walks through the complete process of adding Dataway to a Spring Boot application, covering Maven dependencies, configuration properties, database schema creation, data source setup, Hasor module integration, enabling Hasor in Spring, and finally creating and testing API endpoints using both SQL and DataQL, all illustrated with concrete code snippets and log output.
This tutorial demonstrates a complete workflow for building credit‑card user profiles by first extracting and transforming the KDD99 dataset with SQL, then applying Python libraries such as pandas, matplotlib, and seaborn to perform descriptive statistics and produce visualizations of demographic, transaction, and financial characteristics.
This article surveys emerging database platforms such as PlanetScale, YugaByteDB, Dolt, CockroachDB, Cloudflare D1, Xata, SurrealDB, and FaunaDB, highlighting their architectures, features, pricing, and trade‑offs for developers seeking modern, distributed, or serverless solutions.
This article explains MySQL's explicit and implicit locking mechanisms, illustrates how transaction isolation levels affect lock scope, provides practical SQL examples for various scenarios, and offers guidelines on when to apply pessimistic locks versus optimistic approaches to maintain database performance.
This article provides a detailed overview of Druid, a high‑performance Java database connection pool, covering its core concepts, Maven dependencies, extensive configuration options, monitoring filters, SQL slow‑query logging, and methods to customize or remove the built‑in advertisement page.
This article analyzes a slow MySQL query that scans millions of rows to find students scoring 100 in Chinese, then demonstrates how adding appropriate single‑column and composite indexes, rewriting the query as a join, and using covering indexes can reduce execution time from hours to milliseconds.
The article walks through a real‑world case of a slow MySQL query that took 8 seconds, explains why simple indexing wasn't enough, and demonstrates two successive rewrites using JOIN‑GROUP BY and NOT IN that reduced execution time to 0.7 seconds while detailing the EXPLAIN analysis.
This article introduces SQL as the standard language for relational databases, explains how data is organized into tables with primary and foreign keys, and walks through essential keywords like SELECT, WHERE, and JOIN to query and relate data effectively.
This article recounts three production‑level pagination bugs encountered in a payment system, explains why they occurred—ranging from naive batch processing to MySQL's nondeterministic LIMIT behavior—and shows how proper pagination, ThreadLocal cleanup, and deterministic ordering resolve the issues.
This article presents NetEase Data's end‑to‑end experience in building a metrics middle platform, covering product evolution, the motivations for a unified metrics layer, core technologies such as a logical semantic model, a custom metric query language, engine‑agnostic execution, and future roadmap plans.
Effective index design, including single‑table, foreign‑key, composite, and covering indexes, along with optimizer hints, can dramatically improve the performance of multi‑table queries in PHP‑MySQL applications, and the article provides practical examples and SQL statements for each technique.
This guide explains Druid's core concepts, how to add Maven dependencies, configure properties, enable monitoring filters, customize the admin servlet, remove built‑in ads, and retrieve runtime statistics, providing a complete tutorial for integrating Druid with Spring Boot applications.
The article details a step‑by‑step investigation and optimization of a Java backend service that initially delivered only 50 requests per second under load, covering profiling, slow‑SQL fixes, thread‑pool tuning, JVM memory adjustments, and Spring bean creation overhead to approach the target 500 req/s.
This guide presents a comprehensive set of SQL statements for the idc_evaluating database, illustrating how to retrieve rows by province or IP, count and delete active IPs, describe table structures, perform conditional aggregations, and execute inner‑join queries with calculated fields.
In this article the author walks through a real‑world slow‑query case, using EXPLAIN, rewriting EXISTS clauses with JOIN + GROUP BY and swapping NOT EXISTS for NOT IN, ultimately reducing execution time from 8 seconds to 0.7 seconds while sharing key indexing insights.
This article explains how to build a Text‑to‑SQL application using LangChain, OpenAI LLMs, and SQLDatabaseChain, covering the fundamentals of Text2SQL, LangChain components, code examples, and a practical SQLite case that transforms natural‑language questions into executable SQL queries.
This article experimentally compares the performance of JOIN and IN approaches for retrieving order and user data in MySQL, showing that JOIN is faster on small datasets while IN may become costly with large data volumes, and discusses code implementations and practical conclusions.
This article describes how a payment reconciliation system progressed from a simple MySQL‑based solution through a Hive‑based big‑data approach to a high‑performance StarRocks architecture, detailing the integration of Flink, DataX, and SQL adaptations that dramatically improved query speed, cost, and operational efficiency.
A ToB system that seemed able to handle 500 requests per second stalled at 50 req/s due to hidden lock contention in prototype‑scoped Spring beans, slow SQL updates, excessive logging, and thread‑pool misconfiguration, prompting a step‑by‑step performance investigation and multiple optimizations.
Manticore Search, a C++‑based open‑source search engine derived from Sphinx, offers superior speed, lower cost, and rich features compared with Elasticsearch, and can be quickly deployed via Docker with SQL‑compatible queries and extensive client support.
This article examines eight frequent MySQL performance problems—including misuse of LIMIT, implicit type conversion, sub‑query updates, mixed ordering, EXISTS, condition push‑down, early limiting, and intermediate result push‑down—and provides concrete rewrite techniques and example code that dramatically reduce execution time.
SQL database queries are essential skills for data analysts, enabling them to extract, transform, and analyze data from databases through various operations like filtering, sorting, aggregation, and joining.
This study analyzes JD e‑commerce operational data from February to April 2018, employing Python and SQL to compute key metrics such as PV, UV, conversion rates, attrition, purchase frequency, time‑based behavior, funnel analysis, retention, product sales, and RFM segmentation, and provides actionable recommendations for improving user engagement and sales performance.
This article explains why a DELETE statement with a table alias fails in MySQL 5.7, shows the correct syntax without alias, compares the DELETE syntax of MySQL 5.7 and 8.0, and concludes that alias support in single‑table DELETE was added starting with version 8.0.16.
This guide presents ten practical MySQL performance optimization methods—including index creation, query rewriting, limiting result sets, avoiding SELECT *, reducing joins, using proper data types, caching with Redis, and server configuration tweaks—each illustrated with concrete SQL code examples to help you speed up your database operations.
