BestHub
Sign in
Tagged articles
39 articles
Page 1 of 1
Java Architect Handbook
Java Architect Handbook
Apr 6, 2026 · Databases

Why MySQL Indexes Still Slow Queries and How to Fix Them

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.

Data SkewDatabase InterviewIndex Optimization
0 likes · 15 min read
Why MySQL Indexes Still Slow Queries and How to Fix Them
Ray's Galactic Tech
Ray's Galactic Tech
Jan 17, 2026 · Databases

Master MySQL Index Execution: From Index Key to Table Filter

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.

Database PerformanceIndex Optimizationcovering index
0 likes · 9 min read
Master MySQL Index Execution: From Index Key to Table Filter
JavaGuide
JavaGuide
Nov 20, 2025 · Backend Development

iFlytek Salary Offers Revealed + Comprehensive Java Interview Prep

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.

Java interviewSQL injectionThreadLocal
0 likes · 17 min read
iFlytek Salary Offers Revealed + Comprehensive Java Interview Prep
Senior Brother's Insights
Senior Brother's Insights
Sep 18, 2025 · Databases

Why MySQL Table Lookups Slow Queries and How to Eliminate Them

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.

InnoDBSQL PerformanceTable Lookup
0 likes · 9 min read
Why MySQL Table Lookups Slow Queries and How to Eliminate Them
IT Services Circle
IT Services Circle
Aug 26, 2025 · Databases

Mastering Database Indexes: 10 Essential Questions Every Developer Should Know

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 IndexDatabase IndexSQL Optimization
0 likes · 11 min read
Mastering Database Indexes: 10 Essential Questions Every Developer Should Know
Su San Talks Tech
Su San Talks Tech
Jun 30, 2025 · Databases

10 Proven Index Design Rules to Supercharge Database Performance

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.

Query Tuningcovering indexdatabase indexing
0 likes · 13 min read
10 Proven Index Design Rules to Supercharge Database Performance
Tech Freedom Circle
Tech Freedom Circle
Jun 6, 2025 · Databases

How to Design the Optimal Index for a 27‑Column Table Query (Alibaba Interview)

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 indexexecution planindex condition pushdown
0 likes · 28 min read
How to Design the Optimal Index for a 27‑Column Table Query (Alibaba Interview)
Su San Talks Tech
Su San Talks Tech
Mar 7, 2025 · Databases

How to Fix MySQL Deep Pagination Bottlenecks with 6 Proven Techniques

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.

ElasticsearchTable Partitioningcovering index
0 likes · 9 min read
How to Fix MySQL Deep Pagination Bottlenecks with 6 Proven Techniques
Java Tech Enthusiast
Java Tech Enthusiast
Nov 12, 2024 · Databases

The Origin Story of Covering Indexes in SQLite

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.

Database OptimizationSQLitecovering index
0 likes · 5 min read
The Origin Story of Covering Indexes in SQLite
Lobster Programming
Lobster Programming
Apr 28, 2024 · Databases

How to Solve MySQL Deep Pagination: Causes and 6 Effective Optimization Strategies

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.

Keyset Paginationcovering indexdeep pagination
0 likes · 7 min read
How to Solve MySQL Deep Pagination: Causes and 6 Effective Optimization Strategies
Top Architect
Top Architect
Mar 29, 2023 · Databases

MySQL Query Optimization and Index Tuning: From Subqueries to Joins and Composite Indexes

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 IndexDatabase PerformanceQuery Tuning
0 likes · 12 min read
MySQL Query Optimization and Index Tuning: From Subqueries to Joins and Composite Indexes
ITPUB
ITPUB
Feb 26, 2023 · Databases

Why InnoDB Stores Primary Keys in Secondary Index Leaves – A Deep Dive

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.

Clustered IndexInnoDBcovering index
0 likes · 7 min read
Why InnoDB Stores Primary Keys in Secondary Index Leaves – A Deep Dive
dbaplus Community
dbaplus Community
Nov 18, 2022 · Databases

Why openGauss Misses Index‑Only Scan and How USTORE Fixes It

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.

Index Only ScanUSTOREVacuum
0 likes · 11 min read
Why openGauss Misses Index‑Only Scan and How USTORE Fixes It
dbaplus Community
dbaplus Community
Feb 6, 2022 · Databases

10 Common MySQL Index Pitfalls and How to Avoid Them

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.

