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.
This guide explains how to use Python's cursor.execute with positional and named placeholders, demonstrates safe practices with SQLite, psycopg2, and SQLAlchemy, and shows how to generate log‑friendly SQL strings while preventing injection attacks.
Explore the comprehensive structure of MySQL, from its layered architecture and storage engines to detailed SQL language components, while learning essential DDL, DML, DQL, and DCL commands, performance tuning techniques, security best practices, and advanced features such as window functions, CTEs, and role-based access control.
The article examines the June 2025 TIOBE ranking where SQL fell to its lowest position, recounts its historical highs and removal from the list, highlights everyday reliance on SQL, and analyzes the growing prominence of NoSQL and other programming languages in the era of AI and big data.
This article walks through switching a SpringBoot + MybatisPlus project from MySQL to PostgreSQL, detailing driver addition, JDBC changes, numerous SQL syntax pitfalls, and provides helper scripts for batch column type conversion and default timestamp settings.
This guide explains how to use PHP’s mysqli_query function to connect to MySQL, run SELECT queries, handle results, and perform INSERT, UPDATE, or DELETE operations, complete with a full code example and best‑practice notes.
This article systematically compares six MyBatis batch‑update approaches—including simple for‑loop, foreach XML, CASE WHEN, ON DUPLICATE KEY UPDATE, REPLACE INTO, and MyBatis‑Plus—by presenting concrete code, execution‑time measurements on 10 000 rows, and analysis of I/O, SQL parsing, scalability, and database compatibility, ultimately recommending the most performant and maintainable method for different scenarios.
This article explains MySQL's InnoDB full‑text search, covering the theory of inverted indexes, how to create and use full‑text indexes with various MATCH…AGAINST modes, Boolean operators, query expansion, relevance calculation, and index removal, illustrated with practical SQL examples and diagrams.
This article walks through a real‑world case where a large platform suffered a massive traffic‑based attack, showing how Alibaba Cloud Log Service (SLS) SQL can extract high‑frequency IPs, join them with user identifiers, perform geographic analysis, and automatically update AWS and Alibaba WAF rules to block malicious sources.
This article explains why the SQL keywords IN and NOT IN often lead to poor performance and incorrect results—especially with large tables or NULL values—and shows how using EXISTS, NOT EXISTS or JOIN can provide faster, more reliable queries.
This article explains why using MyBatis foreach for bulk inserts can cause severe performance degradation, analyzes the underlying parsing and executor issues, and provides practical solutions such as using ExecutorType.BATCH and limiting batch sizes to achieve fast, reliable batch insertion.
This article explains why MySQL 8.0 replication to a 5.7 slave fails because of the new utf8mb4_0900_ai_ci collation, analyzes the root causes, and provides step‑by‑step configuration and SQL‑based solutions to ensure a smooth upgrade without data loss.
This article walks through a real‑world case where a large platform suffered a massive traffic‑based attack, showing how to extract high‑frequency IPs, join logs by trace_id, perform geographic analysis, and automate blacklist updates across Alibaba Cloud and AWS using SLS SQL and Python SDKs.
This article presents eight practical, proven techniques—such as prepared statements, proper indexing, selective column retrieval, pagination, efficient joins, query caching, batch operations, and EXPLAIN analysis—to dramatically improve the performance of database queries in PHP web applications, reducing load times and server strain.
This article explains the fundamentals of database indexes, covering their purpose, underlying data structures such as B‑Tree, B+‑Tree, and hash tables, the advantages and drawbacks, different index categories in MySQL, and practical SQL examples for primary, secondary, and unique indexes.
This article presents ten practical rules for designing database indexes—covering business‑scenario analysis, the left‑most prefix principle, avoiding over‑indexing, using covering indexes, data‑type choices, function traps, prefix indexes, NULL handling, maintenance scripts, and monitoring—each illustrated with SQL examples, diagrams, and real‑world performance gains.
This article explains Oracle's step‑by‑step processing of a SQL statement—including the client submission, parsing checks, optimizer decision‑making, row‑source generation, and final execution—while illustrating hard and soft parses, optimizer components, and practical query‑plan queries.
This article walks through adding a creator‑province dropdown filter to a to‑do list page, updating both front‑end components and back‑end APIs, handling status count changes, implementing reset logic, and debugging a pagination bug caused by incorrect start‑row calculations.
This article explains what non‑clustered indexes are, how they are built on B+‑tree structures, their main characteristics, and the scenarios where they improve query performance in relational databases, including fast lookups, join optimization, sorting, and handling frequent data modifications.
This tutorial walks you through preparing your environment, configuring DataGrip, and executing SQL scripts to connect and manage both MySQL and PostgreSQL databases, complete with hardware requirements, connection parameters, sample code, troubleshooting tips, and performance optimization advice.
This tutorial walks through creating a natural‑language‑to‑SQL chatbot using Spring AI, configuring a MySQL school database with Flyway, defining system prompts for a DeepSeek LLM, implementing service beans and a REST API, and interacting with the bot via curl commands.
The article explains the drawbacks of traditional random UUIDs as primary keys, introduces the time‑ordered design of UUIDv7, compares it with earlier versions, and provides practical Java code and SQL examples for generating and using UUIDv7 in databases.
This tutorial demonstrates how to use variables and loop constructs in MySQL and PostgreSQL stored procedures to iterate over a users table, filter records by age, and simulate sending a system welcome message, while comparing syntax differences between the two databases.
Learn essential MySQL commands—from connecting and managing databases, tables, and users to performing CRUD operations, advanced queries, indexing, and backup/restore—along with practical code examples and optimization tips, enabling you to efficiently handle relational data in real‑world applications.
The latest TIOBE index shows SQL falling to 12th place as NoSQL gains traction, driven by AI’s demand for unstructured data, while languages like Python surge, and major vendors such as IBM acquire NoSQL specialists to accelerate large‑scale AI production.
This tutorial explains why stored procedure parameters are essential, details the differences between IN, OUT, and INOUT parameters, and provides step‑by‑step MySQL examples—including environment setup, code snippets, execution results, common FAQs, and an advanced age‑checking exercise—to help developers write flexible, reusable procedures.
This article explains how to implement a lightweight MyBatis interceptor that annotates SQL statements with their mapper ID and execution stack, enabling developers to quickly locate performance bottlenecks during high‑traffic events, and also shows an AspectJ alternative for non‑MyBatis projects.
Learn how to create, execute, and troubleshoot a simple MySQL stored procedure using MySQL 8.0, covering delimiter changes, basic SQL syntax, result verification, common beginner mistakes, and extending the concept with parameterized examples.
This article presents SCALE, a community‑driven, open‑source benchmark that expands beyond simple Text‑to‑SQL accuracy to evaluate large language models on performance, dialect conversion, and deep SQL understanding, offering developers, researchers, and CTOs a realistic measure of AI‑assisted database tasks.
The article examines SQL's recent drop to 12th place in the TIOBE June 2025 ranking, recounts its historical highs and removal from the list, highlights its pervasive use in everyday systems, and discusses how the rise of AI and NoSQL databases may reshape its future role.
An interview-style SQL challenge asks you to identify records where a professor teaches a course belonging to a different college than their own, providing table definitions, sample data, and the expected result, while inviting readers to devise the correct query.
This article explains MySQL's four transaction isolation levels—READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE—through step‑by‑step examples, demonstrating their effects on dirty reads, non‑repeatable reads, phantom reads, and performance considerations, helping developers choose the appropriate level.
This article introduces a lightweight SQL‑coloring technique for MyBatis that annotates each executed query with its mapper ID and call‑stack, enabling developers to pinpoint performance bottlenecks instantly without hunting through multiple code paths, and provides both MyBatis interceptor and AspectJ weaving implementations.
This article explains how MySQL's InnoDB engine implements full‑text search using inverted indexes, shows how to create and use full‑text indexes with various query modes, demonstrates Boolean operators and query expansion, and covers index removal techniques.
This article explains how to use standard SQL on PolarDB PostgreSQL to directly invoke multimodal AI services for image feature extraction and vectorization, eliminating data migration and complex toolchains while providing low‑threshold integration, flexible scenario adaptation, full‑link security, and serverless, pay‑as‑you‑go deployment.
This article presents a series of MySQL interview questions and answers that illustrate typical situations where indexes become ineffective—such as leading wildcards in LIKE, NOT IN, NOT EXISTS, functions, inequality operators, OR conditions, mismatched ORDER BY, large result sets, and explains how to analyze execution plans and use FORCE INDEX to improve performance.
This article explains how to improve Paimon's query performance by optimizing table schemas, storage settings, query parameters, and index designs, covering table mode choices, partitioning, file formats, parallelism, batch reads, and various index types such as Bloom filters and clustering indexes.
This article explains how MySQL InnoDB transforms a simple INSERT statement into a complex on‑disk record by detailing the COMPACT row format, record header, variable‑length field list, NULL bitmap, and overflow handling, helping developers optimize storage and performance.
This guide outlines a step‑by‑step method to diagnose and release MySQL table locks by checking open tables, inspecting running processes, querying InnoDB transaction and lock tables, and generating KILL statements to terminate blocking sessions.
This article explains the evolution of data storage, the fundamentals of computer storage devices, and how database indexes—especially clustered and non‑clustered indexes—leverage binary search to dramatically speed up query performance while outlining their drawbacks and common SQL optimization techniques.
This article provides a comprehensive walkthrough of JDBC for MySQL, covering driver loading, connection creation, statement and prepared statement objects, SQL execution with examples, batch processing, and performance considerations for test engineers using Java.
This comprehensive guide explains the Text2SQL concept, showcases real‑world scenarios, compares three implementation architectures—including a simple prompt‑based method, a LangChain‑based pipeline, and an enterprise‑grade Vanna solution—while providing practical tips, security measures, and advanced enhancements for deploying robust natural‑language‑to‑SQL systems.
This article walks through using MySQL to pivot rows into columns, covering storage concepts, practical CASE‑WHEN aggregation techniques, and detailed SQL examples for sum, count, average and max calculations, while also discussing performance considerations for medium‑scale and large‑scale data workloads.
When designing a database for billions of phone numbers, using INT or BIGINT leads to overflow, data‑integrity loss, and inefficient queries, while VARCHAR(20) preserves formatting, supports international extensions, simplifies validation, and avoids common development pitfalls.
This guide walks you through locating MySQL deadlock logs, analyzing their contents to pinpoint transaction timing, order, and lock details, and identifying the root cause of the deadlock, including special locking scenarios that can trigger deadlocks.
The article explains why using a VARCHAR(20) column to store billions of phone numbers is safer and more flexible than INT or BIGINT, covering range limits, data integrity, query convenience, interview expectations, and common pitfalls such as insufficient field length, charset issues, missing indexes, and lack of encryption.
This comprehensive MySQL tutorial covers everything from core concepts and architecture to practical CRUD operations, advanced queries, indexing, transaction control, stored procedures, triggers, replication, performance tuning, backup strategies, and real-world use cases, providing a complete roadmap for developers and DBAs.
This guide walks you through locating the deadlock log, analyzing its contents to determine the time, order, and involved SQL statements, and pinpointing the root cause of MySQL InnoDB deadlocks, including special locking scenarios and how to interpret lock structures.
This article details the design and implementation of an AI‑driven Intelligent SQL Assistant that automates query parsing, index recommendation, execution‑plan visualization, and supports SQL generation, diagnosis, and explanation across multiple dialects, while outlining its layered architecture, core modules, code examples, and future enhancements.
StarRocks MCP Server provides a universal adapter that lets large language models like Claude, OpenAI, and Gemini execute SQL queries directly against StarRocks, simplifying data Q&A, intelligent analysis, and automated reporting by eliminating the need for bespoke plugins or complex prompt engineering.
This article explains the concept of SaaS multi‑tenant architecture, compares three data‑isolation designs, and demonstrates how to implement elegant tenant‑level data isolation in Java using MyBatis‑Plus’s tenant plugin, complete with configuration, handler code, SQL examples, and troubleshooting tips.
This tutorial explains how to build logistics tracking and delivery management features for an e‑commerce platform using PHP, covering database table creation, inserting, updating, and querying of tracking and delivery records with complete code examples.
This tutorial walks through adding Dataway to a Spring Boot project, covering Maven dependencies, configuration properties, required database tables, data‑source setup, Hasor module integration, enabling Hasor, launching the application, and creating and testing APIs using both SQL and DataQL without writing any service code.
The article outlines why deleting tens of millions of rows in one statement harms performance and reliability, then presents safe, efficient tactics—such as indexed batch deletes, partition drops, creating a new table, or using TRUNCATE—plus pre‑deletion planning, post‑delete validation, monitoring, and space reclamation.
This article presents ten practical SQL optimization techniques—including replacing DISTINCT with GROUP BY, using UNION ALL instead of OR, avoiding functions on indexed columns, leveraging EXISTS over IN, creating covering indexes, partitioning large tables, and more—each illustrated with before-and-after code examples to dramatically improve query speed and reduce server load.
This article details a real‑world case study of the activity middle‑platform system’s slow‑SQL problem, explaining the definition, causes, and harmful impacts of slow queries, then outlines step‑by‑step mitigation strategies—including data cleanup, partitioning, indexing, query refactoring, and scheduling—to dramatically reduce slow‑SQL occurrences from thousands to double‑digit counts.
