Tagged articles
Covering Index
48 articles · Page 1 of 1
This article analyzes why MySQL LIMIT with large offsets causes severe performance degradation, explaining the server-layer mechanics of OFFSET, comparing index-scan vs full-table-scan execution paths, and demonstrating two optimization techniques — delayed join (covering index subquery) and cursor-based pagination — with benchmark results showing 4.68s reduced to 0.21s and 0.006s respectively on a 1.28M-row table.
Covering IndexCursor PaginationDeep Pagination
0 likes · 17 min read

A step-by-step MySQL query optimization case study showing how indexing, join rewriting, composite indexes, and covering indexes reduced execution time from 30,248 seconds to 0.001 seconds with execution plan analysis.
Composite IndexCovering IndexMySQL
0 likes · 12 min read


liandkAug 23, 2026 · Databases
The article explains why MySQL back‑table lookups (回表) double I/O and degrade performance, defines covering indexes that retrieve all needed columns from a secondary index, shows how to identify back‑table cases via EXPLAIN, and provides step‑by‑step commands to create, test, and verify covering indexes for high‑frequency queries.
Covering IndexEXPLAINIndex Optimization
0 likes · 11 min read

The article explains how to make MySQL do less work by reducing scanned rows, avoiding back‑and‑forth lookups with covering indexes, and minimizing sorting or temporary tables, while detailing common index pitfalls, deep pagination tricks, COUNT usage, ORDER BY/filesort handling, JOIN strategies, and step‑by‑step methods for finding and fixing slow SQL statements.
Covering IndexEXPLAINMySQL
0 likes · 17 min read

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.
B+TreeCovering IndexDatabase Performance
0 likes · 11 min read

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.
Covering IndexEXPLAINIndex Optimization
0 likes · 31 min read

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.
Covering IndexData SkewDatabase Interview
0 likes · 15 min read

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.
Back-tableCovering IndexIndex
0 likes · 13 min read

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.
B+TreeCovering IndexEXPLAIN
0 likes · 12 min read

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.
Covering IndexDatabase PerformanceIndex Optimization
0 likes · 9 min read

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.
B+TreeCovering IndexDatabase Indexes
0 likes · 10 min read

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.
Covering IndexJava InterviewMySQL
0 likes · 17 min read

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.
Covering IndexInnoDBMySQL
0 likes · 9 min read

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.
Composite IndexCovering IndexPerformance Tuning
0 likes · 11 min read

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.
Covering IndexMySQLQuery Tuning
0 likes · 13 min read

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.
Covering IndexMySQLexecution plan
0 likes · 28 min read

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.
Covering IndexCursor PaginationDeep Pagination
0 likes · 9 min read

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.
Covering IndexCursor PaginationElasticsearch
0 likes · 9 min read

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.
Back-tableCovering IndexLeftmost Prefix
0 likes · 8 min read

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.
Back-tableCovering IndexDatabase Interview
0 likes · 8 min read

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.
Covering IndexSQLSQLite
0 likes · 5 min read

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.
B+TreeCovering IndexIndex Optimization
0 likes · 9 min read

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.
Covering IndexDeep PaginationKeyset Pagination
0 likes · 7 min read

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.
Composite IndexCovering IndexDatabase Performance
0 likes · 12 min read


ITPUBFeb 26, 2023 · Databases
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.
Covering IndexInnoDBMySQL
0 likes · 7 min read

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.
Covering IndexMySQLSQL Optimization
0 likes · 7 min read

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.
Covering IndexDatabase MigrationIndex Only Scan
0 likes · 11 min read

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.
Covering IndexEXPLAINIndex Optimization
0 likes · 22 min read

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.
Covering IndexDatabase PerformanceEXPLAIN
0 likes · 10 min read

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.
Composite IndexCovering IndexDatabase Performance
0 likes · 13 min read

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.
Covering IndexMySQLORDER BY
0 likes · 13 min read

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.
B+TreeCovering IndexKeyset Pagination
0 likes · 8 min read

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.
Composite IndexCovering IndexIndex Optimization
0 likes · 13 min read

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.
Composite IndexCovering IndexMySQL
0 likes · 3 min read

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.
Covering IndexEXPLAIN PlanIndex Optimization
0 likes · 8 min read

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.
B+TreeCovering IndexMySQL
0 likes · 14 min read

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.
B+TreeComposite IndexCovering Index
0 likes · 10 min read

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.
Covering IndexDatabase PerformanceSQL Optimization
0 likes · 10 min read

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.
Covering IndexDatabase PerformanceIndex Optimization
0 likes · 11 min read

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.
B+TreeCovering IndexMySQL
0 likes · 11 min read

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.
Bookmark ScanCovering IndexDelayed Join
0 likes · 9 min read

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.
Covering IndexDatabase IndexesInnoDB
0 likes · 10 min read


ITPUBJul 17, 2018 · Backend Development
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.
Composite IndexCovering IndexEXPLAIN
0 likes · 18 min read

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.
Covering IndexEXPLAINIndex
0 likes · 8 min read


ITPUBOct 30, 2017 · Databases
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.
Covering IndexMySQLdatabase
0 likes · 6 min read


ITPUBMar 7, 2017 · Databases
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.
Covering IndexLIMITMySQL
0 likes · 6 min read


ITPUBJun 21, 2016 · Databases
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.
Bookmark LookupCovering IndexInclude Clause
0 likes · 6 min read

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.
Covering IndexMySQLperformance
0 likes · 3 min read
