The article explains how running an UPDATE without an indexed WHERE clause in InnoDB under repeatable‑read can trigger full‑table next‑key locks that block other statements, halt the service, and how using indexed predicates, checking execution plans, enabling sql_safe_updates, or forcing an index can avoid the disaster.
This tutorial demonstrates how to sort lists with sort() and sorted(), reverse them, retrieve length, maximum, minimum, and element indices, and includes concrete code examples and their output for each operation.
This guide explains the principles behind InnoDB indexes, why secondary‑index queries may trigger back‑table lookups, and how covering indexes can eliminate the extra I/O, providing concrete SQL examples, EXPLAIN analysis, and best‑practice tips for interview preparation.
Even well‑designed indexes can become ineffective in production, leading to full‑table scans; this article systematically examines ten typical MySQL index‑failure cases—ranging from functions on indexed columns to low selectivity—and provides concrete SQL rewrites, performance comparisons, and diagnostic tools to help developers avoid and resolve these issues.
This article explains why using SELECT * does not automatically invalidate MySQL indexes, explores common scenarios that cause index loss such as left‑most prefix violations, function calls, implicit type conversions, wildcard LIKE patterns, OR/IN/NOT IN misuse, and ORDER BY pitfalls, and provides practical optimization recommendations with concrete SQL examples and verification methods.
Adding a LIMIT 1 clause to a MySQL query that fetches the latest order for a user can unexpectedly trigger a full‑table scan, slowing execution by up to 50 times; this article explains the optimizer’s mis‑choice, shows EXPLAIN analysis, and offers three practical remedies.
A real‑world MySQL incident shows that using INSERT … SELECT without proper indexing can cause full‑table scans and progressive row locks, leading to massive payment failures, but adding an index on the filter column prevents the lock and restores safe batch migration.
This guide walks you through installing the lightweight Elasticvue GUI, configuring an Elasticsearch cluster, creating and mapping an index via the built‑in REST console, adding sample documents, and browsing the index data, providing a practical alternative to the heavier Kibana tool.
This article explains how MySQL implements indexes with B+Tree structures, why they outperform full table scans, the internal layout of leaf and internal nodes, insertion and split mechanics, range‑query processing, and practical optimization tips for clustered and secondary indexes.
This article walks through common MySQL performance pitfalls such as inefficient pagination, implicit type conversion, costly UPDATE/DELETE subqueries, mixed sorting, and EXISTS clauses, and demonstrates practical rewrites—including index usage, JOIN conversion, condition push‑down, early LIMIT, and WITH‑clauses—to transform slow queries into millisecond‑level executions.
Using UUIDs as primary keys in MySQL can dramatically degrade performance on large tables due to larger index size, random inserts causing index splits, slower comparisons, and costly index refreshes on updates, but the impact can be mitigated with ordered UUIDs, binary storage, hybrid keys, or table partitioning.
This article explains the structural differences between MyISAM and InnoDB storage engines in MySQL, focusing on how each implements primary and secondary indexes using B+ trees, with code examples and diagrams to illustrate clustering versus non‑clustering index designs.
This article provides a detailed walkthrough of RocketMQ's IndexFile storage engine, covering its physical layout, index construction and query processes, performance benefits, limitations, lifecycle management, and how it compares to other messaging systems for fast key‑based message retrieval.
This article explains how using UUIDs as primary keys in MySQL can degrade index efficiency, insert and query performance, and cause costly index refreshes on updates, then presents practical techniques such as ordered UUIDs, binary storage, hybrid keys, and partitioning to mitigate these issues.
Using UUIDs as primary keys in MySQL can severely degrade performance due to larger index size, random insertion order, and costly updates, but the article explains the underlying reasons and offers practical optimizations such as ordered UUIDs, binary storage, hybrid auto‑increment keys, and table partitioning.
This comprehensive guide covers Redis data structures, MySQL covering and composite indexes, thread safety strategies, and practical SQL injection prevention techniques, providing concrete examples, configuration tips, and performance analysis for backend developers.
When MySQL executes an UPDATE or DELETE that uses a column without an index, it must scan the entire table and acquires a combination of row, gap, and intention locks, which effectively blocks all other write operations and behaves like a table‑level lock, so adding proper indexes is essential.
This article explains MySQL indexes—how they speed up queries, their types, the inner workings of B‑Tree and B+Tree structures, page storage mechanics, and the trade‑offs between clustered and secondary indexes, providing practical insights for database optimization.
This article explains MySQL's sorting mechanisms, comparing index sorting and filesort, detailing memory and disk phases, single‑row and double‑row strategies, and provides practical optimization tips such as proper index design and buffer configuration.
This article explains the concepts of driving and driven tables in MySQL JOIN queries, shows how the optimizer chooses their order based on data size, indexes and filters, provides concrete examples with EXPLAIN output, and offers practical tips to control and improve table selection for better performance.
This article explains MySQL indexes, their working principle, advantages and drawbacks, different index types, the B+Tree structure, comparisons with B‑Tree, and the differences between clustered and non‑clustered (auxiliary) indexes, providing clear examples and practical insights.
The Apache Doris 3.1 release strengthens lake‑house capabilities with major upgrades to the VARIANT data type, vertical compaction, inverted index storage, new tokenizers, enhanced materialized view support for Iceberg/Paimon/Hudi, and numerous query‑performance optimizations such as faster partition pruning and dynamic partition clipping, offering smoother handling of thousands of columns and large‑scale semi‑structured data.
This guide compiles essential MySQL interview questions covering storage engines, indexes, transactions, locks, and triggers, providing clear explanations and illustrations for both beginners and experienced candidates seeking to excel in database interviews.
This article explains ClickHouse’s core architecture—including its storage‑compute integration, MPP parallelism, columnar storage, vectorized execution, data pre‑sorting, table engines, sparse and auxiliary indexes, and the two‑stage aggregation pipeline—then walks through the exact GROUP BY execution flow for both local and distributed tables, illustrating each step with diagrams, SQL demos, and code snippets.
A production MySQL service crashed due to a BIT(1) status column being compared with a string in an indexed query, revealing how implicit type conversion and index usage can cause unexpected bugs and how removing the offending index quickly resolved the issue.
This article investigates why MySQL recommends auto‑increment primary keys over UUIDs by building three test tables, measuring insert speeds with Spring JdbcTemplate, analyzing index structures, and discussing the performance trade‑offs and drawbacks of each approach.
This article explains what MySQL indexes are, how they work, their advantages and drawbacks, the different index types, the inner workings of B+Tree structures, and the differences between clustered and non‑clustered indexes, providing practical insights for database optimization.
This article presents a series of MySQL interview questions and answers that illustrate typical situations where indexes become ineffective—such as leading wildcards in LIKE, NOT IN, NOT EXISTS, functions, inequality operators, OR conditions, mismatched ORDER BY, large result sets, and explains how to analyze execution plans and use FORCE INDEX to improve performance.
This article explains the evolution of data storage, the fundamentals of computer storage devices, and how database indexes—especially clustered and non‑clustered indexes—leverage binary search to dramatically speed up query performance while outlining their drawbacks and common SQL optimization techniques.
This article explains the fundamentals of database indexes, covering their purpose, underlying structures such as hash tables, B‑Tree and B+‑Tree, the advantages and drawbacks, and the various index types in MySQL including clustered, non‑clustered, primary, secondary, unique, prefix, and full‑text indexes.
This article walks through a mini‑project that creates a student table, adds indexes, populates it with 100 k rows, and then demonstrates six concrete scenarios—such as leading wildcards, arithmetic on indexed columns, functions, type mismatches, composite‑index misuse, and encoding differences—that cause MySQL indexes to become ineffective, showing the EXPLAIN output for each case.
This article explains various situations that cause MySQL indexes to become ineffective—such as leading wildcard LIKE queries, calculations on indexed columns, use of functions, data type mismatches, improper use of composite indexes, and character set differences—providing example SQL, execution plans, and practical testing steps.
This article explains why using UUID as a primary key in MySQL can degrade insert, update, and query performance due to large index size, unordered values, and frequent index splits, and offers optimization strategies such as ordered UUIDs, binary storage, combined auto‑increment keys, and table partitioning.
This article explains the purpose of using the always‑true condition “WHERE 1=1” in MyBatis SQL mappings, demonstrates through EXPLAIN queries that it does not prevent index usage, and recommends a cleaner MyBatis syntax that lets MySQL’s optimizer remove the redundant condition.
The article explains how an unexpected MySQL index interaction with a BIT column and implicit type conversion caused a production outage, details the investigation steps, presents the problematic SQL and table schema, and offers a quick fix by removing the offending index.
This article experimentally compares MySQL auto_increment, UUID, and random Snowflake keys by measuring insert and query speeds, analyzing InnoDB index behavior, and discussing the trade‑offs of each primary‑key strategy, ultimately showing why auto_increment generally outperforms UUID in large‑scale workloads.
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 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 experimentally investigates whether the length of VARCHAR columns (e.g., 50 vs 500) affects MySQL storage size and query performance, covering table creation, bulk data insertion, storage queries, index and full‑table scans, and explains the underlying reasons for any differences observed.
This article explains key MySQL interview topics—including covering indexes that let queries avoid table lookups, the back‑table process when secondary indexes lack needed columns, and the leftmost prefix rule for composite indexes—providing clear examples and optimization tips.
This article investigates whether the length of VARCHAR columns in MySQL affects both the physical storage size of tables and the execution speed of various queries, revealing that storage remains unchanged while long VARCHARs can dramatically slow down full‑table sorts.
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.
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 investigates whether the declared length of VARCHAR columns (e.g., VARCHAR(50) vs VARCHAR(500)) affects MySQL storage size and query performance by creating two tables, inserting one million rows, measuring disk usage, and benchmarking various SELECT and ORDER BY operations.
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 examines frequent MySQL performance problems such as inefficient LIMIT pagination, implicit type conversion, sub‑query updates, mixed sorting, misuse of EXISTS, condition push‑down limitations, early result narrowing, intermediate result push‑down, and demonstrates how rewriting queries with JOINs, derived tables, and WITH clauses can dramatically improve execution speed.
This article explains how MySQL single‑table row limits are determined by primary‑key size, InnoDB page structure, and B+‑tree fan‑out, showing that a table with 100 million rows can still query quickly when the index depth remains low.
This article investigates how many rows should be inserted per batch in MySQL, analyzes the limits of SQL statement size, buffer pool, transaction overhead, and indexes, and presents empirical tests that reveal the most efficient batch size for different data volumes.
This article investigates whether MySQL varchar column length influences storage size and query performance by creating two tables with varchar(50) and varchar(500), inserting one million rows, measuring disk usage, and benchmarking various queries, ultimately revealing that storage is identical while longer columns can degrade performance during sorted scans due to increased temporary file usage.
This article explains how adding an index to a status column in a tens‑of‑millions‑row MySQL table affects query performance, covering the basic index lookup process, extreme cases where full scans are chosen, selectivity, covering indexes, composite indexes, partitioning, and using EXPLAIN to verify execution plans.
This article evaluates MySQL's recommendation to avoid UUID or non‑sequential snowflake IDs by benchmarking three tables—auto‑increment, UUID, and random key—using Spring Boot and JdbcTemplate, then analyzes their index structures, insertion speed, and trade‑offs.
This article explains the fundamental concepts of MySQL index structures, detailing how clustered (primary) indexes store data physically in order, how non‑clustered (secondary) indexes reference primary keys, and compares their performance implications and practical usage scenarios.
This article provides a comprehensive guide to MySQL indexing, covering index types, data structures, clustering and non‑clustering indexes, hash indexes, B+Tree mechanics, index lookup, covering indexes, index push‑down, index merge, cost‑based index selection, common pitfalls, and best practices for designing effective indexes.
This article explains how MySQL uses B‑tree‑based indexes, covering balanced binary trees, B‑trees, B+‑trees, various index types, data‑type effects, and best practices for creating and using composite indexes to optimize query performance.
This article demonstrates how a non‑unique indexed column that allows NULL values behaves with the != operator in MySQL, showing why null rows are omitted from result sets, how index usage changes, and how to rewrite queries with OR or UNION to obtain correct results while preserving performance.
This article explains MySQL index fundamentals—including hash tables, ordered arrays, and B+‑tree structures—covers index maintenance, demonstrates how indexes improve query execution, discusses best practices such as left‑most prefix, covering indexes, index push‑down, and unique indexes, and provides practical tips for index selection, avoiding index loss, and using EXPLAIN for performance tuning.
This article provides a comprehensive guide to MySQL indexing, covering index data structures, maintenance mechanisms, query execution, optimization techniques such as left‑most prefix, index push‑down, unique indexes, index selection, and common pitfalls that cause index inefficiency.
This article analyzes the MySQL 1709/1071 index‑column‑size‑too‑large error that appears after upgrading from 5.6 to 8.0.21, explains its relation to compact/redundant row formats, references the underlying bug, and provides practical steps and SQL queries to detect and fix affected tables before or after the upgrade.
This article investigates MySQL's recommendation against using UUID or non‑sequential random IDs as primary keys by benchmarking auto‑increment, UUID, and Snowflake‑derived keys, analyzing their insertion speed, index behavior, and discussing the advantages and drawbacks of each approach.
This article explains why plain LIKE searches become slow on large MySQL tables, shows how prefix patterns and string functions like LOCATE, POSITION, INSTR, and FIND_IN_SET can leverage indexes, and provides a comprehensive list of thirty practical SQL optimization techniques to dramatically improve query performance.
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 explains how InnoDB stores data pages and builds B+‑tree indexes, illustrates the structure of clustered, secondary and composite indexes, and enumerates typical scenarios—such as left‑most prefix violations, LIKE patterns, and function use—that cause index loss and full‑table scans.
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 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 why slow SQL queries occur, how MySQL indexes dramatically improve query speed, and details the underlying data structures—binary tree, hash, B‑Tree, B+Tree—as well as storage engine differences and composite index rules for optimal performance.
This article explains how MySQL indexes work, compares clustered and non‑clustered indexes, outlines their advantages and disadvantages, and provides practical design guidelines to improve query performance and maintainability.
This article explains four often‑misunderstood Git internals—how the index, staging area, and --cached option are the same, how stash creates hidden commits, the variety of reference types beyond branches and tags, and why merge commits are not actually empty—using concrete commands and examples.
This article explains how to use MySQL's EXPLAIN statement to view execution plans, demonstrates creating sample order and customer tables, walks through each column of the EXPLAIN output, and shows how indexes affect query performance, providing a practical reference for database optimization.
This article explains MySQL index types, B+Tree structures, secondary indexes, and demonstrates how enabling Index Condition Pushdown (ICP) can accelerate a query on a 6‑million‑row table by roughly ninety times, providing detailed setup, commands, and performance results.
This article explains the characteristics of various MySQL join algorithms—including Simple Nested‑Loop, Index Nested‑Loop, Block Nested‑Loop, Hash Join, and Batched Key Access—demonstrates how to examine execution plans, configure optimizer settings, and apply best‑practice optimizations such as indexing, choosing the small driver table, and upgrading MySQL versions.
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 explains why indexing long character columns can bloat MySQL indexes, introduces the concept of index selectivity, shows how to calculate the best prefix length with real‑world queries, and demonstrates performance differences between short and optimal index prefixes.
This article explains how computer storage works, why database indexes dramatically speed up queries, the mechanics of binary search, the differences between clustered and non‑clustered indexes, common pitfalls of over‑indexing, and practical SQL optimization strategies to avoid full table scans and index invalidation.
This article explains SQLite's Rollback and WAL logging modes, compares their performance and concurrency characteristics, investigates a real‑world problem where duplicate IM message IDs appear after app upgrades, and presents a step‑by‑step solution using UNIQUE constraints and index checks to prevent repeated inserts.
This article examines how InnoDB processes UPDATE statements on a table with primary and secondary indexes, analyzes three scenarios to determine which index blocks are modified, explains the internal filtering steps, and validates the behavior by inspecting LSN changes of the affected blocks.
This article explains MySQL's index prefix length restrictions for BLOB/TEXT columns in versions 8.0 and 5.7, demonstrates verification experiments showing the 767‑byte and 3072‑byte limits for different InnoDB row formats, and discusses the underlying design implications.
This article explains how to set up a MySQL test table and indexes, then enumerates nine typical scenarios—such as leading‑wildcard LIKE, arithmetic or functions on indexed columns, type mismatches, composite‑index misuse, character‑set differences, and OR conditions with non‑indexed fields—that cause queries to bypass indexes and perform full table scans.
This article explains common MySQL index‑inefficiency scenarios—including left‑most prefix violations, range queries, functions, LIKE patterns, OR/IN misuse, and ORDER BY—provides concrete SQL examples, visual illustrations of B+‑tree indexing, and practical tips such as index jump scans, ICP, and query rewriting to ensure optimal index usage.
This article explains how InnoDB stores data on disk, describing tablespaces, segments, extents, pages, row formats, record headers, page directories, B+‑tree indexes, and the differences between clustered and secondary indexes, with examples and diagrams to illustrate each component.
This article analyzes MySQL's recommendation against UUIDs and non‑sequential keys by benchmarking three tables—auto‑increment, UUID, and random snowflake IDs—using a Spring Boot JdbcTemplate test, revealing that sequential primary keys provide superior insert performance and lower index fragmentation.
The article investigates why an UPDATE statement on a MySQL 5.7 InnoDB table with RR isolation experiences high lock time, demonstrates how missing indexes cause full‑table scans and blocking, and shows how adding an index and using optimizer trace can diagnose and resolve the issue.
This article explains how database indexes work, covering storage fundamentals, the role of indexes in accelerating queries, different index types such as clustered and non‑clustered, common pitfalls, and practical SQL optimization tips to improve read performance while managing write overhead.
This article explains MySQL InnoDB index fundamentals, covering index types, data structures, clustered and secondary indexes, covering indexes, index pushdown, index merge, cost‑based index selection, common index‑inefficiency scenarios, and practical guidelines for designing effective indexes.
This guide explains MySQL indexes—what they are, their data structures, advantages and drawbacks, when to create them, various index types, how to interpret EXPLAIN output, common pitfalls that cause index loss, and practical optimization techniques for queries, joins, ordering, grouping, and slow‑query logging.
This article explains MySQL index fundamentals—including classification, B+‑tree and hash structures, clustered and secondary indexes, single‑ and composite‑column indexes, covering indexes, index condition pushdown, index merge strategies, cost‑based index selection, common index‑invalidating scenarios, and practical guidelines for creating effective indexes.
This article provides a comprehensive guide to MySQL indexing, covering index types, data structures, clustering vs. non‑clustering, hash and B+Tree indexes, covering indexes, index push‑down, index merge strategies, cost‑based index selection, common pitfalls, and practical indexing principles with illustrative examples and SQL snippets.
This article provides a comprehensive 15‑question guide on MySQL indexes, covering their definition, various types across data‑structure, physical and logical dimensions, situations where indexes fail or are unsuitable, B+‑tree advantages, search process, covering indexes, left‑most prefix, index push‑down, adding indexes to huge tables, using EXPLAIN to verify index usage, hash versus B+‑tree differences, pros and cons, and the distinction between clustered and non‑clustered indexes.
This comprehensive guide explains what MySQL indexes are, details various index types—including B+‑tree, hash, full‑text, and spatial—covers when indexes become ineffective, shows how to design, use, and troubleshoot them, and provides practical steps for large‑scale index management.
This article explains MySQL index fundamentals, focusing on the leftmost prefix principle, common index failure scenarios, the impact of SELECT *, range queries, functions, LIKE patterns, OR, IN, ORDER BY, and provides practical examples and optimization tips.
This article explains MySQL index fundamentals, compares B+Tree and B-Tree structures, shows how page size and tree height affect capacity, outlines which query patterns benefit from indexes, and discusses practical limits and the adaptive hash index feature.
This article analyses how MySQL 5.7 InnoDB processes COUNT(*) versus COUNT(1), COUNT(id) and COUNT on indexed or non‑indexed columns, showing execution‑plan differences across various index configurations, explaining why count(*) can be unexpectedly slower and offering practical guidance for query optimization.
This article compares MySQL's DISTINCT and GROUP BY clauses, explaining how index availability, implicit sorting, and MySQL version differences affect their execution speed, and provides practical guidance on choosing the right clause for optimal query performance.
This article compiles fifteen typical scenarios where MySQL indexes become ineffective—covering composite index left‑most matching, SELECT *, arithmetic on indexed columns, functions, LIKE patterns, type conversion, OR/AND logic, range queries, ORDER BY, and optimizer decisions—providing example SQL, EXPLAIN output, and practical tips to help developers prevent costly full‑table scans.
This article explains how database indexes improve query speed by leveraging storage principles, binary search, and B‑tree structures, discusses clustered versus non‑clustered indexes, outlines the trade‑offs of indexing, and provides practical SQL optimization tips to avoid full table scans and index misuse.
This article explains the differences between DISTINCT and GROUP BY in MySQL, showing that with an index they have similar efficiency, while without an index DISTINCT is usually faster because GROUP BY may trigger sorting and temporary tables, and it provides syntax, examples, and recommendations.
The article analyzes why an identical MySQL query runs faster in a QA environment than in DEV, examines execution‑plan differences caused by table driving choices, optimizer settings, and unnecessary conditions, and demonstrates how rewriting the SQL and adjusting hints can achieve a stable, efficient plan.
This article explains the fundamentals of MySQL indexes, compares them to a library catalog, details various index types and their physical implementations, demonstrates how indexes dramatically speed up queries on large tables, and provides practical SQL syntax for creating and managing indexes.
This article explains the evolution of data storage, the fundamentals of computer storage devices, how database indexes function like a book's table of contents, the role of binary search, the benefits and drawbacks of indexes, and practical SQL optimization techniques to enhance query speed.
This article explains the evolution of data storage, the fundamentals of computer storage devices, how database indexes function like a book's table of contents, the role of binary search, the benefits and drawbacks of indexes, clustered versus non‑clustered indexes, and common SQL optimization techniques.
This article presents a series of MySQL 5.7.26 experiments that reveal how record locks on non‑indexed columns are applied to the primary (clustered) index under different isolation levels, how full‑table scans affect locking behavior, and why updates and inserts may or may not be blocked.
