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.
This article explains the MySQL 8.0.22 READ ONLY option for individual databases, demonstrates how to enable and verify the read‑only state with ALTER DATABASE and SHOW CREATE DATABASE commands, shows the impact on DDL/DML operations, lists usage restrictions, and notes exceptional cases and backup considerations.
Ctrip’s ticket business rebuilt its multi‑engine metrics platform by consolidating ClickHouse, Kylin, and Presto into a single StarRocks database, introducing temporary tables, materialized views, and streamlined ETL, which cut complex query times from minutes to seconds and doubled user traffic.
This guide walks you through installing the Go SQLite3 driver, opening a database connection, creating tables, inserting records, querying data, and provides a complete runnable example, helping developers integrate lightweight SQLite storage into Go applications efficiently.
This article provides a detailed tutorial on the PyMySQL library, covering installation, connection setup, cursor operations, executing queries, transaction handling, advanced features like batch processing and connection pooling, as well as best practices for error handling and performance optimization in Python‑MySQL applications.
The article reviews Flink 1.19’s new features, highlighting SQL capability enhancements such as custom source parallelism, TTL hints, and MiniBatch support for regular joins, as well as runtime dynamic parallelism for batch jobs and flexible checkpointing intervals for different data sources.
This guide walks you through creating a Spring Boot project, integrating the Druid connection‑pool, configuring its WebStatFilter and StatViewServlet for full SQL monitoring, testing the setup, and removing the built‑in advertisement via a custom filter.
This article enumerates the most frequent situations that cause MySQL indexes to become ineffective—such as violating the left‑most rule, using functions or expressions on indexed columns, type conversions, improper LIKE patterns, OR/IN/NOT IN usage, and inequality operators—and demonstrates how to reproduce and verify each case with sample data and SQL scripts.
The article introduces MySQL and PostgreSQL, compares their performance through a benchmark on identical hardware, discusses each system’s strengths, weaknesses, and suitable application scenarios, and provides guidance on choosing the appropriate database for different workloads.
The Apache Doris 2.1.0 release introduces over 100% query performance improvements on TPC‑DS, up to 230% gains on ARM platforms, new Variant and IP data types, async materialized views, auto‑increment columns, auto‑partitioning, group commit, hardened workload groups, TopSQL monitoring, a built‑in job scheduler, and several behavior changes, all aimed at delivering faster, more flexible and more reliable OLAP processing.
The article argues that focusing solely on benchmark speed misleads database choice, emphasizing that true performance must be measured by end‑to‑end user experience, ecosystem, maintainability, and future evolution rather than raw query latency alone.
This article explains why using IN and NOT IN in SQL can lead to poor performance and incorrect results, demonstrates common pitfalls with examples, and shows how to replace them with EXISTS, NOT EXISTS, or JOIN constructs for reliable and faster queries.
DBOS, a database‑driven cloud‑native operating system founded by Michael Stonebraker and Matei Zaharia, aims to replace traditional OS state management with SQL‑based databases, offering enhanced security, rapid ransomware recovery, and seamless integration with Kubernetes and Firecracker hypervisors.
This article explains why the SQL keywords IN and NOT IN often lead to poor performance and subtle bugs, demonstrates real‑world examples with large tables and NULL values, and recommends using EXISTS, NOT EXISTS, or JOINs as more reliable alternatives.
This article explains why slow SQL becomes a critical risk during large‑scale promotions, outlines a three‑step method—observing the query, analyzing it with EXPLAIN, and designing fixes—then demonstrates each step with concrete MySQL examples, tips, and overall optimization principles.
This guide explains why a remote PostgreSQL connection may fail with a password authentication error, analyzes common causes such as wrong credentials or misconfigured files, and provides step‑by‑step solutions including password reset, configuration checks, and trust authentication setup.
This article provides an in‑depth comparison of the open‑source SQL quality management platforms SQLE and Yearning, covering their architecture, supported data sources, UI design, SQL workbench capabilities, user management, ticket workflow, system settings, and overall suitability for different database environments.
This article explains how to construct effective SQL prompts by combining personal SQL expertise with large language model capabilities, introduces the open‑source sql‑translator tool for bidirectional SQL‑natural language conversion, and discusses practical productization scenarios.
The article explains the practical reasons for adding the always‑true condition “WHERE 1=1” in SQL, covering its role in preventing syntax errors, facilitating dynamic query building, copying tables, and its historical performance impact on MySQL.
This article explores SQLite’s 2018 SQL feature updates—from boolean literals and truth testing to window functions, FILTER clauses, upsert syntax, column renaming, and related API changes—highlighting syntax details, limitations, and practical examples for developers seeking to leverage these modern capabilities in production.
This guide walks you through using StarRocks—covering effortless expression‑based partitioning, streamlined data loading with INSERT FROM FILES and PIPE, powerful in‑flight data transformation using SELECT/JOIN/UNNEST, and flexible table structure tweaks via ALTER TABLE to boost query performance.
This article explains MySQL recursive queries using WITH RECURSIVE, detailing their structure, execution process, common use cases, and provides a step‑by‑step example with table creation, data insertion, and a paginated hierarchical query.
This article provides a comprehensive overview of essential MySQL interview topics, covering join types, differences among DROP, DELETE, TRUNCATE, UNION vs UNION ALL, CHAR vs VARCHAR, transaction properties and isolation levels, database normalization, index types and usage, query execution flow, storage engine differences, and practical SQL optimization techniques.
This article introduces the open‑source database design tool Chiner, compares it with PowerDesigner, and provides step‑by‑step guidance on installation, core concepts, table management, relationship diagram creation, and import/export features for efficient database modeling.
This article explains how applying machine learning to database operations—specifically AIOps for MySQL—can automate index recommendation by parsing SQL, extracting semantic and statistical features, generating candidate index combinations, and training an XGBoost model to predict optimal indexes, reducing reliance on manual DBA work.
Because IN and NOT IN often bypass indexes on large tables, can yield incorrect results when column names differ or NULL values are present, and may cause severe performance penalties, they should be replaced with EXISTS/NOT EXISTS subqueries or appropriate JOINs, reserving IN only for small literal lists.
This guide recommends a four‑book sequence—SQL Data Analysis, Python Data Analysis, Python Report Automation, and Python Statistical Analysis—to help aspiring data analysts build essential SQL skills, master Python data manipulation, automate reporting, and grasp the theoretical foundations of statistics.
The article explains that using IN and NOT IN in SQL can cause severe performance degradation and produce incorrect results, especially with large tables, missing indexes, or NULL values, and recommends using EXISTS, NOT EXISTS, or JOINs as safer alternatives.
The article presents a cloud‑native distributed Data Warebase that unifies relational, NoSQL, search and analytical capabilities into a single horizontally‑scalable system, reducing data‑access barriers and improving developer productivity while addressing consistency, latency, cost and operational complexity.
This guide introduces MyCLI, a Python‑driven interactive command‑line client for databases, covering its key features, installation methods, configuration steps, connection commands, query execution, syntax‑highlighting options, and practical usage examples.
This article analyzes a slow Join query on OceanBase 3.2.3.3, examines execution plans and table statistics, proposes two rewrite methods—adding a filter condition and using a hash‑join hint—and demonstrates how these changes reduce execution time from over 500 seconds to just a few seconds.
This article explains how MySQL InnoDB implements full‑text search, covering the underlying inverted index structures, how to create and drop full‑text indexes, the MATCH…AGAINST syntax, and detailed examples of natural language, boolean, and query‑expansion search modes with practical SQL demos.
This article recounts how a production database deadlock was uncovered during testing, explains the use of the EasyBI monitoring tool to collect and visualize error and claim statistics, and shares the step‑by‑step configuration, analysis, and lessons learned for preventing similar issues in future systems.
This article explains why new data systems are needed, introduces Apache Arrow and its columnar in‑memory format, describes read‑time modeling and dynamic schema handling, and shows how Arrow can be used to build a complete data processing pipeline with indexing, SQL planning, and zero‑copy data exchange.
This article explains MySQL 8.3’s new ability to tag GTIDs, describes the original and extended GTID formats, demonstrates how to enable and use tagged GTIDs with mysql-shell, and shows how tagged and non-tagged transactions appear in replication logs.
Chat2DB is a free open‑source multi‑database client that adds AI‑driven natural‑language‑to‑SQL conversion, optimization suggestions, and team collaboration features, offering Windows, macOS, Linux, and web deployments along with Docker installation and detailed usage examples.
An in‑depth comparison of MySQL and PostgreSQL covers their histories, architectural differences, benchmark results showing PostgreSQL’s superior SELECT, INSERT, and UPDATE throughput, and guidance on selecting the appropriate database based on application complexity, scale, and performance requirements.
After a weekend support ticket led to a reckless UPDATE that erased all orders in a production PostgreSQL database, the author details the rapid recovery steps, analyzes the human errors behind the disaster, draws lessons from Chernobyl, and outlines concrete post‑mortem improvements to prevent future data loss.
This article explains why using OFFSET and LIMIT for pagination becomes a performance bottleneck on massive tables, illustrates the cost of full‑table scans, and introduces a cursor‑based pagination technique that leverages primary‑key ordering for efficient data retrieval.
This article explores practical techniques for mitigating data skew in massive SQL join operations, detailing MapJoin, handling special/empty values, hotspot dispersion, SkewJoin, and the novel DistMapJoin approach, complete with code snippets and performance results from Alibaba's payment data pipeline.
This guide explains MySQL's native JSON data type, its storage format, advantages over text columns, and provides step‑by‑step examples for creating tables, inserting JSON, querying with path operators, using built‑in functions, and handling conditional searches.
This guide explains step‑by‑step how to manually set up fine‑grained disaster recovery for GaussDB(DWS) in a cloud‑based dual‑cluster environment, covering preparation, configuration files, backup/restore operations, verification, and removal to achieve reliable, cost‑effective data protection.
This article compares MySQL and PostgreSQL, covering their histories, architecture differences, benchmark results, suitable application scenarios, and a concise summary of PostgreSQL's advantages and disadvantages relative to MySQL.
This guide explains how to implement a searchable user‑story interface using PostgreSQL tables with btree and gin indexes, Java record models, Spring Boot data access, and REST controllers to support range and full‑text queries efficiently.
This article explains how to use PHP's mysqli_query function to perform MySQL operations such as SELECT, INSERT, UPDATE, and DELETE, providing a step‑by‑step example that creates a connection, runs a SELECT query, processes results, and handles errors and cleanup.
This article explains why setting NULL as a column's default in MySQL can lead to index inefficiencies, unpredictable query results, and performance issues, and demonstrates proper handling of NULL with examples, operators, functions, and indexing considerations.
This article explains why using OFFSET and LIMIT for pagination on large tables leads to costly full‑table scans and poor performance, and introduces keyset (cursor) pagination as a faster, index‑driven alternative while discussing its requirements and trade‑offs.
This article explains why defining columns with a default NULL in MySQL is misleading, demonstrates how NULL interacts with indexes, operators, aggregate functions, and sorting, and provides practical examples and recommendations to avoid performance and correctness issues.
This article explains MyBatis caching mechanisms, detailing the scope, implementation, configuration, and differences between first-level (SqlSession) and second-level (mapper) caches, and how they improve query performance while outlining their default behaviors and usage considerations.
This article explains why hash joins usually outperform nested‑loop joins, how to force hash joins in SQL, the data‑type restrictions that prevent hash joins, and practical tips for optimizing join performance in TD and Oracle compatibility modes.
This article reviews SQLite’s 2018 SQL enhancements—including Boolean literals, window functions, the FILTER clause, upsert syntax, and column‑renaming—explaining each feature, showing concrete code examples, comparing behavior with other databases, and noting remaining limitations.
This article explains how MySQL InnoDB implements full‑text search using inverted indexes, covers the creation and usage of FULLTEXT indexes, demonstrates various MATCH…AGAINST query modes such as natural language, boolean, and query expansion, and shows how to manage and delete full‑text indexes with practical SQL examples.
This article explains why a SQL query tool is essential for on‑premise systems, describes the challenges of remote access and data safety, and provides a step‑by‑step implementation covering frontend encryption, backend whitelist checks, decryption, and query execution with code examples.
This guide shows how to use Zabbix to monitor database SQL results, binlog health, master‑slave replication, and how to clean up old Zabbix history with practical Bash scripts for MySQL/GreatDB and ClickHouse.
iQIYI's Magic Mirror platform, evolving from 1.0 to 3.0, addresses the growing data analysis demands of the internet industry by empowering self‑service analytics, introducing multi‑stage architectures, advanced computation engines, customizable SQL, and visual dashboards, thereby improving efficiency, scalability, and data security for business users.
This tutorial explains how to use PyCharm’s integrated database tool to create, configure, test, and manage a MySQL connection, including driver installation, schema selection, SQL execution, and removal of connections, with detailed visual instructions.
An ultra‑large hedge fund with over $10 billion AUM replaced ksqlDB and Flink with RisingWave, leveraging its PostgreSQL‑compatible streaming SQL to achieve sub‑10 ms latency, lower learning and operational costs, rich connectors, advanced operators, and comprehensive observability for real‑time trade data processing.
This article experimentally compares the efficiency of using IN clauses versus JOIN operations in MySQL by testing small, medium, and large datasets, measuring query costs, execution times, and highlighting the scalability limits of IN statements.
This guide explains MySQL’s most useful date and time functions—including CURDATE(), NOW(), DATE_FORMAT(), DATEDIFF(), DATE_ADD(), DATE_SUB(), and DATE()—with clear syntax examples and sample results, helping developers manipulate temporal data efficiently in their databases.
This guide walks you through the most commonly used MySQL date functions—CURDATE, NOW, DATE_FORMAT, DATEDIFF, DATE_ADD, DATE_SUB, and DATE—explaining their purpose, syntax, and example results so you can handle date and time data efficiently in your databases.
This article explains how to efficiently insert massive amounts of data (over 100,000 rows) into a MySQL table by comparing plain JDBC single‑row inserts, JDBC batch inserts, MyBatis/MyBatis‑Plus foreach dynamic SQL, and the built‑in MyBatis‑Plus saveBatch method, while highlighting configuration tips and performance results.
Easy-Query is a Java ORM framework offering strong-typed, database-agnostic CRUD operations, complex SQL generation, multi-table joins, group awareness, and extensive function support, demonstrated through numerous code examples covering queries, joins, subqueries, pagination, and advanced features like enum handling and anonymous type alternatives.
This article explains how to incorporate the SQLE SQL auditing tool into a DevOps workflow using GitLab, GitLab‑Runner, and Docker, detailing environment setup, CI/CD configuration, and automated scanning to ensure compliance and reduce production risks.
This article explains the complete lifecycle of a MySQL SELECT statement, covering the server and storage‑engine layers, the roles of the connector, query cache, parser, optimizer and executor, and provides concrete command‑line and SQL examples.
This article explains how InnoDB determines transaction types based on the first SQL statement, describes the internal state changes when a transaction starts, and details the identification, ID allocation, and handling of read, read‑only, read‑write, and internal transactions in MySQL 8.0.32.
This article explains why database indexes often become ineffective—covering fuzzy searches, type mismatches, function use, NULL values, arithmetic operations, leftmost column rules, and full‑scan decisions—and provides practical guidelines for designing useful indexes.
An in‑depth tutorial walks through the five core SQL language families—DDL, DML, DQL, DCL, and TCL—explaining their purposes, key commands, and practical examples, while highlighting differences and usage tips for database schema definition, data manipulation, querying, permission control, and transaction management.
This guide demonstrates how Elasticsearch 8.11's new ES|QL query language matches the traditional DSL syntax, covering basic searches, sorting, wildcard matching, aggregations, REST API nuances, and practical examples that show equivalent DSL and ES|QL statements with visual results.
This article walks through the challenges of environment‑specific data isolation in a Java project, explains why adding an env column to every table is impractical, and presents a clean solution using a MyBatis interceptor that rewrites SQL and custom annotations to control filtering, ensuring safe, maintainable code across pre‑release, gray, and production environments.
This article explains essential SQL concepts and common pitfalls—such as overusing SELECT *, choosing appropriate data types, respecting column order, handling NULL differences, leveraging database processing, using connection pools, and applying batch operations—to help developers write efficient, cost‑effective queries and advance their careers.
This guide explains MySQL stored procedure flow‑control constructs—including IF, CASE, LOOP, WHILE, REPEAT, LEAVE and ITERATE—detailing their syntax, usage patterns, and practical examples such as salary adjustments and conditional logic, helping developers implement complex database operations efficiently.
This article explains MySQL event scheduler fundamentals, including enabling/disabling the scheduler, creating, altering, and dropping events, scheduling syntax, and practical examples such as generating real-time sales data and periodic statistics, providing code snippets and best‑practice guidance for database automation.
This article demonstrates how MySQL silently discards trailing spaces in VARCHAR columns during queries, shows example tables and queries where spaces are ignored, and explains how to use the BINARY keyword to enforce strict matching of trailing spaces.
This article provides a detailed walkthrough of using the SQLE platform to create users, configure data sources, submit SQL tickets, perform reviews, and execute releases, illustrating each step with screenshots and practical tips for effective database change management.
This article examines the many syntactic forms of MySQL's BEGIN statement, explains which variations are valid or cause syntax errors, and details how BEGIN commits any existing transaction and prepares a new one without immediately starting it, all based on MySQL 8.0.32 InnoDB source code.
This tutorial demonstrates how to set up a PHP script that connects to a MySQL database, executes a SELECT query, stores the result rows in an array, and displays the data in an HTML table using loops such as while and foreach.
The article explains how the data team adopted the open‑source machine‑learning platform MindsDB to simplify data integration, enable SQL‑based model training and inference, manage model versions, and dramatically shorten recommendation system development cycles, achieving up to 30% efficiency gains.
This guide walks you through a 5‑minute challenge that shows how to enable Hologres real‑time lakehouse capabilities, configure OSS, DLF, and Hologres services, create external and internal tables, run TPCH Q11 queries, and submit results for prizes.
