This article provides a detailed technical overview of Presto 2.0, the native C++ query engine built on the Velox library, covering its motivation, vectorized architecture, memory management, performance benchmarks from Meta and IBM, and deployment practices for large‑scale data warehouses.
This article presents a detailed experimental study of OceanBase's Arbitration Service in a 2F1A (two full‑function replicas plus one arbitration node) configuration, examining how the system behaves when one or both full‑function replicas fail, how log‑stream degradation and permanent offline mechanisms work, and how normal service is restored after node recovery.
Huolala’s data testing platform tackles massive data volume, complexity, and quality challenges by automating test case generation, execution, monitoring, and alerting across multiple storage systems, dramatically reducing testing cycles from five days to 1.5 days and saving over 800 person-days.
This article explains why upgrading MySQL from 5.7 to 8.0 can cause an "Illegal mix of collations" error in FIND_IN_SET queries, analyzes the root cause, and presents three practical solutions—adjusting server parameters, altering table collations, or rewriting the SQL—to restore correct operation.
This guide explains the fundamentals of Druid as a Java database connection pool, how to add required Maven dependencies, configure various Druid properties and filters for SQL and web monitoring, enable slow‑SQL logging, integrate Spring AOP monitoring, remove the default Alibaba advertisement, and retrieve monitoring data via the DruidStatManagerFacade API.
This guide explains how to integrate Alibaba's Druid connection pool into Spring Boot, covering basic concepts, Maven dependencies, essential configuration properties, built‑in filters, monitoring pages, SQL and slow‑query logging, Spring AOP monitoring, ad removal, and programmatic access to Druid statistics.
This article explains why new data systems are needed, introduces Apache Arrow and its columnar in‑memory format, describes dynamic read‑time modeling, outlines the system’s execution flow, storage and indexing strategies, and shares practical tips and extensions for building scalable big‑data solutions.
The article walks through three Flink streaming pitfalls—data‑skew‑induced back‑pressure, lost watermarks after interval joins, and ineffective group‑by causing duplicate rows—and shows how to resolve them with two‑stage distinct aggregation, hash‑based key distribution, processing‑time windows or split jobs, and mini‑batch buffering.
This article explains StarRocks' compaction process that merges multiple data versions into larger files to reduce I/O, details the scheduler and executor roles, shows how to monitor and control compaction via SQL commands, and provides tuning parameters and best‑practice recommendations for optimal performance.
This article details how to switch a SpringBoot + MybatisPlus + MySQL project to PostgreSQL by adding the driver, updating JDBC settings, handling numerous SQL and type‑conversion pitfalls, and provides ready‑to‑run PostgreSQL scripts for bulk column modifications and default‑value settings.
This article explains why MySQL columns defined as INT(10) appear as INT after upgrading from 5.7 to 8.0, demonstrates the change with reproducible examples, and discusses the storage limits, proper selection, and display‑width behavior of integer types including ZEROFILL.
This guide details how to use mysqldump and related commands to back up single or multiple MySQL databases, specific tables, compress backups, include drop statements, export only schema, and automate the process with scripts and cron jobs, plus instructions for restoring data.
This article presents twenty practical SQL optimization techniques—including index usage, selective column queries, avoiding functions in WHERE clauses, replacing subqueries with joins, limiting result sets, using prepared statements, proper data types, and transaction handling—to improve query performance and overall database efficiency.
An in‑depth comparison of PostgreSQL and MySQL covers their histories, ACID compliance, performance, scalability, advanced features, security, backup strategies, installation on Linux, and provides practical SQL code examples to help full‑stack developers choose the right open‑source relational database for their projects.
This article explains nine common reasons why MySQL indexes become ineffective and offers concrete solutions—including query rewrites, data type alignment, index redesign, and maintenance tips—to help developers improve database query performance.
This article walks through a real‑world OceanBase OMS migration case where adding an OCP association triggers a “Server Internal Error”, detailing log analysis, metadb schema inspection, OAT deployment checks, the impact of the MySQL “-f” flag, a temporary fix, and the root‑cause conclusion.
This article explains how to design a MySQL table for product sales statistics, initialize massive test data, and compare several query strategies—including loop queries, OR‑concatenated dynamic SQL, mixed filtering, and the SQL‑92 row‑comparison technique—to efficiently retrieve sales figures for multiple business units and their products without violating development constraints.
This article presents ten essential advanced MySQL concepts—including CTEs, recursive CTEs, temporary functions, CASE‑WHEN pivots, EXCEPT vs NOT IN, self‑joins, ranking window functions, delta calculations, running totals, and date‑time manipulation—each explained with clear examples and practical SQL snippets.
MySQL offers a comprehensive set of string functions—including lower, upper, concat, substring/substr, length, char_length, instr, lpad, rpad, trim (with leading, trailing, both options), replace, repeat, reverse, and format—that enable case conversion, concatenation, extraction, padding, trimming, substitution, repetition, reversal, and numeric formatting of textual data.
This article compares Doris and ClickHouse across architecture, table creation, ecosystem integration, management tools, query performance, and join capabilities, offering practical guidance on how to choose the right database based on your specific data processing and operational requirements.
On June 3, 2024 DuckDB announced its 1.0.0 "Snow Duck" release, highlighting a custom single‑file storage format with full transaction semantics, backward compatibility, massive community adoption, and a roadmap focused on stability, extensions, and continued open‑source development.
This article explains how to use PHP's mysqli_num_rows function to retrieve the number of rows returned by a MySQL query, including a complete example with connection handling, error checking, and output of the row count.
This article explains why plain LIKE searches become slow on large MySQL tables, shows how prefix patterns and string functions like LOCATE, POSITION, INSTR, and FIND_IN_SET can leverage indexes, and provides a comprehensive list of thirty practical SQL optimization techniques to dramatically improve query performance.
This article presents a configurable OLAP framework that streamlines multi‑dimensional, multi‑metric analysis by separating core and interaction domains, using metadata‑driven modeling, runtime request handling, and Maven‑based integration to accelerate development and improve query performance.
This article details how to switch a SpringBoot‑MyBatisPlus‑MySQL application to PostgreSQL, covering driver addition, JDBC configuration changes, numerous SQL and type‑conversion pitfalls, helper scripts for bulk column updates, and essential migration notes.
The article defines large database transactions, explains their performance and replication risks, offers practical guidelines to avoid them, demonstrates a MySQL example that creates and repeatedly inserts data to illustrate growing execution time, and discusses challenges and solutions for handling massive tables in high‑traffic environments.
A daily million‑order e‑commerce platform only generates about 30 TPS, making inventory‑deduction and oversell concerns trivial and solvable with a single atomic SQL update rather than complex distributed solutions.
This article explores how to retrieve sales statistics for multiple business units and their associated products in MySQL, compares loop‑based, OR‑concatenated, mixed‑filter, and SQL‑92 row‑comparison techniques, and explains why the row‑comparison method was ultimately chosen for its performance and compliance with development constraints.
This guide walks you through using Alibaba Cloud EMR Serverless Spark to ingest OSS‑HDFS audit logs, create source, detail, and summary data‑warehouse tables with Spark SQL, orchestrate the tasks in a workflow, and schedule daily IP‑traffic analysis, complete with code snippets and UI screenshots.
This article walks through a real‑world Oracle index failure, explains why indexes become UNUSABLE, shows how to clean residual metadata, rebuild the index safely, outlines common causes for both regular and partitioned tables, and provides SQL scripts for monitoring invalid indexes.
Learn how to create a Go-based command-line tool that accepts user-supplied parameters to connect to an Oracle database using the loginWithServiceName function, execute arbitrary SQL queries, and display results, complete with full source code, flag parsing, and error handling.
The article compares MySQL and PostgreSQL, covering their histories, architecture hierarchies, benchmark performance on SELECT/INSERT/UPDATE, suitable use cases, and summarizes PostgreSQL’s advantages and disadvantages relative to MySQL, providing guidance for choosing the appropriate database.
Amid the rise of vector databases, AI, and cloud computing, this article explains how PostgreSQL’s flexibility, extensibility, and open‑source community are reigniting SQL’s relevance in 2024, uniting OLTP and OLAP workloads and positioning PostgreSQL as the versatile backbone for modern data management.
SQLite 3.46.0 introduces extensive PRAGMA enhancements, new date‑time modifiers, support for underscore digits in numeric literals, a json_pretty() function, significant query planner optimizations, improved JSON handling, and the ability to use large hexadecimal literals as column defaults, all while maintaining its public‑domain, self‑contained nature.
The article explains what constitutes an SQL problem, presents a typical case of performance degradation caused by mismatched character sets, and outlines how the SQLE platform can help developers, testers, and DBAs systematically manage and improve SQL quality throughout the software lifecycle.
This article explains the concept of MySQL master‑slave replication, outlines its advantages and drawbacks, and provides a step‑by‑step guide—including downloading MySQL 8.0, configuring my.cnf on master and slave servers, creating replication users, setting up replication parameters, and using Laravel for read‑write splitting—complete with all necessary shell and SQL commands.
This article presents a comprehensive guide to MySQL table design, covering naming conventions, appropriate data types, primary key strategies, field length choices, logical deletion, common fields, indexing rules, normalization trade‑offs, storage engine selection, time type recommendations, and SQL optimization tips.
This article demonstrates how to diagnose and resolve repeated UPDATE failures in OceanBase by extracting information from Top SQL, log files, and the gv$sql_audit view, identifying error codes such as 6005, 6003, and 6212, and applying corrective actions like adjusting timeout settings, killing lock‑holding sessions, and checking lock statistics.
This article explains the different types of InnoDB row locks—including shared, exclusive, gap, next‑key, and insert‑intention locks—by showing preparation steps, SQL examples, lock‑mode details, and how they interact under repeatable‑read isolation in MySQL 8.0.32.
The article explains why using IN and NOT IN in SQL queries can lead to poor performance, index bypass, unexpected results with NULL values, and subtle bugs, and it recommends safer alternatives such as EXISTS, NOT EXISTS, and JOIN constructs.
This article presents a systematic benchmark comparing ClickHouse local (single‑node) tables and distributed tables across three data volumes—≈60 billion, 5 billion and 50 million rows—using a variety of aggregation and filter queries, and reveals that distributed tables dominate at large scale while the gap narrows as the dataset shrinks.
This article presents a detailed case study of refactoring a complex, performance‑critical SQL query used in a JDL routing system, demonstrating step‑by‑step formatting, hierarchical decomposition, and optimization techniques such as layer merging, predicate push‑down, join simplification, and index tuning to dramatically reduce execution time.
This article demonstrates how to refactor a complex, production‑level SQL query used in a routing system by formatting, decomposing into layers, merging temporary tables, pushing predicates, optimizing joins, and validating performance, ultimately reducing nesting from four levels to one and cutting execution time from 4.75 s to 0.6 s.
This article explains common scenarios of implicit type and charset conversions in MySQL, demonstrates how they cause index loss and performance degradation through detailed examples and EXPLAIN output, and provides practical guidelines to prevent such conversions in production environments.
This article provides an in‑depth tutorial on the sqlx package for Go, covering installation, type design, database connection methods, query execution, named parameters, transaction handling, prepared statements, unsafe scanning, field‑mapping customization, and practical code examples to simplify working with relational databases.
A quiet Saturday turned into a disaster when a simple UPDATE query accidentally deleted every order in production, prompting a rapid, step‑by‑step recovery, a post‑mortem analysis of the root causes, and a set of hard‑won operational lessons for any engineering team.
This article presents a comprehensive collection of MySQL performance guidelines, covering query writing, index usage, join strategies, data types, batch operations, and execution plan analysis to help developers write faster and more reliable SQL statements.
This article presents UCloud's USDP‑based streaming data lake warehouse solution that leverages Flink for real‑time processing and Paimon for lake storage, detailing its architecture, advantages, practical scenarios, and providing complete SQL and Flink CDC code snippets for end‑to‑end implementation.
This article explains how InnoDB full‑text search can replace LIKE% fuzzy queries by using inverted indexes, covering index structures, creation syntax, MATCH() AGAINST() modes, practical demos, query expansion, and index removal, while noting when to prefer external search engines.
This article presents a series of B2B testing scenarios—upload validation, rule retrieval, rule‑group matching, and similar rule conditions—demonstrating how targeted code review, precise test case design, and SQL verification can dramatically reduce test effort while uncovering hidden bugs.
This article explains MySQL's transaction concepts, the four isolation levels defined by the SQL standard, how each level prevents dirty reads, non‑repeatable reads, and phantom reads, and provides practical commands and example experiments to configure and observe their behavior in InnoDB.
This comprehensive guide explains MySQL transaction fundamentals, defines dirty reads, non‑repeatable reads and phantom reads, compares the four isolation levels, shows how to query and set them, and details the underlying locking mechanisms such as MVCC and next‑key locks.
Oracle announced its upcoming AI coding assistant, Code Assist, tailored for Java, Oracle Database, NetSuite scripts, and Oracle Cloud, offering chat-driven code suggestions, documentation generation, and security scanning across VS Code and JetBrains IDEs, while emphasizing multi‑language support and data privacy.
Donald Chamberlin, co‑author of SQL, explains why he now supports the NoSQL movement, describing how modern web applications demand scalability that traditional relational databases struggle to provide, and discusses emerging query languages like SQL++ (PartiQL) that bridge the gap between relational and document models.
This article explains how Lazada’s internal selection platform leveraged Hologres and the RoaringBitmap compression algorithm to dramatically reduce storage costs, accelerate set operations, and break the 200,000‑item pool limit, achieving up to a 99% speed improvement in scheduling.
This article explains how MySQL table‑level locks and metadata locks (MDL) can block both reads and writes, describes the two lock implementations, their types, the interaction with LOCK TABLES and FLUSH TABLES, and provides practical queries for diagnosing lock‑related blocking issues.
The OceanBase community offers a detailed, eight‑session DBA tutorial covering the platform’s architecture, deployment, migration, performance tuning, diagnostic tools, and ecosystem components, complemented by live streaming, hands‑on exercises, certification incentives, and real‑world user testimonials to help developers and DBAs master this distributed database.
Oracle’s latest release, Database 23ai, upgrades its 23c platform with AI-driven vector search, RAG capabilities, and enhanced JSON and graph querying, positioning the database as a unified, secure, and scalable solution for handling structured, semi‑structured, and unstructured data across cloud and on‑premises environments.
This article analyzes common anomalies caused by the PageHelper pagination plugin in a Java backend project—such as duplicate user registration, limited query results, and password‑reset errors—traces them to ThreadLocal misuse, explains the internal workflow of startPage, getLocalPage, and clearPage, and offers practical recommendations to avoid these pitfalls.
This article explains eight key reasons why SQL and relational database systems remain the dominant data management solution half a century after their invention, covering performance, maturity, community, simplicity, popularity, open‑source options, powerful query capabilities, and their continued relevance alongside newer technologies.
This article explains how to create a custom annotation and a MyBatis‑Plus interceptor that automatically injects data‑permission WHERE clauses into SQL statements, supports role‑based scopes, and shows both basic and advanced implementations with full Java code examples.
This guide explains how MySQL's InnoDB engine handles transactions and ACID properties, demonstrates exclusive and shared locking mechanisms, shows how to use connection pools, and provides reusable Python code for robust database operations, all illustrated with clear SQL and Python snippets.
This article outlines eight key reasons why SQL and relational databases remain the dominant data management solution half a century after their invention, covering processing power, proven reliability, community support, simplicity, widespread adoption, open‑source growth, practical query power, and their role alongside NoSQL technologies.
This article explains the purpose and inner workings of various database indexing algorithms—including B‑Tree, Hash, Bitmap, and Full‑Text indexes—illustrates their strengths and weaknesses with SQL examples, and provides guidance on when to choose each type for optimal query performance.
This article explains how to dramatically improve MySQL insert speed by adjusting database settings, choosing the MyISAM engine, and applying batch insertion methods such as concatenated SQL, transaction‑wrapped inserts, and MyBatis‑Flex saveBatch, complete with test code and performance results.
This article explains MySQL's architecture, how to view storage engines and variables, demonstrates creating various indexes, describes why SQL optimization is needed, details the EXPLAIN plan keywords and index types, and provides practical best‑practice tips for single‑ and multi‑table query performance improvements.
This guide compiles essential MySQL interview topics—including definitions, differences from Oracle and SQL Server, index types and usage, B‑tree vs B+‑tree structures, deadlock handling, and view concepts—providing a systematic overview for developers preparing for database interviews.
This article walks through the end‑to‑end lifecycle of a SQL task in a big‑data environment, covering creation, scheduling metadata, instance generation, resource allocation, ODPS execution, and final processing on the Fuxi distributed engine.
This article explains the concepts, formulas, and step‑by‑step implementation of a user‑retention analysis model, covering both Hive‑based offline processing and ClickHouse‑accelerated real‑time queries, complete with SQL examples, architecture diagrams, and practical optimization tips.
This article introduces Flyway as a database migration tool, explains its migration types and common commands, demonstrates how to integrate Flyway with Spring Boot using Maven and YAML configurations, provides example SQL migration scripts and logs, and briefly mentions related community promotions.
This guide explains the step‑by‑step SQL execution sequence, offers practical tips for optimizing multi‑table joins, and provides comprehensive indexing recommendations to improve query performance and reduce resource consumption.
This article introduces the Perfetto web UI, explains its four main sections, walks through essential navigation shortcuts, and shares practical techniques such as viewing wake‑up sources, critical paths, pinning threads, inspecting CPU info, buffer usage, timeouts, and logs to help Android developers efficiently analyze performance traces.
This article explains why using IN and NOT IN in SQL queries often leads to poor performance and incorrect results, and demonstrates safer alternatives such as EXISTS, NOT EXISTS, and JOINs with practical code examples.
This article demonstrates how the MySQL LIKE clause can give misleading results when filtering triggers, walks through a step‑by‑step lab that creates tables and triggers, shows the pitfalls of LIKE, and presents a safer WHERE‑based method for retrieving trigger definitions.
This article explains why using OFFSET and LIMIT for pagination becomes inefficient on large tables, illustrates the performance impact with examples, and proposes a cursor‑based pagination alternative that leverages indexed primary keys to achieve faster, scalable queries.
A Java developer recounts a production MySQL outage caused by a faulty batch insert, walks through failed delete and lock‑free attempts, then details a series of table‑recreation, truncate, and index‑rebuilding steps that finally restored data integrity and performance.
This article explains the logical execution order of SQL statements—including FROM‑JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY and LIMIT—illustrates each step with examples and diagrams, and clarifies common misconceptions about clause sequencing.
This article explains how to boost MySQL insertion speed by adjusting server hardware, tuning database parameters, choosing the MyISAM engine, and especially applying three batch‑insert techniques—SQL concatenation, MyBatis‑Flex saveBatch, and manual transaction batching—while showing test results and configuration tips.
This article explains how an invisible zero‑width Unicode character embedded in a MySQL table name can make the table appear to disappear, demonstrates the problem with reproducible examples, analyzes the root cause, and provides practical steps to detect and fix such issues.
The article examines why MySQL LIMIT pagination becomes slower on deeper pages of a 500,000‑row table, analyzes query execution plans, and presents three optimization strategies—ordered index filtering, subquery pagination, and join‑based pagination—to improve performance in real‑world scenarios.
Learn how to assess and optimize your MySQL database using four powerful open‑source tools—mysqltuner.pl, tuning‑primer.sh, pt‑variable‑advisor, and pt‑query‑digest—complete with download links, command‑line usage examples, and guidance on interpreting their diagnostic reports to improve performance and security.
This article explains MaxCompute’s new integrated offline‑and‑near‑real‑time architecture, Transaction Table 2.0, detailing its unified storage and compute design, automatic data governance, schema evolution, upsert and time‑travel capabilities, and how it simplifies complex big‑data pipelines while delivering minute‑level latency and lower costs.
This article presents thirty practical SQL optimization techniques, ranging from avoiding SELECT * and using LIMIT 1 to proper indexing, join strategies, and query rewriting, each illustrated with code examples and clear explanations to help developers write faster, more efficient database queries.
This article explains MySQL events as built‑in schedulers, outlines their advantages, shows how to enable the event scheduler, create, view, modify, and drop events, and provides concrete examples for generating real‑time sales data and periodic statistics.
This article demonstrates how to mask sensitive data such as phone numbers, ID cards, and personal information using SQL string functions, a Java library (sensitive‑plus), and the MyBatis‑Mate Sensitive Jackson plugin, providing complete code examples, configuration files, and test results for backend developers.
This guide explains how to rescue both physically and logically deleted Oracle databases, covering RMAN file recovery, full database restoration, flashback table techniques, recycle‑bin usage, flashback database configuration, and essential backup strategies to prevent data loss.
MyScaleDB is a high‑performance, cost‑effective SQL vector database built on ClickHouse that lets developers use familiar SQL to store, index, and search billions of vectors alongside structured data, offering fast, accurate AI retrieval and seamless integration with existing tools.
This article explains MySQL InnoDB full-text search, covering inverted index structures, creation and usage of full-text indexes, query modes such as natural language, boolean, and query expansion, and provides practical SQL examples and considerations for effective text searching.
This article explains MySQL foreign keys, how to define them with CREATE TABLE syntax, cascade actions, limitations, pros and cons, and provides a complete MySQL 5.7 example with metadata queries and a cascade‑delete test, helping readers understand when to use or avoid foreign keys.
This article analyzes a slow NOT IN subquery that OceanBase rewrites into a NESTED‑LOOP ANTI JOIN, explains why HASH ANTI JOIN is not chosen due to NULL‑sensitive semantics and missing NOT NULL constraints, compares the behavior with Oracle's Null‑Aware Anti Join, and provides practical rewrite and hint‑based optimization recommendations.
This article explains the new mysqld-long-query-time option added to MySQL 8.0.30's mysqldump tool, shows how to configure it, presents comparative experiments demonstrating its effect on slow‑query logs, and provides conclusions and additional notes for practical database backup scenarios.
This article presents nine practical MySQL index optimization techniques—from selecting high‑selectivity columns and avoiding functions on indexed fields to applying the leftmost‑prefix rule, handling NULLs, using proper LIKE patterns, and leveraging EXPLAIN and LIMIT 1 for faster queries.
When a user reported errors, I discovered a MySQL table flooded with millions of bad rows from a new group, causing slow queries and crashes; I detail the step‑by‑step investigation, failed attempts with DELETE and lock‑free changes, and the final successful rebuild using temporary tables, DROP, TRUNCATE, and index reconstruction.
This article explains why using OFFSET with LIMIT becomes a performance bottleneck on massive tables, demonstrates the inefficiency of full‑table scans, and introduces a faster cursor‑based pagination technique that leverages indexed primary keys for efficient data retrieval.
This article walks through three real‑world MaxCompute optimization cases—tuning UNION handling, enabling JSON‑object batch extraction, and removing unnecessary GROUP BY—to dramatically reduce CPU, memory, and storage usage while improving job stability.
This article explains ten advanced SQL techniques—including common table expressions, recursive CTEs, temporary functions, CASE‑WHEN pivots, EXCEPT vs NOT IN, self‑joins, ranking functions, delta calculations, cumulative sums, and date‑time manipulation—providing clear explanations and complete query examples to help readers ace data‑science interview questions.
This tutorial walks through connecting to an SQLite database with PHP, creating a 'users' table using SQL, and verifying the table’s structure through PRAGMA queries, providing complete code examples for each step to help developers understand SQLite integration in web projects.
This article walks through the complete process of upgrading MySQL 5.7.43 to 8.0.34 on CentOS 7, covering lifecycle background, new 8.0 features, pre‑upgrade checks, data backup, configuration changes, installation, upgrade execution, post‑upgrade verification, and troubleshooting of typical SSL and host‑blocking issues.
This article explains how OFFSET and LIMIT cause severe performance problems on large tables due to full‑table scans, demonstrates the issue with real‑world examples, and presents a more efficient cursor‑based (keyset) pagination technique as a practical alternative.
This article explains a real‑world question about grouping 5,000 nationwide records by province to compute counts, totals, averages and ratios, compares using Excel pivot tables, SQL, and demonstrates a concise Python pandas pivot_table solution.