Index Optimizationcovering indexexplain
0 likes · 22 min read
10 Common MySQL Index Pitfalls and How to Avoid Them
Ops Development Stories
Ops Development Stories
Nov 9, 2021 · Databases

Master MySQL Index Optimization: 10 Practical Tips to Avoid Index Pitfalls

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.

Database PerformanceIndex Optimizationcovering index
0 likes · 10 min read
Master MySQL Index Optimization: 10 Practical Tips to Avoid Index Pitfalls
Wukong Talks Architecture
Wukong Talks Architecture
Oct 25, 2021 · Databases

MySQL Index Optimization and Code Review Practices

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.

Code reviewComposite IndexDatabase Performance
0 likes · 13 min read
MySQL Index Optimization and Code Review Practices
Full-Stack Internet Architecture
Full-Stack Internet Architecture
Aug 5, 2021 · Databases

Understanding and Optimizing ORDER BY in MySQL

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.

Order ByRowidcovering index
0 likes · 13 min read
Understanding and Optimizing ORDER BY in MySQL
NiuNiu MaTe
NiuNiu MaTe
Apr 14, 2021 · Databases

Why MySQL OFFSET Is So Slow and How to Fix It

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+TreeKeyset PaginationOFFSET
0 likes · 8 min read
Why MySQL OFFSET Is So Slow and How to Fix It
JavaEdge
JavaEdge
Apr 13, 2021 · Databases

How MySQL Executes ORDER BY: Full-Field vs RowID Sorting and Index Optimization

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 IndexIndex OptimizationOrder By
0 likes · 13 min read
How MySQL Executes ORDER BY: Full-Field vs RowID Sorting and Index Optimization
Code Ape Tech Column
Code Ape Tech Column
Jan 20, 2021 · Databases

Why Does MySQL Pick the Wrong Index? A Deep Dive into Query Optimization

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.

Database TuningExplain PlanIndex Optimization
0 likes · 8 min read
Why Does MySQL Pick the Wrong Index? A Deep Dive into Query Optimization
Senior Brother's Insights
Senior Brother's Insights
Nov 23, 2020 · Databases

Master MySQL Indexes: Types, Structures, and Optimization Strategies

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+TreeClustered IndexHash Index
0 likes · 14 min read
Master MySQL Indexes: Types, Structures, and Optimization Strategies
Open Source Tech Hub
Open Source Tech Hub
Oct 31, 2020 · Databases

Why MySQL Indexes Matter: From B‑Tree to Covering Indexes Explained

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 IndexInnoDB
0 likes · 10 min read
Why MySQL Indexes Matter: From B‑Tree to Covering Indexes Explained
macrozheng
macrozheng
Jul 14, 2020 · Databases

Why SELECT * Slows Down MySQL: Deep Dive into Indexes and Optimization

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.

Database PerformanceSQL Optimizationcovering index
0 likes · 10 min read
Why SELECT * Slows Down MySQL: Deep Dive into Indexes and Optimization
Programmer DD
Programmer DD
May 26, 2019 · Databases

How to Speed Up Large‑Scale MySQL Pagination: Practical Tips & Benchmarks

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
How to Speed Up Large‑Scale MySQL Pagination: Practical Tips & Benchmarks
Java Captain
Java Captain
Apr 12, 2019 · Databases

Understanding MySQL Indexes in InnoDB: Structures, Types, and Optimization Techniques

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.

Database IndexesInnoDBcovering index
0 likes · 10 min read
Understanding MySQL Indexes in InnoDB: Structures, Types, and Optimization Techniques
ITPUB
ITPUB
Jul 17, 2018 · Backend Development

Master MySQL Index Optimization: From Full Table Scans to Covering Indexes

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
Master MySQL Index Optimization: From Full Table Scans to Covering Indexes
ITPUB
ITPUB
Oct 30, 2017 · Databases

How to Speed Up MySQL Queries with Covering Indexes: A Step‑by‑Step Guide

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 indexdatabasemysql
0 likes · 6 min read
How to Speed Up MySQL Queries with Covering Indexes: A Step‑by‑Step Guide
ITPUB
ITPUB
Jun 21, 2016 · Databases

Eliminate Bookmark Lookups in SQL Server: Index Strategies Explained

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 LookupInclude ClauseIndex Intersection
0 likes · 6 min read
Eliminate Bookmark Lookups in SQL Server: Index Strategies Explained