This article explains how to install the IP4R PostgreSQL extension via PGXS, describes its custom IP address and range data types, demonstrates common queries, functions, operators, and GiST indexing techniques, and compares its performance against bigint‑based lookups for efficient IP range handling.
This article explains how MySQL decides whether an index can be used for range queries, LIKE patterns, and sorting, detailing key_len calculation, index key vs. filter vs. table filter concepts, and practical examples with BETWEEN and ORDER BY.
This guide presents four practical MySQL performance‑boosting techniques—selecting optimal column types, replacing sub‑queries with JOINs, using UNION instead of temporary tables, and employing transactions with BEGIN/COMMIT/ROLLBACK—to make queries faster and ensure data integrity.
HybridDB for MySQL delivers millisecond‑scale, multi‑dimensional filtering on billions of rows with hundreds of metrics by combining a high‑performance columnar engine, automatic composite indexes, and a fused MPP‑DAG pipeline, turning half‑day push preparation into seconds while supporting full SQL, spatial, and JSON data.
This article walks through diagnosing unexpected Oracle parallel execution, resolving DBLINK‑induced plan anomalies after a database upgrade, and fixing ORA‑00604 errors caused by missing domain indexes, providing step‑by‑step queries, parameter checks, and corrective ALTER statements to restore normal performance.
A developer’s series of MySQL UPDATE statements unintentionally set a column to zero due to misplaced quotation marks, and the article explains how MySQL parses such syntax, the resulting data loss, and best practices to avoid similar errors.
This article explains how InnoDB stores data pages, builds page directories, and uses B+‑tree indexes—including clustered, secondary, and composite indexes—to enable fast record lookup, while also covering MyISAM indexing differences and the SQL statements for creating and dropping indexes.
This article explains how to use pessimistic (FOR UPDATE) and optimistic (version field) database locks in a Java SSM red‑packet application to prevent over‑issuance, details implementation steps, code changes, performance testing, and the trade‑offs between lock types.
This article explores various SQL engines and storage options for big‑data workloads, compares their performance and capabilities, shows practical code examples, and offers guidance on writing efficient SQL in complex data environments.
This article presents ten classic MySQL error scenarios—from connection limits and replication conflicts to permission issues and timeout settings—explaining their causes, illustrating them with screenshots, and offering concrete configuration tweaks and procedural steps to resolve each problem efficiently.
This article presents ten classic MySQL error scenarios—from connection limits and replication conflicts to permission issues and character‑set problems—explaining their root causes, diagnostic commands, and step‑by‑step solutions to help beginners and DBAs troubleshoot efficiently.
This article explains MySQL's XA distributed transaction mechanism, covering its 2‑phase commit process, the roles of resource and transaction managers, internal vs. external XA, essential SQL syntax, practical code examples, common pitfalls, performance considerations, and reference resources.
This tutorial explains why and how to use MySQL window functions, covering their concepts, syntax, various function families, practical examples such as ranking, distribution, lead/lag, first/last, and aggregations within dynamic windows, plus detailed SQL snippets and visual illustrations.
Xiaomi is open‑sourcing its Go‑based SQL Optimizer and Rewriter (SOAR) at OSCAR Days, a tool that automatically checks SQL quality, suggests index improvements, rewrites queries, provides EXPLAIN analysis, and integrates with editor plugins and a web self‑audit platform to boost database reliability and developer productivity.
This article explains how Common Table Expressions (CTEs) simplify hierarchical queries in SQL, covering both non‑recursive and recursive forms, providing step‑by‑step examples for retrieving child, parent, and root categories using MySQL 8 and PostgreSQL 10, along with test data and execution results.
This article details how Beike Zhaofang leveraged the ClickHouse columnar OLAP database for large‑scale user behavior analysis, covering its architecture, key features, performance benchmarks against other engines, data ingestion pipelines, custom UDFs for funnel and retention metrics, deployment setup, and future enhancements.
This article introduces the open‑source BI platform Redash, outlines its wide‑range data source support and visualization capabilities, and provides step‑by‑step Docker‑Compose installation instructions along with practical usage tips for building dashboards.
This tutorial walks you through logging into Navicat, creating a MySQL database, designing a table with fields and a primary key, inserting sample data, and running a simple SELECT query, all illustrated with screenshots for clear, hands‑on learning.
This article explains the concept of FUZZ testing, describes its two main techniques, presents the design and core functions of a self‑developed FUZZ testing tool, showcases real‑world test results on SQL and HTTP interfaces, and outlines future enhancements for broader applicability.
This comprehensive guide walks you through mastering MySQL by covering server configuration choices, storage engine selection, data type recommendations, index strategies, backup methods, logging options, and practical advice on triggers, views, stored procedures, and character set handling, all aimed at boosting performance and avoiding common mistakes.
This article explains fundamental MySQL concepts including primary, candidate, super and foreign keys, transaction ACID properties, view creation, differences among DROP, DELETE and TRUNCATE, index structures and types, join variations, normalization forms, optimization techniques, replication mechanisms, engine distinctions, and logging formats.
This article explores PostgreSQL’s advanced SQL capabilities—including Common Table Expressions (WITH), batch inserts, the RETURNING clause, UPSERT with ON CONFLICT, TABLESAMPLE methods, aggregation functions like string_agg and array_agg, and a range of window functions—providing practical examples, performance insights, and guidance for real‑world use.
Through detailed debugging and source code analysis, the author discovers that MyBatis's SqlSession.clearCache() does clear the first‑level cache, but the observed stale query results are caused by MySQL's default REPEATABLE‑READ isolation level, which can be resolved by setting the transaction isolation to READ COMMITTED.
This article explains how to use Bash shell scripts on Linux to automate MySQL database testing tasks such as creating tables, inserting, updating, and monitoring dynamic database states, providing step‑by‑step code examples and execution instructions for efficient test automation.
This guide explains how to safely archive massive MySQL tables by using a custom reimplementation of Percona's pt-archiver that creates a temporary copy, swaps tables via triggers, and moves old data to an archive, reducing disk usage without deleting original rows.
Apache Hive is an open‑source data‑warehouse system built on Hadoop that enables users to read, write, and manage large distributed datasets using SQL‑like queries, offering features such as ETL support, various file‑format connectors, extensible UDFs, and integration with tools like Tez, Spark, and MapReduce.
This article explains the importance of transaction isolation in ACID, describes the four InnoDB isolation levels—READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE—covers their locking behavior, effects on replication, and performance trade‑offs, and offers guidance on choosing the appropriate level.
This article details the architecture and implementation techniques of T‑TDSQL’s dual‑temporal database system, covering full‑state data reading, history‑table creation, snapshot query syntax, transaction snapshot attributes, visibility algorithms, transaction‑state management, index design, and valid‑time creation, query, update, and delete operations.
A production Oracle database suffered widespread query failures because collected system statistics gave the optimizer incorrect I/O cost values, causing it to favor index scans over full table scans; the article details the diagnosis, root cause, and a simple fix by deleting the system stats.
This article provides a concise overview of Apache Hive, covering its definition, Hadoop background, architecture, query workflow, storage model, advantages, disadvantages, and a comparison with traditional relational databases, helping readers understand how Hive enables SQL-like queries on data stored in HDFS.
The article walks through two common Oracle performance problems—slow tablespace‑information queries and sluggish ASH data collection—explaining root causes, showing diagnostic steps, and providing exact SQL and PL/SQL scripts to gather statistics, clean up partitions, and restore fast query response.
This guide introduces Elasticsearch 6.3's native SQL support, demonstrates how to bulk‑load sample books, run SQL queries, apply ordering, limits, combine filters, translate SQL to ES DSL, and use the built‑in SQL client, providing a practical walkthrough for developers.
This article explains how T‑TDSQL leverages full‑temporal storage and snapshot‑difference techniques to perform incremental data extraction, precise account reconciliation, online flashback, and data replay, illustrating the underlying SQL commands, error‑handling tables, and practical benefits for internet finance workloads.
This article presents a comprehensive overview of Oracle partitioning, covering basic concepts, the evolution of partition features across versions, practical best‑practice scenarios, and the latest enhancements in Oracle 12c, 12.2, and 18c that improve performance, manageability, and availability.
This article provides concise MySQL command examples for creating unique and full‑text indexes, multi‑column indexes, views, triggers, inserting data, pagination, transaction handling, character set configuration, and basic service management.
This article provides a concise introduction to essential SQL concepts, covering database creation, manipulation, table design, constraints, indexes, and common DDL statements, making it a useful reference for beginners and experienced developers alike.
This article walks through choosing a web framework and database, introduces relational databases, explains design steps such as requirement analysis, table creation, primary key selection, relationship modeling (one‑to‑many, many‑to‑many, one‑to‑one) with SQL examples, and covers normalization forms, integrity rules, and indexing strategies.
This guide explains MariaDB 10.3 system-versioned tables, showing how to create them with PERIOD FOR SYSTEM_TIME, query historical rows using FOR SYSTEM_TIME clauses, restore previous data via export/import, partition history to reduce storage, and safely manage versioning in production environments.
This article recounts a May 2018 incident where a missing WHERE clause caused a full‑table update of user asset data, detailing the step‑by‑step recovery attempts, iterative testing, and the key lessons learned about backup, simulation, and cautious production operations.
This guide outlines a comprehensive, step‑by‑step strategy for preparing Java backend interview candidates—covering architecture, databases, core Java, multithreading, JVM, and practical tips to demonstrate senior‑level expertise.
This guide provides a comprehensive overview of MySQL fundamentals—including CRUD statements, table creation, index management, data manipulation, transaction control, stored procedures, and trigger creation—complete with syntax examples and practical tips for reliable database development.
The article describes how to build and use a sql-review-helper tool that automatically captures all SQL statements during CI, deduplicates them, runs EXPLAIN to generate fingerprints, and stores the results in a reviewable file so that DBA and developers can reliably review every new SQL before it reaches production.
This guide outlines best‑practice naming rules, storage engine choices, data type recommendations, and a collection of performance‑oriented MySQL techniques—including indexing strategies, query optimizations, pagination methods, and security measures—to help developers design efficient and maintainable database schemas.
This article explains the new MySQL 8.0 features for user and role management, including changes to authentication plugins, password encryption, dynamic SET PERSIST configuration, password expiration and reuse policies, and detailed SQL examples for creating users, granting privileges, and managing roles.
A mathematician‑turned‑engineer recounts his 2015‑2022 path from undocumented recommendation systems at Tencent, through high‑precision security models, reinforcement‑learning game AI, quantum‑ML studies, to large‑scale AIOps time‑series anomaly detection, offering practical lessons for anyone transitioning into machine learning.
This article presents a comprehensive set of MySQL optimization techniques—including query‑cache tuning, proper use of EXPLAIN, indexing strategies, avoiding costly functions, and leveraging prepared statements—to help developers eliminate database bottlenecks and boost web‑application performance.
This article demystifies MySQL query optimization by explaining the server's logical architecture, the end‑to‑end query processing flow, the role of the client/server protocol, query cache, parsing, optimizer, execution engine, and result delivery, and then offers concrete performance‑tuning recommendations on schema design, data types, indexing strategies, and specific query patterns such as COUNT(), JOIN, LIMIT pagination, and UNION.
This article introduces MySQL 8's Common Table Expressions (CTEs), explains their syntax and advantages over derived tables, and provides multiple practical examples—including recursive queries, table creation, and usage in INSERT, UPDATE, and DELETE statements—plus a Docker setup tip for MySQL 8.
This article presents essential MySQL command-line operations, database and table management statements, and compares equivalent SQL syntax in Oracle, covering date handling, string functions, and notable differences such as pagination and auto‑increment behavior.
This article walks through MySQL 8.0's key relational‑database enhancements, including invisible indexes for performance testing, the SET PERSIST command for persistent configuration, default utf8mb4 encoding, Common Table Expressions for clearer queries, and window functions for advanced ranking and aggregation.
This article shows how to identify and remove duplicate rows in a MySQL table by defining duplication, using GROUP BY with HAVING, creating temporary tables with MIN, and applying various techniques—including UNION, nested subqueries, and joins—to handle single‑column and multi‑column duplicate detection.
This guide explains why an Oracle DBMS_JOB can end up with a next execution date of 4000‑01‑01, how to interpret its status fields, diagnose the underlying errors, and restore the job using manual execution and log‑based troubleshooting steps.
This guide shows how to use SRVCTL commands and SQL queries to view the status of Oracle RAC databases, instances, services, node applications, ASM instances, and disk groups, as well as how to start and stop the entire RAC cluster safely.
This article explains how MySQL indexes work, why queries can become slow, how to interpret the EXPLAIN output—including id, select_type, table, type, possible_keys, key, key_len, ref, rows, and extra columns—and provides practical examples and optimization cases to improve query performance while balancing the cost of maintaining indexes.
This guide walks through configuring Oracle multi‑master data replication, covering site creation, group objects, propagator and receiver setup, purge scheduling, database link creation, replication group definition, table registration, conflict handling, and finally resuming replication, all illustrated with complete SQL code examples.
This tutorial demonstrates how to install PyMySQL, establish a MySQL connection in Python, and perform table creation, data insertion, and query operations using example code, while outlining required prerequisites such as credentials and database details.
This article presents thirty practical SQL tuning guidelines—ranging from avoiding certain WHERE‑clause operators and functions to proper index usage and set‑based alternatives—that help prevent full table scans and improve overall database performance.
This article compiles essential MySQL interview topics, covering ACID transaction properties, isolation levels and their issues, storage engine differences, index types, query execution order, lock mechanisms, temporary tables, normalization, read‑write splitting, performance tuning, and recovery logs, providing concise explanations and examples for each concept.
This article investigates the classic MySQL pagination “optimization” technique, reproduces tests on a low‑end server, compares plain LIMIT queries with the sub‑query rewrite on both clustered and non‑clustered indexes, and explains why the method sometimes improves performance and sometimes adds overhead.
The article explains that Oracle Enterprise Manager requires listeners with dynamic registration, shows how a static listener on a non‑default port causes a "database is down" error, and provides step‑by‑step commands to modify the local_listener parameter so both listeners register dynamically and EM works correctly.
This article explains the challenges of MySQL SQL auditing, compares semi‑automated and manual review methods, and introduces Inception—a MySQL‑compatible automated auditing, execution, and rollback system that addresses many of the pain points faced by DBAs.
Both MySQL 8 and MariaDB 10.2 introduce robust window functions and Common Table Expressions (CTEs), enabling developers to write more efficient, maintainable queries; this article explains their syntax, demonstrates practical examples, compares implementations, and highlights performance and readability benefits across popular database systems.
This article examines a high‑volume Oracle query scenario, explains why using multiple IN clauses on millions of rows leads to costly full‑table scans, and presents three alternative strategies—including a single‑SQL join, UNION ALL view, and temporary‑table approaches—along with practical examples and performance considerations.
This article outlines the most frequent SQL performance mistakes—such as misusing UPDATE, over‑selecting columns, unnecessary double queries, and improper use of GUIDs—and provides concrete, actionable techniques like CASE expressions, temporary tables, batch operations, and table‑valued functions to dramatically improve query speed and concurrency.
This guide explains Oracle's TX row lock mechanism, shows how to identify blocking sessions, locate the exact locked rows using system views and ROWID functions, and provides practical steps to prevent and mitigate TX lock waiting issues.
By switching to the InnoDB storage engine, allocating about 80% of system memory to its buffer pool, and configuring multiple buffer pool instances, you can significantly improve MySQL performance, with practical commands and guidelines for various memory sizes.
This comprehensive guide introduces MySQL fundamentals, walks through installation on Windows and Linux, explains core commands for connecting, creating databases, managing users and privileges, performing table CRUD operations, handling data types, and provides a curated list of analysis, backup, performance, HA, and GUI tools for MySQL.
This article traces the evolution of database systems—from early navigational models and Codd's relational theory through the rise of NoSQL and sharding, to the resurgence of NewSQL and the emerging HTAP paradigm—highlighting technical motivations, design trade‑offs, and future directions.
This article explains how Alibaba built a massive real‑time computing platform using Flink and its Blink extensions, detailing the challenges of ultra‑low latency, exactly‑once guarantees, and high throughput, and showing how these technologies powered the record‑breaking Double 11 shopping festival.
This article presents five practical MySQL index usage questions, each with multiple‑choice options, followed by detailed answers and explanations that illustrate when an index is appropriate, how function usage affects it, and the impact on query performance.
A 2017 roundup highlights ten innovative database releases—including a time‑series extension for PostgreSQL, a multi‑model Azure service, Google’s globally distributed Spanner, Amazon’s Neptune graph service, and several open‑source cloud‑native databases—detailing their key features, architectures, and intended use cases.
This article explains MySQL character sets and collations, their relationship, how to view supported options, and step‑by‑step methods for configuring them at server, database, table, and column levels, plus guidance on when each setting should be applied.
This article explains the four MySQL transaction isolation levels—Read Uncommitted, Read Committed, Repeatable Read, and Serializable—by creating a sample table and running step‑by‑step command‑line sessions that demonstrate dirty reads, non‑repeatable reads, phantom reads, and the practical limits of InnoDB's MVCC implementation.
This article shares practical experience deploying MariaDB ColumnStore for offline OLAP, covering its MPP‑based architecture, user‑experience benefits, high‑availability options, numerous syntax and data‑type limitations, and detailed scripts for schema conversion and incremental data extraction using pt‑archiver.
This article explains how PostgreSQL transforms and rewrites query trees, why a completed transform‑rewrite tree may not be optimal, and details the subquery_planner’s role, tuple_fraction handling, and the step‑by‑step logical optimization process used to generate efficient execution plans.
This article explains the classification of Oracle data dictionary objects, demonstrates how to query static and dynamic dictionary tables and views, lists common view families and permission views, and provides numerous SQL examples for inspecting users, tables, indexes, sequences, constraints, and PL/SQL objects.
This article walks through the problem of duplicate rows in a MySQL table, shows an initial complex SQL attempt, compares a slow PHP‑based cleanup, and finally presents a concise, three‑step DELETE statement that eliminates duplicates in under a second.
This article explains why conventional indexes waste space and perform poorly on highly skewed columns, introduces a decode‑based function index that excludes high‑frequency values, details the experimental setup with millions of rows, compares index size and query performance, and outlines the method's limitations.
Learn a comprehensive methodology for SQL performance tuning, from identifying resource bottlenecks and understanding query processing stages to applying practical optimization tactics such as efficient joins, index usage, avoiding costly operations, and best‑practice guidelines that improve execution speed and resource consumption.
An Oracle SQL query with five EXISTS permission checks runs dramatically slower when only a few permissions are configured, and the author investigates the root cause using execution plans, SQL Monitor data, and step‑by‑step testing, ultimately revealing how execution counts and row returns drive the unexpected performance degradation.
This article introduces MySQL 8.0.2's window functions, demonstrates how to compute yearly average ratings and rank movies by year and overall using examples with sample tables, and explains the syntax and benefits over traditional GROUP BY queries.
This article outlines essential database design principles and a comprehensive set of query optimization techniques, covering row size limits, appropriate data types, field length choices, index usage, avoiding costly WHERE clause patterns, and best practices for temporary tables, transactions, and result set handling to boost SQL performance.
This guide explains the factors that affect MySQL INSERT performance and presents a collection of practical techniques—including multi‑row INSERT, bulk_insert_buffer_size tuning, INSERT DELAYED, LOAD DATA INFILE, table creation with disabled keys, LOCK TABLES, and the proper use of IGNORE and ON DUPLICATE KEY UPDATE—to dramatically speed up large‑scale data loading.
This article explains the importance of upgrading PostgreSQL to benefit from new features and security fixes, compares traditional upgrade methods, introduces the PGQ replication system, and provides a detailed step‑by‑step guide for performing a major version upgrade with minimal downtime.
This guide walks you through using MySQL's native JSON data type—creating tables with JSON columns, inserting simple and complex JSON values, updating nested structures, generating indexed columns from JSON fields, converting strings to JSON, and a comprehensive list of built‑in JSON functions.
The article explains how NetEase’s real‑time big‑data stream computing platform, Sloth, handles massive, continuously generated data during China’s Double 11 shopping festival, covering use cases, architectural shifts from batch to incremental processing, technical challenges, and the role of stream‑SQL for easier development.
The article explains how continuous data generation and real‑time stream processing enable e‑commerce platforms like NetEase Kaola to handle massive Double 11 traffic, showcasing use cases, architectural shifts from batch to incremental computing, and the technical challenges of latency, accuracy, and fault tolerance.
This guide presents thirty‑four practical Oracle SQL optimization techniques—from choosing the most efficient table order and rewriting WHERE clauses to leveraging indexes, avoiding SELECT *, using DECODE, and replacing costly operations—helping developers dramatically improve query speed and resource usage.
This article examines 2017 GitHub data to compare how major tech companies like Microsoft, Google, Amazon, and others contribute to open‑source projects, revealing surprising rankings, star counts, and the methodology behind the analysis using Google BigQuery and custom SQL queries.
The article examines why a MySQL DELETE statement with a composite index on biz_date and status still triggers a full‑table scan, shows EXPLAIN output, tests forced index usage, and ultimately demonstrates that narrowing the date range in the query dramatically reduces rows examined and improves performance.
Facing a last‑minute performance crisis on a major release, the author dissected eight SQL statements, merged four permission‑related queries using Oracle's RANK function, and reduced the responsibility‑lookup time from five seconds to under one second, ensuring the system met its strict latency target.
The article introduces data-ci, a SQL‑driven unit testing framework that lets engineers write, organize, and automate data validation tests for ETL pipelines, providing assertions, failure callbacks, coverage reporting, and CI integration to improve data quality and reliability.
This guide explains MySQL B+Tree index structures, their advantages, how to create primary, unique, ordinary, full‑text, composite and prefix indexes, and provides practical rules and tips for designing efficient indexes, query patterns, and sorting strategies to dramatically improve database performance.
This article walks through diagnosing a sluggish Oracle search query with many optional filters, removing unnecessary joins and function calls, applying conditional MyBatis joins, and creating composite indexes to dramatically lower the execution plan cost from 13902 to under 100.
This guide provides a comprehensive collection of Oracle SQL commands covering connection, query execution, variable handling, formatting, tablespace creation, modification, and external table operations, presented step‑by‑step for database administrators and developers.
When a sales employee's workflow records vanished after a WeChat ID change, the team traced the deletion to MySQL binary logs, extracted the relevant log entries, and used bulk UPDATE statements to replace the old user_id with the new one, fully restoring the missing data.
In a high‑pressure performance‑optimization project, a team dissected a massive, multi‑with‑clause SQL report into smaller temporary‑table queries, applied dimensional modeling, and achieved a 6‑second runtime reduction while handling complex reporting requirements across regions, contractors, and milestones.
The article compiles Meituan‑Dianping engineers' Q&A covering SQL book suggestions, relational versus NoSQL engine choices, performance tuning techniques, time‑series database uses, sharding versus partitioning strategies, query handling in distributed systems, proxy limitations, and practical advice on replication and pre‑database setups.
This article provides a detailed, side‑by‑side comparison of MySQL and PostgreSQL, covering their histories, licensing, feature sets, performance characteristics, extensions like PostGIS, indexing, clustering, transaction handling, and practical guidance on choosing the right database for specific workloads.
The article investigates why a seemingly indexed MySQL DELETE query still performs a full‑table scan, analyzes execution plans and statistics, demonstrates forcing the index, and finally rewrites the query to limit the date range, achieving a much faster operation.
