The article explains how database indexes act like a book's table of contents, reducing full‑table scans and disk I/O by using B+Tree structures, and compares clustered, non‑clustered, and covering indexes while highlighting their benefits and trade‑offs.
This guide walks through why caching alone can't solve performance bottlene bottlenecks, shows how to enable MySQL slow‑query logging in SpringBoot, analyzes slow SQL with tools like mysqldumpslow and pt‑query‑digest, explains the full EXPLAIN output, and dives into InnoDB B‑tree, clustered vs secondary indexes, covering indexes, and common causes of index loss.
This guide explains the six common reasons why MySQL indexes may fail to improve query speed, shows how interviewers evaluate index knowledge, and provides concrete SQL examples, EXPLAIN analysis, and practical optimization techniques such as redesigning indexes, using covering indexes, avoiding implicit type conversion, and tuning database configuration.
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.
This guide explains MySQL’s B+‑tree index architecture, compares clustered and secondary indexes, demonstrates covering and prefix indexes, outlines design principles such as high‑selectivity columns and the left‑most prefix rule, and shows how to use EXPLAIN and real‑world examples to diagnose and improve query performance.
This guide explains how MySQL processes queries in three index‑related stages—Index Key, Index Filter, and Table Filter—showing why keeping operations in the index layer dramatically improves performance and how to design and diagnose indexes effectively.
This guide walks developers through the fundamentals of database indexes, explaining why they matter, how they work, common pitfalls, and best‑practice design tips with concrete SQL examples and visual illustrations.
The article shares iFlytek's recent campus salary packages, then dives into a detailed Java interview guide covering Redis data types, key expiration handling, thread safety, ThreadLocal usage, MySQL covering and composite indexes, slow‑query analysis, and SQL‑injection prevention techniques.
This article explains the concept of table lookups in MySQL, shows how they arise from using secondary indexes, provides concrete query examples, and offers practical optimization techniques such as creating covering indexes, reducing selected columns, and analyzing execution plans to improve performance.
This article demystifies database indexes by explaining their purpose, inner workings, and best‑practice design, covering common pitfalls such as slow queries, over‑indexing, NULL handling, composite index ordering, covering indexes, sorting, grouping, and maintenance across different database systems.
This article presents ten practical rules for designing database indexes—covering business‑scenario analysis, the left‑most prefix principle, avoiding over‑indexing, using covering indexes, data‑type choices, function traps, prefix indexes, NULL handling, maintenance scripts, and monitoring—each illustrated with SQL examples, diagrams, and real‑world performance gains.
This article analyzes a typical Alibaba interview question that asks for the best index on a table with 27 columns for the query SELECT a, b, c FROM table WHERE d = X ORDER BY e, covering index design, field ordering, comparisons with alternative indexes, index condition pushdown, and the detailed MySQL execution process.
This article dissects the performance problems of MySQL deep pagination, explains why OFFSET‑based queries become increasingly costly, and presents six practical optimization strategies—including cursor pagination, deferred joins, covering indexes, partitioning, precomputed pages, and Elasticsearch integration—to dramatically speed up order‑lookup queries in large e‑commerce databases.
When a MySQL order table with 20 million rows is paginated using a large OFFSET, query latency can explode from seconds to minutes, but applying six concrete techniques—cursor pagination, deferred join, covering index, table partitioning, precomputed pages, and Elasticsearch integration—can shrink response time to a few hundred milliseconds.
This article explains MySQL covering indexes, how table lookups (back‑table operations) work, and the leftmost prefix rule for composite indexes, providing practical examples and tips for optimizing query performance in interview scenarios.
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.
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.
The article explains how using a large OFFSET with MySQL LIMIT forces the engine to scan and sort hundreds of thousands of index and data pages, then presents three concrete optimizations—covering index scans, subqueries, and partitioned tables—to dramatically reduce the work.
Deep pagination in MySQL becomes a performance bottleneck when tables reach millions of rows, causing excessive row scans and costly index lookups; this article explains its root causes and presents six practical optimization techniques—including keyset pagination, delayed joins, covering indexes, data partitioning, and external search engines—to dramatically improve query speed.
This article demonstrates MySQL query performance issues with large tables, shows how a subquery without indexes can take hours, and walks through step‑by‑step optimizations including adding single‑column indexes, converting to EXISTS and JOIN queries, creating composite indexes, covering indexes, and sorting improvements, achieving speedups of thousands of times.
This article explains InnoDB’s clustered and secondary index structures, why secondary index leaf nodes store primary key values, how lookups trigger “row‑lookup” (回表) operations, and how covering indexes can eliminate those extra scans, illustrated with MySQL examples.
This article demonstrates how creating a covering composite index on MySQL tables can reduce a 55‑second aggregation query on five million rows to about two seconds by eliminating the need for row lookups and fully utilizing index data.
The article examines why openGauss 2.0/3.0 cannot use Index‑Only Scan for covering indexes, explores the role of the visibility map and VACUUM, compares behavior with PostgreSQL 11, and shows how the USTORE storage engine can finally enable true covering indexes.
This article walks through ten typical scenarios where MySQL indexes fail—covering left‑most prefix violations, SELECT *, calculations, functions, type mismatches, LIKE patterns, column comparisons, OR, NOT IN/NOT EXISTS, and ORDER BY—showing why each occurs and how to keep indexes effective.
This article demonstrates how to create and use a sample employees table, explains composite index structures, and provides ten concrete MySQL index best‑practice guidelines—covering full‑value matches, leftmost‑prefix rules, avoiding functions, range conditions, covering indexes, inequality, NULL checks, leading wildcards, type conversions, and OR usage—each illustrated with EXPLAIN output and code examples.
This article explains MySQL index fundamentals, the B+Tree structure, various index types, the concept of covering indexes to avoid row look‑ups, the left‑most prefix rule, and presents three real‑world optimization cases that illustrate how to design effective composite indexes, reduce index size, and improve query performance while avoiding costly table scans and sorts.
This article explains the internal execution process of MySQL's ORDER BY, compares full‑field and rowid sorting, shows how indexes, sort_buffer size and max_length_for_sort_data affect performance, and provides practical optimization techniques such as composite and covering indexes.
This article examines why MySQL pagination with large OFFSET values becomes painfully slow, explains the underlying B+‑tree index mechanics, and presents two practical solutions—keyset pagination and index‑covering subqueries—to dramatically improve query performance.
This article explains MySQL's ORDER BY execution process, comparing full-field sorting with rowid sorting, detailing how sort_buffer, temporary files, and index usage affect performance, and showing how composite and covering indexes can eliminate sorting altogether.
This article explains why MySQL may still use a composite index as a covering or range index despite the query not following the leftmost‑prefix rule, by analyzing optimizer trace output and demonstrating the behavior with concrete experiments.
This article examines a slow MySQL query that scans millions of rows, explains why the optimizer chose a low‑selectivity index, demonstrates how forcing the correct index dramatically improves performance, and shows how to create a covering composite index to resolve the issue permanently.
This article explains the fundamental concepts and practical design of MySQL indexes, covering implementation-level index types, application-level index categories, the differences between clustered and non‑clustered indexes, B‑Tree and hash structures, covering indexes, and best‑practice strategies for optimal query performance.
This article explains how MySQL indexes work, comparing binary search trees, red‑black trees, hash tables, B‑trees, B+‑trees, and the differences between MyISAM and InnoDB implementations, and it details primary, secondary, covering, and composite index usage with practical examples.
This article explains why using SELECT * in MySQL queries degrades performance, covering increased parsing cost, unnecessary data transfer, loss of covering index optimization, and how proper column selection and index design can dramatically improve query speed.
This article explains why using SELECT * in MySQL queries reduces performance, detailing increased parsing cost, network overhead, I/O for large columns, and loss of covering index optimization, and also expands on index concepts such as composite indexes and their impact on query efficiency.
This article recounts a detailed MySQL interview, explaining index fundamentals, B‑Tree versus hash structures, clustered and covering indexes, composite indexes with left‑most prefix rules, and advanced optimizations like index condition pushdown, offering practical insights for database professionals.
This article explains why traditional MySQL pagination (ORDER BY … LIMIT N,M) becomes painfully slow on tens of millions of rows, analyzes the root causes, and presents concrete optimizations such as covering‑index delayed joins, bookmark‑based scans, and query rewrites that can cut execution time to a third of the original.
This article explains MySQL indexes—defining what they are, classifying them by structure, storage, and logic, illustrating InnoDB clustered and secondary index structures with examples, and covering advanced concepts such as covering indexes, the leftmost‑prefix rule, and index condition pushdown to improve query performance.
This guide walks through common MySQL performance pitfalls, explains how to interpret EXPLAIN output, and demonstrates step‑by‑step index creation, composite and covering indexes, as well as force‑index usage to dramatically speed up order‑related queries in high‑volume systems.
This article analyzes why two similar MySQL SELECT statements return different rows by examining index structures, covering index behavior, the optimizer's choice of secondary versus primary keys, and experimental verification using EXPLAIN statements and index modifications.
This article walks through a real MySQL case where a simple SELECT query runs slowly despite an existing index, explains why the optimizer chooses a full‑table scan, and demonstrates how adding a covering composite index reduces execution time from 260 ms to 13 ms.
This article examines the performance problems of MySQL LIMIT pagination on a table with over 800,000 rows, demonstrates timing tests for various offsets, and presents covering‑index techniques that dramatically reduce query time, including practical SQL examples.
This article explains what bookmark (key) lookups are in SQL Server, shows how they appear in execution plans, and presents three practical indexing techniques—clustered indexes, covering indexes with INCLUDE, and index intersection—to remove the extra I/O overhead.
A simple MySQL query on a million‑row picture table was sped up by adding a composite covering index, reducing average execution time from about 40 ms to 30 ms and eliminating the need for a table lookup.
