This article examines eight frequent MySQL performance problems—including misuse of LIMIT, implicit type conversion, sub‑query updates, mixed ordering, EXISTS, condition push‑down, early limiting, and intermediate result push‑down—and provides concrete rewrite techniques and example code that dramatically reduce execution time.
SQL database queries are essential skills for data analysts, enabling them to extract, transform, and analyze data from databases through various operations like filtering, sorting, aggregation, and joining.
This study analyzes JD e‑commerce operational data from February to April 2018, employing Python and SQL to compute key metrics such as PV, UV, conversion rates, attrition, purchase frequency, time‑based behavior, funnel analysis, retention, product sales, and RFM segmentation, and provides actionable recommendations for improving user engagement and sales performance.
This article explains why a DELETE statement with a table alias fails in MySQL 5.7, shows the correct syntax without alias, compares the DELETE syntax of MySQL 5.7 and 8.0, and concludes that alias support in single‑table DELETE was added starting with version 8.0.16.
This guide presents ten practical MySQL performance optimization methods—including index creation, query rewriting, limiting result sets, avoiding SELECT *, reducing joins, using proper data types, caching with Redis, and server configuration tweaks—each illustrated with concrete SQL code examples to help you speed up your database operations.
This article explains how PHP can connect to various databases, execute queries, fetch results, insert or update records, close connections, handle errors, and address security concerns, providing code examples for both MySQLi and PDO extensions.
Through a series of local experiments on MySQL tables with varying data volumes, this article analyzes the performance trade‑offs between JOIN queries and IN‑list queries, detailing execution plans, PHP benchmarking, and the impact of large IN lists on query speed and reliability.
This article analyzes a coupon‑claiming race condition that caused stock to become negative under load and presents four solutions—including Java synchronized blocks, SQL conditional updates, Redis SETNX locks, and Redisson client locks—to reliably prevent over‑issuance in backend systems.
This guide walks you through TDengine setup for IoT, covering password changes, login syntax, database and table creation, super‑table design, tag management, and practical insertion examples, all with clear command‑line snippets.
This article examines why MySQL permits creating multiple identical indexes on the same column, demonstrates the behavior through several tests, compares it with Oracle's stricter restrictions, and explains how the optimizer selects indexes, highlighting practical implications for database design.
This article traces PostgreSQL's origins from the 1980s INGRES project, compares navigation and relational databases, outlines its evolution through Postgres95 to PostgreSQL, and highlights ten modern features that make it a powerful, scalable, and secure choice for developers today.
This article offers a concise MVP learning plan for recent graduates aiming to secure a big‑data internship, covering essential computer fundamentals, core big‑data frameworks, project ideas, and algorithm/SQL practice, along with practical study tips and resource recommendations.
This article analyzes the performance problems of LIMIT‑based pagination on massive MySQL tables and presents three progressively more efficient solutions—including a simple LIMIT approach, a tag‑record method using the last primary key, and a range‑limit method with cached min‑ID—along with best‑practice indexing recommendations to keep query latency in the tens of milliseconds even for tables containing billions of rows.
This article explains what a MySQL deadlock is, shows a concrete example using two conflicting transactions on a simple test table, demonstrates how to detect the deadlock with InnoDB status output, and provides practical tips for preventing deadlocks in production systems.
The article details a step‑by‑step performance investigation and optimization of a Spring‑based ToB system, covering slow SQL, thread‑pool tuning, JVM memory adjustments, excessive logging, and bean‑creation overhead, ultimately improving throughput from 50 req/s to nearly the required 500 req/s.
SQLite 3.44.0 introduces enhanced aggregate functions with ORDER BY support, new scalar functions like concat(), string_agg(), expanded strftime() format specifiers, additional C APIs, stricter PRAGMA behavior, improved query planner optimizations, runtime detection of long‑double support, and default UTF‑8 handling in the Windows CLI.
This article explains the concepts, analytical methods, visualizations, and SQL implementation of user crowd analysis—including distribution, metric, drill‑down, cross, and comparative analyses—while also covering trend monitoring, TGI calculation, and handling of array‑type tags in ClickHouse and Hive.
This article explains why full‑text search is needed, how InnoDB implements it with inverted indexes, the syntax for creating and using full‑text indexes, the different query modes (natural language, boolean, query expansion), and how to delete full‑text indexes in MySQL.
The article explains MySQL’s explicit and implicit lock mechanisms—including SELECT … FOR UPDATE, metadata, row, gap, and next‑key locks—how isolation levels affect them, and offers practical guidelines such as using optimistic locking, indexing locked columns, and avoiding long transactions to prevent phantom reads, deadlocks, and performance issues.
The article recounts three real‑world pagination bugs—batch‑processing loops, misuse of MyBatis PageHelper, and nondeterministic results from identical ORDER BY values—explaining why they occur, how they manifest in production, and concrete fixes to avoid data loss or endless loops.
This article presents a collection of SQL statements—including SELECT, DELETE, DESCRIBE, and JOIN queries—targeting various tables in the idc_evaluating database, accompanied by detailed English explanations of their purpose, syntax, and result set structure.
This article explains why slow SQL queries become a major risk for service stability, outlines a step‑by‑step investigation process using EXPLAIN, and presents multiple real‑world case studies with code snippets and optimization principles to quickly locate and resolve performance bottlenecks.
This article presents a detailed reference of MySQL server configuration options (my.cnf), explaining each setting such as storage engine, networking, replication, logging, cache sizes, and performance‑related parameters to help administrators fine‑tune their databases.
MySQL 8.2.0, released on October 25, 2023, introduces audit‑log cleanup scripts, FIDO‑based authentication, named‑parameter support, numerous deprecations, firewall reload scheduling, expanded SQL syntax options, and new client and server features while dropping outdated functions and variables.
After upgrading a Doris cluster from version 1.2.3 to 2.0.2, the author runs a series of SQL benchmarks—including PK lookups, top‑client queries, distinct counts on low‑ and high‑cardinality columns, minute‑level session analysis, and full‑table deduplication—to measure execution times, revealing mixed performance gains and regressions across the seven test scenarios.
This article explains how MySQL's SELECT ... FOR UPDATE statement applies different lock types—row, table, or none—depending on whether the WHERE clause uses primary keys, unique indexes, regular indexes, range queries, non‑indexed fields, or returns no rows, illustrated with practical transaction examples.
The newsletter announces the SQLE 3.0 release with new UI and demo environments, highlights the SQLE 2.2310.0‑pre3 update, reviews technical articles on MySQL cloning, OceanBase indexing, data replication, charset issues, and connection‑pool design, recaps recent conferences, and previews upcoming OB Cloud open class events.
This article explains ten data‑analysis techniques for enhancing user experience—such as behavior tracking, A/B testing, sentiment analysis, and personalization—and provides concrete SQL code snippets that illustrate how to import, query, filter, sort, aggregate, join, update, delete, and back up data in relational databases.
This article explains the concept of database transactions, the ACID properties, how MySQL implements transactions with InnoDB, how to manage autocommit, the essential transaction control statements, a practical example using savepoints, and the four isolation levels supported by MySQL.
This guide explains why case‑sensitivity issues arise in databases, how naming conventions can prevent them, and compares the behavior of Snowflake, Databend, MySQL, and PostgreSQL for database, table, column names and string literals, complete with practical examples and a concise checklist.
This announcement details a book giveaway sponsored by Tsinghua University Press and presents a comprehensive SQL learning curriculum covering SQLite, MySQL, query fundamentals, subqueries, joins, and advanced database features, complete with micro‑lecture videos and hands‑on exercises.
The article analyzes how to improve inventory‑update transactions during massive sales periods by comparing two SQL transaction patterns, calculating their TPS under defined latency assumptions, and suggesting that placing the row lock closer to the commit yields significantly higher throughput.
This guide details essential Oracle database daily inspection commands—covering instance status, tablespace health, resource usage, performance metrics, server CPU/memory/I/O checks, and security audits—to help DBAs ensure system stability and quickly diagnose issues.
This article examines why combining ORDER BY with LIMIT in MySQL pagination can produce duplicate rows on subsequent pages, analyzes the optimizer’s priority‑queue behavior introduced in version 5.6, and presents practical solutions such as using unique sort keys, adding ID ordering, and understanding deep‑pagination limitations.
This article presents a systematic performance comparison between Doris and ClickHouse, covering data ingestion speed, SQL syntax differences, hardware impact, and detailed query benchmarks across multiple scenarios, ultimately revealing that each system excels in different use cases.
This article explains how LEFT JOIN works in SQL, clarifying the difference between ON‑clause conditions that affect the join process and WHERE‑clause filters that operate on the resulting temporary table, using multiple example queries and visual illustrations.
This article presents a typical requirement to calculate count, minimum, maximum, and average scores for three subjects in a student_score table, demonstrates how to fulfill it using raw SQL, then shows step‑by‑step implementations with Fluent MyBatis, native MyBatis XML, and MyBatis‑Plus code generation, and finally compares the three approaches highlighting their pros and cons.
This article explains how to locate duplicate rows in a table, contrasts TRUNCATE and DELETE commands, clarifies the difference between primary and candidate keys, demonstrates queries for retrieving the second highest salary, and provides a collection of typical SQL interview questions with sample code.
This article explains why a MySQL 5.7.30 upgrade with GTID enabled causes a GTID‑consistency error for CREATE TABLE ... SELECT statements, describes the underlying mechanism, provides a safe two‑step workaround, and notes that MySQL 8.0.21+ now supports atomic DDL for this operation.
This article shares a master's graduate's experience of landing a big‑data developer offer, outlines how to choose the right direction, and provides detailed interview preparation, framework specialization, and practical advice for 2024 campus recruitment in the big‑data field.
This article demonstrates how to implement a statistical query on a student_score table using three ORM frameworks—Fluent Mybatis, native Mybatis, and Mybatis‑Plus—showing code examples, configuration steps, and a side‑by‑side comparison of their ease of use and generated code.
This article explains MySQL deadlocks, describing common scenarios such as multi‑table updates, nested transactions, inconsistent index order, and concurrent index updates, provides a concrete banking transfer example, and outlines practical steps for monitoring, terminating, retrying, analyzing, and preventing deadlocks in production.
The article teaches developers essential MaxCompute data‑processing skills—from creating and naming tables, handling strings and dates, and writing basic SELECTs, joins, and aggregations, to employing advanced techniques such as temporary tables, CTEs, partitioning, and map‑join hints for efficient complex queries.
This article explains how MySQL's offline_mode variable and the pt‑kill tool can be used by DBAs to place a server in maintenance mode, instantly dropping non‑privileged client connections, preventing new ones, and providing a safe way to perform maintenance or backups.
This article details how a fast‑growing HR SaaS company analyzed MaxCompute billing spikes, identified five key cost drivers, leveraged tenant‑level Information Schema to extract task metadata, applied SQL‑based cost formulas, and implemented targeted optimizations that stabilized their cloud data‑processing expenses.
When a single MySQL table grows to millions of rows, offset‑based pagination becomes painfully slow, so this article explains why, measures the performance impact, and presents three practical sharding‑aware pagination strategies—including global query, jump‑page prohibition, and a two‑step query method—along with concrete SQL rewrites and optimization tips.
This guide presents a comprehensive set of MySQL performance‑tuning techniques—including proper use of EXPLAIN, limiting IN lists, selecting specific columns, avoiding costly ORDER BY RAND(), choosing UNION ALL over UNION, distinguishing IN from EXISTS, employing full‑text indexes, respecting left‑most prefix rules, and leveraging FORCE INDEX or STRAIGHT_JOIN—to help developers write faster, more efficient queries.
This article breaks down MySQL's complex locking mechanisms—global, table, and row locks—explaining how different SELECT statements, transaction isolation levels, and index types affect lock acquisition, and provides practical test scripts and visual examples to help developers master lock behavior.
Vivo’s big‑data team horizontally scaled its Hive Metastore by evaluating MySQL sharding (Waggle‑Dance) against a TiDB migration, ultimately adopting TiDB, which after a synchronized cut‑over delivered ~15% faster queries, 80% DDL latency reduction, linear scaling, low resource use, and valuable operational lessons.
This article explains how to create optimal MySQL indexes for a service_log table based on various search conditions, covering single‑column, composite, full‑text, prefix, and covering indexes, along with best practices, index size considerations, and practical SQL examples.
This article demonstrates how to implement a statistical query on a student_score table—calculating count, min, max, and average scores per term and subject—using Fluent Mybatis, native Mybatis, and Mybatis‑Plus, compares their code complexity, and provides code generation settings for each framework.
This article walks through a puzzling PostgreSQL self‑join query, shows how different alias placements change the execution plan, explains the meaning of InitPlan and One‑Time Filter nodes, and demonstrates their impact with concrete examples and EXPLAIN output.
This article explains how MySQL permission modifications behave at table, database, and global levels, demonstrates verification steps with Uproxy's connection pool, and clarifies why query cache settings and USE statements affect the visibility of privilege changes.
This article explains the principle behind MySQL's index‑merge optimization, demonstrates how to set up test data, walks through the three merge algorithms (intersect, union, sort‑union), provides practical SQL examples, and shares tips for tuning and avoiding common pitfalls.
Oracle 23c turns the converged database vision into reality by unifying relational, document, and graph data under a single SQL engine, offering built‑in microservice support, lock‑free reservations, immutable tables, and an integrated SQL firewall to reduce architecture complexity and development cost.
The SQLE 2.2309.0-pre4 release introduces an enterprise‑level SQL lifecycle management panel, a rule knowledge base, new TDSQL audit rules, and intelligent metadata scanning tasks, while providing links to the repository, documentation, and demo environments for both community and enterprise editions.
This article explains how to detect, analyze, and resolve slow SQL queries that threaten service stability, offering step‑by‑step guidance, EXPLAIN analysis, and real‑world case studies to help developers quickly pinpoint and fix performance bottlenecks.
The article explains how large‑offset (deep) pagination in MySQL and distributed databases degrades performance and increases memory and bandwidth usage, and describes VtDriver's streaming and SQL‑rewrite techniques along with practical recommendations such as range queries and sub‑queries to mitigate these issues.
The Taobao platform’s slow‑SQL governance team implemented a comprehensive detection and governance pipeline—combining internal slow‑log tools, database slow‑query logs, and JVM‑Sandbox instrumentation to capture full SQL details, scoring high‑risk queries by execution time, scans, and standards violations, then prioritizing remediation through health scores, branch‑diff checks, and issue tracking—significantly cutting DB‑related incidents and boosting system stability.
This article explains how to achieve high‑performance database search in PHP by optimizing schema, writing efficient SQL, using mysqli/PDO functions, employing caching tools like Memcached or Redis, and integrating full‑text engines such as Elasticsearch, with complete code examples.
This article explains how StarRocks 3.0 extends real‑time data‑warehouse capabilities to support data‑lake analysis, external catalog integration, Trino compatibility, extensive I/O optimizations, and powerful materialized‑view features that together enable a unified, cloud‑native Lakehouse solution with high performance and flexible resource isolation.
The SQLE 2.2309.0-pre3 release introduces a fast SQL audit feature, adds numerous MySQL audit rules, provides detailed community and enterprise version updates, and includes links to the repository, documentation, and previous versions for comprehensive usage and evaluation.
This article explains how JDBC operations evolve into the MyBatis persistence framework, covering JDBC's seven-step query process, MyBatis's design components, initialization workflow, builder patterns, and includes practical Java code examples for each stage.
This article walks through the creation and step‑by‑step optimization of a MySQL service_log table, covering proper data‑type selection, index usage, NULL handling, DATETIME vs TIMESTAMP choices, and floating‑point considerations to achieve a lean, high‑performance schema.
This article explains MyBatis dynamic SQL, its nine XML tags, execution principle, and how to use if, where, set, choose, trim, foreach, sql, include, and association mappings for flexible query building, batch operations, and one‑to‑many, many‑to‑one, and many‑to‑many relationships.
The Aikexing Open Source Community weekly newsletter shares curated database articles, technical insights on MySQL, OceanBase, and Redis, announces SQLE 2.2309.0‑pre2 release with Feishu integration, recaps recent events like QCon Beijing, and promotes upcoming meetups and open‑source projects.
This article explains how Alibaba's user growth platform leveraged RoaringBitmap in Hologres to accelerate massive user profiling, reducing analysis time from several minutes to around ten seconds by redesigning bitmap storage, optimizing data pipelines, and employing efficient SQL and scheduling techniques.
This article explains why an UPDATE statement that separates column assignments with AND fails in MySQL, how the misuse turns values into 0, and shows the correct comma‑separated syntax with examples and code snippets to reliably update multiple fields.
This article explains MySQL's logical architecture, query execution flow, caching mechanisms, and detailed index design techniques, providing practical tips and examples so developers can understand the underlying principles and apply effective performance optimizations in real‑world applications.
This article demonstrates how to use MySQL to create employee and performance tables, populate them with sample data, and write SQL statements that first select employees whose salaries exceed their department's average and then filter out those with non‑A performance ratings, providing a complete layoff‑target list.
This article explains how JDBC evolves into MyBatis, detailing the seven-step JDBC query process, the motivations for encapsulating JDBC, MyBatis architecture, core components, initialization mechanisms, design patterns, and code examples, while offering optimization strategies for connection handling, SQL management, and result mapping.
Manticore Search is a C++‑based open‑source search engine that builds on Sphinx, offering high‑performance full‑text search, SQL compatibility, and significant speed advantages over Elasticsearch, with detailed usage instructions and Docker deployment examples for developers.
The article explains how Hologres RoaringBitmap compresses user ID sets into efficient bitmap indexes, splits 64‑bit IDs into buckets, syncs them from MaxCompute, and enables sub‑second user portrait queries that previously took minutes, dramatically improving performance and scalability.
This article breaks down the true execution sequence of an SQL query—from FROM and JOIN through WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and finally LIMIT—explaining each stage with examples, code snippets, and visual diagrams to help developers write more efficient queries.
This comprehensive guide consolidates 43 essential MySQL interview topics—from normalization, storage engines, and indexing to transaction handling, logging, replication, and performance tuning—providing clear explanations, code examples, and diagrams to help candidates quickly master the core concepts.
This article introduces MaxCompute’s new PIVOT and UNPIVOT syntax, explains their use cases for converting rows to columns and vice versa, provides detailed syntax rules, and demonstrates practical examples with code snippets to simplify data transformation and boost developer productivity in big-data environments.
This article examines why MySQL columns are often defined as NULL, debunks common myths, and presents several technical reasons—including storage overhead, index complications, query pitfalls, and timestamp issues—that justify using NOT NULL with meaningful default values for better performance and data integrity.
With MySQL 5.7 reaching end‑of‑life, this guide explains why you should upgrade to MySQL 8.0, outlines the new SQL capabilities and tools, and compares migration options including DBaaS and switching to PostgreSQL, helping you choose the best path for your workloads.
The SQLE 2.2309.0-pre1 release introduces downloadable scan task reports, eight new MySQL audit rules, and expands MyBatis scanning to all supported data source types, while providing comprehensive project overview, full release information, and links to documentation and demos.
The IEEE Spectrum 2023 programming language ranking shows Python topping both the Spectrum and Trend dimensions, while SQL rises to the top for job relevance, and the article compares these results with other major surveys to reveal shifting development trends.
This article introduces MaxCompute’s QUALIFY clause, explains its syntax and execution order, compares it to HAVING, provides multiple code examples—including simple and complex queries—and demonstrates how QUALIFY streamlines filtering of window function results, boosting query readability and efficiency.
This guide walks backend developers through fifteen practical best‑practice tips covering table and column naming, data types, field lengths, primary keys, storage engines, null constraints, foreign keys, indexes, time and monetary fields, character sets, collations, and handling large columns to reduce maintenance costs and avoid common pitfalls.
This article presents a detailed checklist of mandatory and recommended standards for database security, design, naming, indexing, SQL usage, and operational procedures, aiming to prevent low‑level failures in high‑concurrency, large‑scale internet applications.
This article walks through the exact sequence a database follows when executing a SQL query—starting with FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and finally LIMIT—highlighting key differences from the textual order of the statement.
This article examines whether adding the always‑true condition WHERE 1=1 in MySQL queries prevents index usage, by comparing EXPLAIN plans for queries with and without the clause and proposing a cleaner MyBatis mapping approach.
This article explains key database terminology, traces the history of database technologies, compares relational, NoSQL, NewSQL, OLTP/OLAP, columnar, time‑series and graph databases, and demonstrates practical data‑structure designs such as zipper tables, bit operations, bitmaps, bloom filters, and ring queues for software development.
This guide explains MaxCompute’s IF‑ELSE syntax, its compile‑time and runtime behaviors, and provides practical code examples showing how to conditionally route SQL logic based on data characteristics, helping developers write more flexible big‑data queries.
This article demystifies the actual execution sequence of SQL queries, detailing how FROM and JOIN establish table relationships, WHERE filters rows, GROUP BY groups data, HAVING applies aggregate conditions, SELECT retrieves fields, ORDER BY sorts results, and LIMIT restricts the final output.
The article explains that MySQL implicitly converts VARCHAR values to numbers during a comparison with an integer, truncating non‑numeric strings and causing queries like "WHERE a = 0" to return rows that should not match, unlike Oracle and SQL Server which raise errors.
This article examines frequent MySQL performance problems such as inefficient LIMIT usage, implicit type conversion, sub‑query updates, mixed sorting, EXISTS clauses, condition push‑down, early range reduction, and intermediate result handling, and provides concrete SQL rewrites and execution‑plan analyses to dramatically improve query speed.
This article explains the true execution sequence of SQL statements—starting with FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and LIMIT—illustrating each step with diagrams, examples, and code snippets to clarify how databases process queries.
The SQLE 2.2308.0 release introduces intelligent SQL file scanning, per‑instance work‑order approvers, multi‑file MyBatis review in IntelliJ, zero‑cost Java probe integration, new OceanBase‑Oracle support, expanded TDSQL distributed rules, and numerous bug fixes and optimizations across both community and enterprise versions.
This article explains how to identify and remove duplicate rows in a MySQL table—keeping a single record per duplicate group—by using a self‑join DELETE statement or a nested subquery approach, complete with sample table creation and data insertion scripts.
RisingWave is an open‑source distributed streaming SQL database that uses SQL to define tables and materialized views, offering low‑latency incremental query processing, a scalable architecture with separate compute and storage, robust consistency guarantees, and real‑time analytics demonstrated through several practical use cases.
This article explains how to manage multithreaded database insert operations in Spring Boot, demonstrates why the @Transactional annotation fails in a multithreaded context, and provides a solution using manual SqlSession transaction control with code examples and test results.
This article explains why using NULL in MySQL tables hurts storage, index size, query correctness, and update performance, and shows concrete examples, benchmark data, and official documentation that justify preferring NOT NULL with sensible default values.
This article explains how to retrieve the names of students who have selected both course 1 and course 2 in a MySQL table by using a GROUP BY clause together with a HAVING condition that counts distinct course IDs, avoiding incorrect results from a simple IN filter.
This article explains how to perform relational updates in MySQL using JOINs, subqueries, and multi‑table UPDATE statements, demonstrating table creation, sample data insertion, and the differences between INNER, LEFT, and RIGHT joins for keeping dependent tables in sync.
This article explains MySQL's four transaction isolation levels—READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE—detailing their definitions, associated phenomena such as dirty reads, non‑repeatable reads, and phantom reads, and demonstrates how to query and set isolation levels with practical SQL examples.
This article explains how to split a massive MySQL table into smaller tables, use the MERGE engine to create a unified view, troubleshoot common errors, and implement efficient pagination across the split tables with example SQL and Java code.
