This article provides a comprehensive overview of data warehouse concepts, compares classic warehouse models, explains dimensional modeling fundamentals such as fact and dimension tables, demonstrates a practical e‑commerce scenario with schema design and SQL query examples, and discusses real‑world trade‑offs.
When a MySQL query uses LIMIT with a large offset on a table of millions of rows, the database must scan hundreds of thousands of index entries and corresponding clustered rows, causing massive random I/O; rewriting the query with a sub‑query join reduces I/O dramatically, cutting execution time from over a minute to under a second while also preventing buffer‑pool pollution.
This article demonstrates how to use Fluent MyBatis, native MyBatis, and MyBatis‑Plus to query a student_score table for term‑wise statistics of English, Math, and Chinese scores, providing complete code examples, SQL statements, mapper definitions, and code‑generation configurations for each approach.
This guide explains step‑by‑step how to identify and delete duplicate records in MySQL tables, covering simple SELECT checks, handling MySQL’s update‑from limitation, and fast deletion techniques that keep one record per duplicate group.
This article offers a thorough overview of relational database fundamentals and SQL syntax, detailing core concepts, clause structures, DDL/DML/TCL/DCL commands, functions, transaction handling, user privileges, stored procedures, cursors, and triggers with practical code examples.
This article explains why a naïve Python script for deleting duplicate MySQL rows is too slow, demonstrates the MySQL error caused by deleting from the same table you query, and provides two pure‑SQL solutions: one that removes all duplicates and another that keeps a single row per duplicate key.
This article benchmarks MySQL's datetime, timestamp, and bigint columns by inserting 500,000 rows and measuring query, group‑by, and order‑by performance, concluding that bigint generally offers the best speed for time‑based operations while timestamp saves space for static timestamps.
This article explains how MySQL treats NULL values, the operators and functions for handling them, demonstrates that IS NULL can still use indexes, shows the impact on aggregate functions, distinct, group by and order by, and recommends defining columns NOT NULL to avoid performance pitfalls.
This article examines the requirements of commercial advertising systems for data storage, traces the evolution from single‑node MySQL to BaikalDB’s cloud‑native, MySQL‑compatible distributed architecture, and details its storage, compute, and scheduling designs, highlighting key features such as Raft replication, RocksDB storage, and hybrid OLTP/OLAP support.
This article explains how PolarDB‑X classifies different data‑deletion scenarios, compares traditional recovery methods like PITR, Flashback Query, and binlog‑based tools, and introduces its precise SQL‑level flashback feature that uses TraceID to quickly and accurately restore mistakenly deleted rows.
The article presents a detailed interview‑style walkthrough of MySQL InnoDB locking mechanisms, covering table‑ versus row‑level locks, shared and exclusive locks, intention locks, gap and next‑key locks, auto‑increment locks, predicate locks, and related SQL statements, illustrated with tables and code examples.
This article explains how Vipshop built the Hera data service since 2019 to provide a unified API for warehouse access, detailing its background, architecture, core features such as multi‑engine queries, adaptive execution, custom Lisp syntax, task scheduling, metrics collection, and performance gains for both B‑to‑B and B‑to‑C workloads.
This article analyzes MySQL 8.0.25 source code, detailing the server architecture, parser reconstruction, prepare/rewrite stages, the optimizer transformations, and the new hypergraph optimizer, while also comparing these mechanisms with PostgreSQL’s processing pipeline.
This article provides a detailed tutorial on relational database fundamentals, covering key terminology, SQL syntax, DDL/DML/DCL/TCL commands, query clauses, joins, set operations, functions, aggregation, indexing, constraints, transactions, permission management, stored procedures, cursors, and triggers with practical code examples.
This article compiles extensive interview questions and topics covering Hadoop, Spark, Flink, Hive, Kafka, MySQL, Redis, Java fundamentals, and algorithms, organized by companies such as Xiaomi, ByteDance, Alibaba, Shopee, Tencent, Meituan, NetEase, and Baidu, to help candidates prepare effectively for big‑data engineering roles.
This article explains why MySQL's auto‑increment counter can be lost after a server restart for InnoDB tables, compares the behavior with MyISAM, demonstrates the problem with practical SQL examples, and shows how MySQL 8.0 fixes the counter logic.
This article explains several MySQL techniques—INSERT IGNORE, ON DUPLICATE KEY UPDATE, and REPLACE INTO—to efficiently insert large volumes of data while preventing duplicate primary‑key or unique‑key rows, and provides practical code examples for single and batch operations.
This article details the practical challenges encountered when migrating a MariaDB 10.3 database to MySQL 5.7.25, explains why certain defaults and engine-specific options cause import errors, and provides step‑by‑step fixes such as adjusting default expressions, removing Aria‑specific parameters, and configuring sql_mode.
This article explains MySQL's three‑layer logical architecture, the end‑to‑end query execution flow, and the inner workings of the optimizer, cache, and storage engine, then offers practical schema, index, and specific‑query tuning techniques to boost database performance.
This article provides a comprehensive collection of MySQL command-line instructions for exporting databases or tables, importing data, managing databases and tables, performing DDL and DML operations, handling field types, and configuring user privileges, all illustrated with practical code examples.
This article explains why a naïve Python script for deduplicating millions of rows is too slow, then walks through a series of MySQL queries—including how to identify duplicate names, avoid the 1093 error, and delete duplicates while keeping a single representative row—demonstrating fast, reliable cleanup of large tables.
This article provides a comprehensive collection of MySQL optimization techniques—including storage engine characteristics, data type selection, index design, and query rewriting—targeted at teams without a dedicated DBA, using MySQL 5.5 and InnoDB as the reference platform.
Flyway is an open‑source database migration tool that version‑controls SQL scripts, supports many databases, and integrates with Maven and Spring to automate schema changes across environments, offering commands such as migrate, clean, info, validate, baseline, undo and repair, with detailed configuration examples.
This article investigates the meaning of the number in parentheses for MySQL INT types, demonstrates through storage tests that INT(4) and INT(11) have identical storage size and range, and explains how the ZEROFILL attribute changes only the displayed width.
This tutorial walks data analysts through setting up a Python environment with Jupyter and Anaconda, importing diverse datasets via Pandas, cleaning and reshaping data, performing calculations, filtering, visualizing results, and finally merging and grouping data to produce insightful analyses comparable to SQL and Excel workflows.
This article explains how to identify and delete duplicate records in large MySQL tables, discusses why a naïve delete fails, and provides two robust SQL solutions—one that removes all duplicates and another that retains a single row per duplicated key—demonstrating fast execution even on tables with hundreds of thousands of rows.
This comprehensive guide covers SQL fundamentals—including database terminology, syntax structure, DDL/DML/TCL/DCL commands, CRUD operations, subqueries, joins, unions, functions, ordering, grouping, indexes, constraints, transactions, permissions, stored procedures, cursors, and triggers—providing clear examples and best practices for relational database development.
This article compiles 100 common MySQL interview questions for developers, covering index structures and optimization, transaction ACID properties and isolation levels, primary‑key design, storage‑engine choices, sharding strategies, and miscellaneous SQL nuances such as binlog formats and MyBatis parameter handling.
This article summarizes comprehensive MySQL index design guidelines, covering naming conventions, column selection criteria, index count control, handling of frequently updated columns, function and duplicate indexes, small‑table considerations, and index ordering to optimize query performance.
This article describes how to identify and delete duplicate rows in MySQL tables—first showing a naive Python‑based approach, then presenting efficient SQL queries that locate duplicate names, explain MySQL's limitation on deleting from the same table being queried, and provide a safe sub‑query solution to remove all duplicates while preserving a single representative row.
This article explains MySQL's SQL Mode concept, lists the default mode values for MySQL 5.7, demonstrates how to view and change the mode with SQL commands, describes the impact of common modes such as STRICT_TRANS_TABLES and ONLY_FULL_GROUP_BY, and provides a complete reference table of all supported mode values and predefined mode combinations.
This article demonstrates how to use the RETURNING clause in Tencent Cloud Enterprise MySQL to retrieve inserted, replaced, or deleted rows directly, showing step‑by‑step examples with images and explaining the behavior of RETURNING, REPLACE RETURNING, and DELETE RETURNING statements.
This article provides a comprehensive collection of 52 practical tips for improving SQL query performance, covering index creation, query rewriting, data type selection, storage engine choices, and execution plan analysis to help developers write faster, more efficient database statements.
This article explains how data-driven decision making evolves from simple Excel and Python analysis to robust data warehouses, introduces star‑schema dimension modeling, and demonstrates practical integration of Alibaba Cloud MaxCompute with Tableau for interactive OLAP‑style analytics.
This article examines a real‑world Oracle SQL query, identifies costly full‑table scans and low‑selectivity columns, and demonstrates how creating an index on a key column reduces execution time from 24 minutes to just one second, highlighting essential SQL performance‑tuning techniques.
This article explains MySQL’s logical architecture, query processing steps, and the principles behind query optimization, covering topics such as client‑server protocol, query cache, parsing, cost‑based optimizer, execution engine, and practical index design strategies to improve performance.
Developers often use SELECT count(*) to check record existence, but replacing it with SELECT 1 … LIMIT 1 improves performance by stopping after the first match, allowing simpler Java null checks and potentially reducing index usage, especially when only a binary presence test is needed.
This article compares Elasticsearch and ClickHouse for log analytics, detailing their architectures, node roles, query capabilities, and performance through Docker‑Compose stacks and synthetic syslog data, concluding that ClickHouse generally outperforms Elasticsearch in speed and aggregation efficiency.
This tutorial explains how to import the built‑in sqlite3 library in Python, create and connect to a SQLite database file, define tables, insert and query records, and seamlessly exchange data with Pandas data frames, while also showing how to access the database via SQL client tools.
The article explains why using COUNT(*) for existence checks is inefficient and demonstrates a more performant approach by selecting a constant with LIMIT 1, providing Java examples and showing how this reduces database work and improves query speed.
This article explores how backend developers can handle intricate e‑commerce search filters by first attempting a monolithic SQL solution, then improving performance with index analysis and query splitting, and finally achieving fast, scalable results using Redis sets, sorted sets, and transaction commands.
ClickHouse is an MPP column‑oriented DBMS that combines full DBMS functionality, advanced columnar storage with high compression, SIMD‑based vectorized execution, a rich relational SQL interface, diverse table engines, multi‑master clustering, and flexible sharding and distributed query capabilities, making it exceptionally fast for analytical workloads.
This article compares ClickHouse and Elasticsearch for API log storage, detailing development activity, schema handling, query performance, statistical functions, MySQL integration, new features, and practical drawbacks, while providing concrete SQL examples and migration tips.
The article introduces the command‑line utility q, explains how to install it on Linux and Windows, and demonstrates using familiar SQLite‑style SQL queries to search, filter, join, and aggregate log files and command output, offering a convenient alternative to traditional shell tools.
This tutorial walks through MySQL join techniques—including WHERE, LEFT, and INNER joins—shows how to retrieve specific columns, and provides clear examples of inserting single or multiple rows, updating records, and deleting data, all illustrated with step‑by‑step SQL code and result screenshots.
This article walks you through using Navicat for MySQL to create databases and tables, design table structures, and perform native SQL SELECT queries—including filtering, multiple conditions, and column selection—illustrated with step‑by‑step screenshots and code examples.
The article explains why a simple GROUP BY query in MySQL 5.6 returns the earliest rows instead of the most recent ones, demonstrates how to correctly obtain the latest record per name using sub‑queries or window functions, and discusses the impact of the ONLY_FULL_GROUP_BY mode in MySQL 5.7.
MySQL deep pagination can be dramatically accelerated by ordering on the primary key, indexing the sort column, and using keyset pagination or a sub‑query join instead of scanning millions of rows, while only minor tweaks like increasing sort_buffer_size provide negligible gains.
This article explains how MySQL may rewrite a LEFT JOIN as an INNER JOIN during optimization when the WHERE clause filters the joined table, demonstrates the behavior with sample class and student tables, and presents test cases confirming the optimizer's decision.
This article investigates why MySQL queries with large LIMIT offsets become extremely slow, demonstrates the issue with simulated millions‑of‑row datasets, and presents three optimization strategies—including index‑covering subqueries, remembering the last primary‑key position, and applying offset throttling—to achieve consistent, fast pagination performance.
This article walks through a practical MySQL tutorial that identifies duplicate records, explains why naïve row‑by‑row deletion is slow, and provides efficient DELETE statements—including derived‑table tricks—to clean up large tables while preserving one record per duplicate group.
This article provides a comprehensive overview of Apache Flink SQL 1.13, detailing new Window TVF support, cumulate windows, performance optimizations, time‑zone handling, enhanced Hive compatibility, SQL client upgrades, DataStream‑Table conversion improvements, and outlines the roadmap for the upcoming 1.14 release.
This article explains how to detect table‑space fragmentation in MySQL, demonstrates the performance impact of deleted rows, and provides step‑by‑step commands—including ALTER TABLE FORCE, OPTIMIZE TABLE, and mysqlcheck—to reclaim space and improve query speed.
The article recounts a recent project where reusing existing stored procedures to check a user's Job, Certification, and Disclosure data led to maintenance headaches, prompting a discussion of alternative approaches, their drawbacks, and why duplicating the query sometimes becomes the only viable solution.
SQLFlow is an open‑source platform that lets users build and run machine‑learning and deep‑learning models directly from SQL statements, lowering the barrier for business analysts to apply AI by abstracting complex pipelines into familiar database queries.
This article provides a comprehensive overview of ClickHouse, covering its columnar OLAP architecture, key features such as data compression, vectorized processing, distributed query handling, and SQL support, followed by detailed step‑by‑step instructions for deploying a multi‑node ClickHouse cluster with MergeTree and ReplicatedMergeTree engines, configuration files, and Java MyBatis integration.
This article introduces the command‑line utility q, explains how to install it on Linux and Windows, and demonstrates using full SQLite‑compatible SQL syntax to query, filter, join, and aggregate plain‑text log files, offering a database‑style alternative to traditional shell tools.
This guide walks you through installing DataGrip, configuring drivers, connecting to various databases, using its powerful SQL editor with shortcuts, customizing appearance, performing data export/import, and leveraging advanced navigation and editing features to boost productivity.
This article explains why correlated subqueries are a performance bottleneck, introduces the Apply (Correlated Join) operator, classifies subquery types, shows how to transform them into efficient joins, and discusses rule‑based push‑down techniques for Project, Filter, Aggregate, and Set operations.
This article explains how MySQL treats backslashes in INSERT and SELECT statements, demonstrates the resulting storage differences with practical examples, and clarifies the double‑escaping rules that developers must handle to avoid unexpected query results.
This article provides a detailed, step‑by‑step tutorial on installing, configuring, and using JetBrains DataGrip—including connection setup, SQL editing, result navigation, data import/export, and advanced editor shortcuts—demonstrating why the tool is considered a powerful and user‑friendly database client.
This article examines the challenges of using Kafka, Flink, and Hive for real‑time data warehousing, introduces Apache Iceberg 0.11 as a solution, details its architecture, query planning, Flink integration, code examples, optimization techniques, and summarizes the benefits for large‑scale data processing.
Java developers often mishandle SQL, leading to performance, correctness, and maintenance issues; this article outlines ten common pitfalls—from misunderstanding NULL and misusing UNION to neglecting MERGE and batch inserts—and provides practical solutions to write efficient, reliable database queries.
This article provides a comprehensive collection of practical MySQL and SQL performance optimization techniques, covering index usage, query rewriting, avoiding full table scans, proper data types, efficient joins, backup strategies, and other best practices to improve database speed and reliability.
This article explains the special nature of NULL in SQL, how to correctly test for NULL using IS NULL, the three‑valued logic of true/false/unknown, and the pitfalls of NOT, NOT IN, and Oracle’s handling of empty strings versus NULL.
This article walks through the challenges of implementing a multi‑criteria product search for a shopping site, compares a naïve SQL solution with optimized query splitting, and then shows how Redis sets, sorted sets, and transactions can dramatically improve performance while adding pagination and update handling.
This article provides an in‑depth overview of Presto, covering its history, core principles, architectural components, query optimization techniques, resource management, tuning tips, data model, and case studies from companies like Didi and Youzan, offering practical guidance for deploying and operating the distributed SQL engine at scale.
This article provides a step‑by‑step walkthrough of installing DataGrip, configuring data sources, using its SQL console, creating tables, exporting and importing data, and leveraging powerful navigation and editor shortcuts to boost database productivity.
This article examines how misplaced quotation marks in MySQL UPDATE statements can unintentionally set fields to zero, explains MySQL’s implicit type conversion that causes the condition to always evaluate true, and offers practical steps for detecting and preventing such bugs.
A developer’s accidental misplacement of quotation marks in UPDATE statements caused all target fields to become zero, and this article explains the MySQL parsing behavior, implicit type conversion, and how to prevent such silent data corruption.
This article examines the pitfalls of reusing existing SQL stored procedures to determine the presence of Job, Certification, and Disclosure data for a user, illustrating maintenance challenges with temporary tables, output parameters, and code‑reuse limitations through concrete code examples.
Starting with MySQL 8.0.23, columns can be marked INVISIBLE, causing them to be omitted from SELECT * queries unless explicitly referenced; this article explains the feature, demonstrates creation, inspection, DML considerations, schema modifications, backup behavior, and its impact on database design.
The article outlines ten frequent errors that Java programmers encounter when writing SQL—ranging from misunderstanding NULL handling to misusing UNION, pagination, and batch inserts—and provides practical solutions to improve correctness, performance, and maintainability of database interactions.
This article outlines ten frequent errors Java programmers encounter when writing SQL, explains why each mistake occurs, and provides practical solutions to improve correctness, performance, and maintainability of database interactions in Java applications.
This article examines the trade‑offs between automatically generated and manually crafted SQL parsers, detailing lexical‑syntax analysis techniques, performance challenges in OLAP workloads, and the authors’ custom parser design that achieves up to 30‑50× speed gains in complex queries and bulk inserts.
This article reviews the history of MySQL and MariaDB, describes a test environment, runs insertion, batch, and query benchmarks (with and without indexes), and concludes that MariaDB generally outperforms MySQL in speed and memory usage while highlighting trade‑offs of indexing.
This tutorial introduces pandasql, a compact Python library that enables SQL queries on pandas DataFrames within the Rodeo IDE, covering installation, built‑in datasets, basic queries, aggregation, joins, and visualization tips for data‑analysis beginners.
This article provides a detailed tutorial on using MyBatis-Plus in Java, covering setup, entity and mapper definitions, CRUD operations, annotations, condition builders, pagination, AR mode, logical deletion, automatic field filling, optimistic locking, performance analysis, multi‑tenant support, dynamic table names, configuration options, and code generation.
This article provides a thorough overview of relational database fundamentals, SQL syntax structures, data definition and manipulation languages, transaction control, permission management, as well as detailed examples of CRUD operations, subqueries, joins, unions, functions, constraints, stored procedures, cursors, and triggers, all illustrated with practical code snippets.
This article explains how 58's commercial data team built a comprehensive data metric system—from identifying common metric definition issues to establishing a domain‑driven hierarchy, distinguishing atomic and derived metrics, implementing a unified metric management platform, and providing APIs and examples for querying and visualizing metrics.
The article examines a production MySQL query that suffers severe slowdown due to large LIMIT offsets, demonstrates how to reproduce the issue with massive test data, analyzes the root cause, and presents three optimization strategies—including index covering, keyset pagination, and offset limiting—to dramatically improve query performance.
This article introduces the fundamentals of distributed database architectures, compares Shared‑Nothing and Shared‑Storage designs, explains their three‑tier structure, core engine components, SQL execution flow, performance and cost optimizations, and showcases a real‑world high‑traffic deployment in Douyin’s Spring Festival event.
This article explains how incorrect placement of quotation marks in MySQL UPDATE statements can cause the SET values to be evaluated as boolean expressions, leading to implicit type conversion that updates fields to zero, and offers practical steps to prevent such errors.
An engineer describes a production MySQL pagination issue where large offset queries caused severe slowdown, analyzes the root cause, demonstrates data simulation scripts, and presents three optimization strategies—including index covering with subqueries, redefining start positions, and applying offset limits—to improve query performance on massive tables.
This article provides a comprehensive overview of data warehouses, explaining their purpose, differences from databases, OLTP vs OLAP, traditional versus internet data warehouse models, layered architecture, modeling theories, metric dictionaries, date dimensions, naming conventions, data governance, and incremental synchronization techniques with practical SQL examples.
The article explains PHP's PDO::exec method, detailing its signature, parameters, return value, and provides a complete example showing how to delete rows from a database table and retrieve the count of affected rows.
This article demonstrates how PostgreSQL 14's pageinspect extension adds functions to examine GIST index internal pages, provides step‑by‑step SQL examples for creating a test table and index, and shows how to query opaque page info and item data using the new functions.
The PDO::__construct method creates a PDO object that represents a connection to a database, detailing its parameters—DSN, username, password, and driver options—return value, and providing a complete PHP example of establishing and handling a MySQL connection.
This article examines common MySQL performance pitfalls such as inefficient LIMIT offsets, implicit type conversions, suboptimal UPDATE/DELETE joins, mixed sorting, misuse of EXISTS, and limited condition pushdown, then demonstrates concrete rewrites—including range reduction and WITH clauses—that reduce execution times from seconds to milliseconds.
This article explains MySQL's three time types, highlights the quirks and automatic defaults of TIMESTAMP—including the 2038 problem, high‑concurrency issues, and unexpected behavior in non‑strict mode—provides practical examples, and offers guidance on configuring server variables or switching to DATETIME for safer handling.
The article explains the functional differences and performance characteristics of MySQL's count(1), count(*), and count(column) expressions, showing when each is appropriate and providing a concrete example with query results to illustrate their behavior.
This article explains the concept of database transactions, the ACID properties, MySQL's four isolation levels, the phenomena they cause such as dirty reads, non‑repeatable reads and phantom reads, and provides step‑by‑step experiments demonstrating each isolation level in practice.
Flink SQL's idle state retention time feature prevents state explosion by automatically cleaning up state for keys that remain inactive beyond a configurable time window, requiring both minimum and maximum retention settings, with implementation details involving CleanupState, timers, and KeyedProcessFunctionWithCleanupState.
This article presents Qunar's practical experience with Flink and Iceberg 0.11, covering background challenges such as Kafka data loss and Hive metadata pressure, explaining Iceberg architecture, query planning, and detailed solutions including real‑time ingestion, small‑file handling, sorting, and code examples for seamless migration.
A developer’s production fix using dozens of UPDATE statements unintentionally set a column to zero because of misplaced quotation marks, illustrating how MySQL’s implicit conversion and syntax quirks can turn seemingly correct SQL into disastrous data loss.
This article explains how LEFT JOIN always returns all rows from the left table, how conditions placed in the ON clause affect only the right‑table matching without filtering left‑table rows, and how a WHERE clause filters the final result, illustrated with multiple SQL examples.
This guide presents a detailed MySQL inspection checklist covering operating‑system metrics, critical MySQL parameters, status queries, replication health, high‑availability components, and useful SQL scripts, enabling DBAs to efficiently monitor performance, detect issues, and maintain reliable database services.
This article explains the concepts of isolation levels, dirty reads, phantom rows, and how various relational and NoSQL databases implement these phenomena, providing practical guidance on choosing the appropriate isolation level to ensure data consistency and performance.
The article shares a developer's post‑interview reflections covering self‑introduction, Spring ecosystem usage, Nacos vs Eureka service discovery, SpringBoot startup mechanics, design patterns, Linux commands, Redis operations, Excel POI handling, SQL optimization techniques, and other practical interview questions.
This article explains MySQL full‑text indexing, compares its syntax with ordinary SQL, demonstrates how to create and query a full‑text index using natural language, boolean, and query‑expansion modes, and shows performance differences through execution‑plan analysis and relevance ranking.
