This article reviews the SQL enhancements introduced in SQLite from version 3.22.0 through 3.26.0, covering boolean literals, window functions, the FILTER clause, upsert syntax, and column‑renaming support, while comparing SQLite's capabilities to other major database engines.
The article clarifies that the number in MySQL's int(N) definition does not restrict the column's numeric range, demonstrates this with insert tests, and explains that only when combined with ZEROFILL does the display width affect how values are shown.
This article introduces Hologres Dynamic Table, explains its typical real‑time data‑warehouse scenarios, outlines the underlying architecture and its challenges, details the incremental and full‑refresh modes, provides usage steps, best‑practice recommendations, demo scenarios, and compares it with competing solutions.
The article explains why the SQL keywords IN and NOT IN should be avoided due to poor performance, index usage issues, and unexpected results with NULL values, and demonstrates safer alternatives such as EXISTS, NOT EXISTS, and JOIN with practical code examples.
This article demonstrates how to create a Python MySQLWrapper class that encapsulates connection handling, query execution, and CRUD operations, providing a modular, reusable interface for interacting with MySQL databases, complete with example code and usage instructions.
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 database connection, executes a SELECT query, processes results, and closes the connection.
This article explains how MySQL InnoDB full‑text search works, covering inverted index structures, creation and deletion of full‑text indexes, query syntax for natural language, boolean, and query‑expansion modes, relevance calculation, and practical examples with SQL code.
This article walks through a MySQL performance case study, showing how a slow sub‑query can be accelerated by adding single‑column and composite indexes, rewriting the query with EXISTS or JOIN, and applying best‑practice indexing strategies such as covering indexes and left‑most prefix rules.
This article introduces MySQL's JSON data type, explains its advantages and use cases, demonstrates how to create tables, insert JSON data, perform basic, conditional, nested, and sorted queries, modify data with JSON functions, and efficiently index JSON columns for faster retrieval.
This article provides a step‑by‑step guide to essential MySQL operations, covering connection methods, database creation and deletion, user management, password changes, data manipulation, query techniques, indexing strategies, performance tips, and backup procedures, all illustrated with complete command examples.
This article explains why the SQL keywords IN and NOT IN often lead to poor performance and unexpected results, demonstrates the problems with large tables and NULL values, and recommends using EXISTS, NOT EXISTS, or JOIN alternatives for more reliable and efficient queries.
This article explains the meaning of the number in MySQL INT(10), how it controls display width with ZEROFILL, shows practical SQL examples, and details the deprecation of integer display width starting from MySQL 8.0.17.
This article explains the role of the DWS layer in data warehouses, outlines design principles, step‑by‑step modeling, naming conventions, field design, provides concrete DDL/ETL examples, common pitfalls, and how to build reusable, performant summary tables for analytics.
Manticore Search is a high‑performance, open‑source C++ search engine that builds on Sphinx, offering real‑time indexing, SQL support, distributed search, and significant speed advantages over Elasticsearch, with simple installation via Linux packages or Docker and extensive plugin ecosystems for various applications.
This guide explains how to use mysqldump to back up a single MySQL database, multiple databases, or all databases, includes options for specific tables, compression with gzip, adding drop statements, exporting only schema, and provides restore procedures and a sample automated backup script.
SQLE 4.2502.0 has been officially released, introducing SQL compliance rewriting, a performance tracker, a syntax rule knowledge graph, new community and enterprise features, detailed upgrade instructions, and comprehensive version logs highlighting enhancements, bug fixes, and support for multiple database platforms.
This article explains typical sharding strategies—hash, range, and middle‑table—describes SDK, Proxy, and Sidecar middleware forms, and presents several pagination optimization techniques such as global query rewriting, average pagination, disabling cross‑page queries, secondary queries, and using intermediate tables to improve performance in distributed databases.
This article explains how to improve MySQL query performance by optimizing ORDER BY, GROUP BY, and LIMIT operations, covering index usage, file‑sort algorithms, covering indexes, prefix indexes, and practical code examples for large‑scale data sets.
This article explains why large SQL IN clauses cause performance issues or errors in MySQL and Oracle, and demonstrates a Spring Boot 3 solution that uses custom annotations, an AOP aspect, multithreading, and result handlers to automatically split and process the queries in batches.
The article explains how a large‑scale data‑development IDE leverages ANTLR4 to build a custom SparkSQL parser that provides real‑time syntax checking, auto‑completion, and validation by generating ASTs, using listeners for context, optimizing performance, and exploring future integration with large language models.
This guide introduces the free, open‑source DBeaver tool, explains how to download and install it, demonstrates configuring themes, creating data sources, managing tables, executing SQL, and highlights its support for many databases while noting limitations of the community edition.
This article presents a comprehensive guide to essential SQL skills for data‑analysis positions, covering join operations, difference detection, duplicate removal, missing‑value handling, multi‑condition and fuzzy filtering, aggregation, conditional calculations, trimming, cost computation, and includes a collection of practical interview questions with sample queries.
This article introduces Chat2DB, an AI‑enhanced SQL client and reporting tool, walks through its key features, Docker‑based installation, practical usage with a SpringBoot‑Vue e‑commerce project, and demonstrates how its built‑in AI can generate SQL queries automatically.
An in‑depth investigation reveals why MySQL versions prior to 5.7.31 incorrectly treat back‑ticked asterisks as column names, causing “Unknown column ‘*’” errors, and shows how systematic testing and version‑specific patches resolved the issue for DBAs and developers.
This article explains the background of tenant creation failures caused by insufficient CPU resources in OceanBase, details the calculation of usable CPU in version 4.x, shows how to query CPU usage with SQL, describes related configuration parameters, and provides step‑by‑step procedures for modifying cpu_count and testing over‑commit scenarios across different OceanBase versions.
This article explains the concept and syntax of SQL window functions, demonstrates basic and advanced use cases with step‑by‑step data preparation, queries using row_number() and lag, and provides complete scripts to solve common interview‑style ranking problems.
This article explores advanced SQL query optimization techniques for large datasets, covering sorting methods, GROUP BY improvements, pagination strategies, covering indexes, and prefix index considerations, with practical examples, EXPLAIN analyses, and recommendations to reduce filesort usage and enhance performance.
This article introduces ten common Python database interaction patterns—from basic CRUD operations and table creation to advanced transaction handling, batch inserts, and stored procedures—culminating in a complete user‑registration system example that demonstrates how to combine these techniques effectively.
This article explains how to read and parse the Undo log generated by a DELETE operation in MySQL 8.0.32 InnoDB, covering preparation, log extraction, detailed field parsing, locating the primary index record, and the step‑by‑step rollback of secondary and primary index records.
This article compiles essential MySQL interview questions covering data types, char vs varchar, TRUNCATE vs DELETE, triggers, storage engines, lock mechanisms, transactions, stored procedures, index fundamentals, creation syntax, performance impact, and practical optimization techniques with example SQL code.
This guide explains MySQL's regular‑expression capabilities—including REGEXP, NOT REGEXP, REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR and their RLIKE equivalents—covering syntax, multibyte safety, match‑type flags, and practical usage examples.
The article describes a production deadlock (ORA-000060) caused by unsorted, duplicate user_id rows in a file processed in parallel batch updates, shows MyBatis XML code, illustrates conflicting thread execution, and provides practical solutions such as sorting the input and deduplicating records.
The article explains how to design a MySQL table for product sales statistics and compares several query strategies—including loop queries, OR concatenation, mixed filtering, and finally SQL‑92 row‑by‑row comparison—to efficiently retrieve sales data for multiple business units and their dynamic product lists while respecting development constraints.
This article explains how to use PHP's mysqli_query function to connect to a MySQL database, execute SELECT queries, handle result sets, and perform other operations such as INSERT, UPDATE, and DELETE, including a complete example code snippet and best practices for error handling.
The article walks through a common database requirement—grouping rows, sorting within each group, and extracting the first (or top N) records—by preparing sample data, comparing MySQL window functions, subqueries, and JOIN solutions, adding index optimizations, and demonstrating equivalent Elasticsearch aggregations, all backed by concrete performance measurements.
The article compares Java, Scala, SQL, and Python for data‑engineering tasks, arguing that Python’s versatility, rich ecosystem, and ease of use make it the preferred language for both small‑scale and massive Spark workloads despite its performance trade‑offs.
This tutorial explains how to use Elasticsearch's built‑in SQL interface to replace complex Query DSL statements with familiar SQL syntax, covering environment setup, index creation, data insertion, and a variety of query examples such as filtering, ordering, grouping, aggregation, and date manipulation.
This article provides a comprehensive, step‑by‑step guide to essential MySQL commands—including connection, process inspection, variable queries, lock information, transaction status, engine details, character set checks, import/export, index management, table creation, stored procedures, and view creation—complete with example code snippets for each operation.
This guide explains MySQL’s error, general, binary, and slow query logs, covering default settings, how to enable or modify them, binary log formats and recovery procedures, as well as tools for analyzing slow queries, providing a comprehensive reference for database administrators.
The article recounts decades‑long Oracle DBA experiences, describing early‑era crashes, notorious ORA‑1591 and ORA‑600 errors, troubleshooting tactics, and how those hardships forged highly skilled DBAs while highlighting the evolution toward more mature database systems.
This guide explains MySQL transaction fundamentals, ACID properties, row and table locking mechanisms, and demonstrates how to implement them in Python using pymysql, including connection pooling, context managers, and a reusable SQL helper class for robust database operations.
This guide walks through verifying the MariaDB server_audit plugin, installing it via configuration files or SQL, setting audit event variables, enabling logging, and restarting the MySQL service in a Kubernetes environment to achieve comprehensive query and connection auditing.
This article explains why developers often insert the always‑true condition 1=1 in SQL, examines its potential performance and readability drawbacks, and demonstrates cleaner alternatives using MyBatis dynamic tags and Entity Framework to build conditional queries without unnecessary predicates.
This guide walks you through setting up a personal DataWorks Notebook, performing interactive SQL development with engines like MaxCompute, creating Python visualizations, building ipywidgets for dynamic queries, and leveraging the AI‑powered Copilot to rewrite, explain, and comment code, all within a unified big‑data platform.
This guide explains the exact sequence in which SQL statements are processed, offers practical tips for optimizing multi‑table joins, and provides comprehensive index recommendations and mnemonic rules to improve query performance and reduce resource consumption.
This article explains how MySQL 8.0.32's InnoDB engine generates, reads, and parses Undo logs for an UPDATE operation, and details the step‑by‑step process of locating the primary record, constructing a rollback record, and restoring both secondary and primary index entries.
MySQL 9.2.0 introduces the CREATE_SPATIAL_REFERENCE_SYSTEM privilege, JavaScript library support, enhanced SQL stored routine APIs, ENUM and SET handling, EXPLAIN FORMAT=JSON version 2, while deprecating several version‑token functions and privileges and removing keyword restrictions, providing a comprehensive overview of additions, deprecations, and removals.
Manticore Search is a high‑performance, open‑source C++ search engine that improves upon Sphinx, offering fast full‑text search, SQL compatibility, Docker deployment, and extensive performance advantages over Elasticsearch, with detailed usage examples and code snippets.
The article explains that using a 32‑bit UNSIGNED INT to store IPv4 addresses in MySQL saves space and improves index and range‑query performance, outlines the storage savings compared to VARCHAR, mentions the need for manual conversion, and provides MySQL and Java code examples for converting between string and integer representations.
This article introduces the open‑source JSQLParser library, shows how to set it up in a Spring Boot 3 project, and provides step‑by‑step code examples for simple parsing, extracting tables, retrieving WHERE clauses, building or modifying SQL statements, and handling parsing errors.
A simple typo that changed a daily interest rate from 0.00006944444 to 0.00069444444 inflated calculated interest tenfold, went unnoticed because the author self‑approved the SQL update and testing only checked syntax, and the incident underscores the need for rigorous code review, testing, and change‑management procedures for critical data.
The article investigates a MyBatis batch‑insert job that consumes excessive CPU and takes 14 minutes, explains why the foreach‑generated giant INSERT statement is inefficient, and demonstrates how switching to ExecutorType.BATCH or MyBatis dynamic‑SQL batch support reduces the runtime to under two seconds.
When a client sends an SQL statement over TCP, the parser checks syntax and semantics and builds a query tree, the optimizer evaluates possible execution plans using statistics to estimate costs and selects the most efficient one, and the executor runs the plan, using the buffer manager for reads or the transaction manager for updates while the lock manager enforces ACID properties.
This article explains how MySQL 8.0.32 InnoDB generates and stores Update undo logs, detailing the log format, the meaning of each field, how to interpret the log address, and provides concrete SQL examples and shell calculations for practical insight.
This article explains the concept of database indexes, compares various index structures such as hash tables, ordered arrays, binary trees, B‑trees and B+‑trees, details InnoDB primary, secondary, covering and composite indexes, discusses prefix indexes and order‑by optimization, and provides practical advice and SQL examples for improving MySQL query performance.
This article explains how to migrate sharded Mycat data to ActionDB with ActionOMS, addressing duplicate primary keys by modifying both source and target schemas, configuring migration tools, and completing validation and post‑migration steps.
This article presents a real‑world case study of a high‑concurrency data‑processing interface whose response time was reduced from 30 seconds to under one second by diagnosing SQL bottlenecks, applying PostgreSQL array aggregation, moving logic to the database, and introducing Caffeine caching, with detailed code examples and performance analysis.
This guide compiles the most common MySQL interview questions asked by leading Chinese tech firms, covering fundamentals, data types, storage engines, indexes, transactions, locks, views, stored procedures, triggers, and optimization, with clear explanations, diagrams, and a free downloadable resource.
This guide explains how to overcome MySQL analytical performance bottlenecks by integrating AnalyticDB MySQL with Data Transmission Service (DTS), covering architecture, resource setup, test data creation, real‑time synchronization, and high‑performance SQL queries using window functions.
To recover accidentally deleted MySQL rows, ensure binary logging is enabled and set to ROW mode, locate the appropriate binlog files, use mysqlbinlog (or tools like binlog2sql) to extract the original INSERT events within a time window, and replay or reconstruct them to restore the data.
The article details how WanJia Shuke, the tech arm of China Resources Vanguard, tackled retail system fragmentation, user‑experience degradation, complex linkages and scalability limits by migrating dozens of projects to the distributed OceanBase database, achieving up to 70% performance improvement, 25% cost reduction and streamlined operations.
This article presents ten practical MyBatis techniques—including dynamic SQL, resultMap, foreach, pagination, annotation proxies, second‑level cache, dynamic table names, custom type handlers, logging, and multi‑datasource configuration—to help Java developers write cleaner, safer, and more performant data‑access code.
This article explains how Spark ≥ 3.3 introduces Storage Partitioned Join (SPJ) to avoid costly shuffle operations when joining partitioned V2 source tables such as Apache Iceberg, detailing the required conditions, configuration settings, practical code examples, and various join scenarios including mismatched partitions and data skew.
The article shares practical methods and code examples for making SQL both readable and high‑performing in large‑scale data platforms, covering predicate push‑down with subqueries, deduplication strategies, bucket utilization, and Python‑driven job parameter handling.
This article recounts a 2012 incident where a backend API slowed dramatically after hidden BLOB columns were added, and explains why using SELECT *—which blocks index‑only scans, forces extra I/O, increases deserialization, network, and client processing costs, and hampers schema maintenance—should be avoided in favor of explicit column lists.
This guide introduces the open‑source DBeaver database manager, walks through downloading, installing, and configuring it, demonstrates its use with a SpringBoot3 + Vue e‑commerce project, and covers theme settings, data source creation, table operations, SQL editing, and its support for various database types.
This article explains how MySQL 8.0.32 InnoDB generates and stores Insert Undo logs, detailing their binary format, field meanings, example values, and how the hidden DB_ROLL_PTR field encodes the log address, with accompanying SQL and shell examples.
This article introduces RisingWave, an open‑source streaming database, and explains how its SQL‑based interface, compute‑storage separation, UDF support, and materialized views enable efficient real‑time feature engineering, state management, and diverse downstream applications, including the enhancements in RisingWave 2.0.
Discover ten powerful MyBatis techniques—from dynamic SQL and resultMap customization to batch operations, pagination, annotation‑based mappers, caching, dynamic table names, custom type handlers, logging, and multi‑datasource configuration—that can dramatically improve Java backend development efficiency and code quality.
This article explains the hidden performance costs of traditional OFFSET pagination in MySQL, analyzes why deep paging becomes a bottleneck, and presents keyset (cursor) pagination and other optimization techniques as efficient alternatives for handling massive or frequently changing datasets.
Alibaba Cloud’s Serverless Log Service (SLS) has overhauled its SQL engine with a C++‑based compute engine, SIMD acceleration, storage‑compute fusion, and optimized scheduling, delivering up to three‑fold speed gains, 50% latency reduction, and significant improvements across high‑cardinality, JSON, IP, and join queries.
The article explains how MaxCompute’s newly introduced Bloomfilter index dramatically improves emergency data tracing by cutting query time and resource consumption, replacing costly secondary indexes, reducing storage by over 45%, and providing a lightweight, high‑efficiency solution for large‑scale point‑lookup scenarios.
This article explains how to accurately calculate the real data size in OceanBase by using OCP cluster overview, system views, and SQL queries for server‑level, tenant‑level, and table‑level statistics, covering differences between V3 and V4 versions and the use of obdiag.
This article presents a step‑by‑step AI‑assisted investigation of an OceanBase performance test that triggered error 4013 due to memory limits, detailing four interactive rounds with ChatDBA, the root‑cause analysis, and practical remediation measures.
Testing shows that VARCHAR(50) and VARCHAR(500) occupy identical storage, yet while simple lookups perform similarly, sorting on the longer column triggers disk‑based mergesort and can be several times slower, demonstrating that excessive VARCHAR length harms query performance without saving space.
This article demonstrates how to use MySQL's EXPLAIN statement to examine execution plans, interpret key fields such as possible_keys, key, rows, and Extra, and apply index adjustments—including adding and forcing indexes—to improve query performance while weighing sorting costs.
This article walks through 13 practical MySQL techniques—including group_concat, char_length, locate, replace, now, various INSERT variations, SELECT FOR UPDATE, on duplicate key update, SHOW CREATE TABLE, CREATE TABLE … SELECT, EXPLAIN, SHOW PROCESSLIST, and mysqldump—providing clear examples, SQL snippets, and screenshots to help developers write more efficient and reliable queries.
This article walks through a real‑world slow‑SQL case from a logistics routing system, demonstrating how systematic formatting, layer decomposition, condition push‑down, join optimization, and validation can transform a tangled query into a clean, performant, and maintainable statement.
This article introduces an online SQL-to-ER diagram tool, explains its advantages, provides step‑by‑step usage instructions, showcases example SQL and Java code for parsing and generating ER diagrams, and discusses the underlying design‑pattern architecture such as Strategy, Factory, Dependency Injection, and Singleton.
This article explains why indexes are essential for query performance, lists the most common scenarios for creating single‑column, unique, composite, and prefix indexes, provides concrete SQL examples, and shows how to verify index usage with EXPLAIN.
This article examines how pagination queries on large MySQL tables become dramatically slower as the offset grows, presents real‑world performance measurements, and offers three practical optimization strategies—including selecting only primary keys, range‑based filtering, and using ElasticSearch—to keep query latency low.
This guide explains how to back up single or multiple MySQL databases, entire server instances, specific tables, or only schema, using mysqldump with options like -B, gzip compression, add‑drop flags, and provides restore procedures and an automated backup script.
The article explains why multi‑table JOINs in MySQL can degrade performance, readability, and index usage, and it presents optimization strategies such as query decomposition, data redundancy, wide tables, and introduces the hash join algorithm with detailed build and probe phases, including disk‑based handling.
This article presents twelve practical MyBatis‑Plus optimization techniques—including avoiding isNull checks, specifying select fields, batch operations, using EXISTS, safe ordering, type‑safe Lambda queries, handling nulls, performance tracking, enum mapping, logical deletion, optimistic locking, and increment/decrement methods—to improve code readability, maintainability, and database query performance.
This article walks through StarRocks' materialized view refresh mechanisms, explains the various refresh triggers, details the refresh workflow, and introduces new parameters that allow selective refreshing of only changed data to avoid costly full‑partition refreshes.
This article outlines fifteen typical MySQL pitfalls—including missing WHERE clauses, lack of indexes, improper NULL handling, wrong data types, deep pagination, missing EXPLAIN analysis, charset misconfiguration, SQL injection risks, transaction misuse, collation issues, overusing SELECT *, index loss, frequent schema changes, missing backups, and unarchived historical data—and provides concrete examples and best‑practice solutions to improve performance, reliability, and security.
This article explains the concept and importance of MySQL database transactions, describes the ACID properties, shows how to use transaction control statements and savepoints, and demonstrates each isolation level (read uncommitted, read committed, repeatable read, serializable) with practical SQL examples.
This article outlines fifteen frequent MySQL mistakes—from missing WHERE clauses and absent indexes to improper data types, deep pagination, SQL injection risks, and inadequate backups—providing clear examples and practical solutions to improve query performance, data integrity, and overall database reliability.
The author compares IDEA's built‑in database tool with Navicat for running massive SQL scripts, showing that IDEA consistently uses less CPU, avoids freezes, and achieves higher execution speed—even on files up to 1.83 GB—making it the preferred choice for large‑scale database migrations.
This article explains the different MySQL JOIN types—including INNER, LEFT, RIGHT, and simulated FULL OUTER joins—by creating sample order and customer tables, inserting test data, and showing the corresponding SQL statements and result sets with visual diagrams.
This article explains MySQL's various numeric, date/time, and string data types, discusses their characteristics such as signed/unsigned integers, floating‑point precision, fixed‑point accuracy, and provides practical best‑practice recommendations for choosing optimal types in database design.
This article examines how InnoDB acquires various row, gap, and next‑key locks during INSERT … ON DUPLICATE KEY UPDATE statements under REPEATABLE‑READ and READ‑COMMITTED isolation levels, illustrating the lock behavior with detailed SQL examples and performance‑schema queries.
The article argues that while solid SQL skills are essential for data development and interview preparation, investing excessive effort to master advanced SQL yields diminishing returns, especially as AI tools automate routine queries, and recommends focusing on newer, more impactful technologies instead.
The article recounts a developer’s mistake of entering an extra decimal place in a SQL update for daily interest rates, explains how the error escaped code review, testing and production validation, and shares practical lessons on precision handling, review processes, and operational safeguards.
This article examines how pagination queries on massive MySQL tables become dramatically slower as the offset grows, defines what constitutes a slow SQL, and presents three practical optimization techniques—including returning only primary keys, range filtering, and using Elasticsearch—to dramatically improve query performance.
This guide introduces StarRocks, a high‑performance MPP database, explains its architecture and typical use cases, walks through a Docker‑based quickstart, shows how to create databases and tables, load NYC crash and weather datasets via Stream Load, and demonstrates analytical SQL queries that reveal traffic‑accident patterns under different weather conditions.
This article explains the need for vector databases in the AI era, reviews PostgreSQL's extensible ecosystem, compares vector‑database options, provides step‑by‑step PgVector installation and usage, shares operational best practices, performance tuning tips, and real‑world Qunar & Tujia case studies.
This guide walks through using SQLite's C API—including opening databases, executing queries with and without callbacks, handling results, and performing insert, delete, and display operations—complete with code snippets, table schemas, and compilation instructions.
This tutorial shows how to use Elasticsearch's built‑in SQL support to run familiar MySQL‑style queries—creating an index, inserting documents, and performing SELECT, WHERE, ORDER BY, GROUP BY, LIMIT, aggregation, date and string functions—making ES data retrieval as easy as relational databases.
The article recounts how SQLite’s creator, D. Richard Hipp, learned about covering indexes from MySQL co‑founder David Axmark at a German PHP conference and, during a trans‑Atlantic Delta flight, added support that lets queries be satisfied entirely from the index, dramatically boosting performance by avoiding table reads.
