BestHub
Sign in
Tagged articles
252 articles
Page 3 of 3
Aikesheng Open Source Community
Aikesheng Open Source Community
Jun 30, 2020 · Databases

MySQL View Optimization: Fixing Slow Query Caused by Character Set Conversion

This article analyzes a slow MySQL view query caused by mismatched character sets, demonstrates how the automatic charset conversion prevents index usage, and shows step‑by‑step how altering the table charset and removing the conversion restores index usage and reduces execution time from seconds to fractions of a second.

MySQLPerformance tuningSQL optimization
0 likes · 7 min read
MySQL View Optimization: Fixing Slow Query Caused by Character Set Conversion
ITPUB
ITPUB
Jun 3, 2020 · Databases

Why MySQL Ignored My Index: Charset Mismatch Triggers Full Table Scans

A MySQL join between a large and a small table ran for seconds because differing character sets forced implicit conversions that disabled the index, and the article shows how to diagnose the issue with EXPLAIN, fix it by aligning charsets, and avoid similar pitfalls.

CharsetJoinMySQL
0 likes · 7 min read
Why MySQL Ignored My Index: Charset Mismatch Triggers Full Table Scans
macrozheng
macrozheng
May 9, 2020 · Databases

Why Insert‑Into‑Select Can Lock Your MySQL Tables and How to Fix It

An engineer’s mishap with a massive ‘INSERT INTO … SELECT’ migration exposed how full‑table scans lock MySQL tables, causing payment failures, and shows that adding an index on the filter column prevents the lock and ensures safe, efficient data transfer.

INSERT INTO SELECTMySQLdatabase migration
0 likes · 7 min read
Why Insert‑Into‑Select Can Lock Your MySQL Tables and How to Fix It
Top Architect
Top Architect
Apr 30, 2020 · Databases

Why LIMIT with Large Offsets Slows MySQL Queries and How to Optimize It

The article explains why using LIMIT with a large offset in MySQL causes severe performance degradation, demonstrates the problem with real data, and shows how rewriting the query with a sub‑query that selects only primary keys dramatically reduces execution time by avoiding massive random I/O and buffer‑pool pollution.

LIMITMySQLbuffer pool
0 likes · 8 min read
Why LIMIT with Large Offsets Slows MySQL Queries and How to Optimize It
Java Captain
Java Captain
Apr 5, 2020 · Databases

Optimizing MySQL LIMIT Queries by Reducing Row Lookups

This article explains why large OFFSET values in MySQL LIMIT queries cause severe performance degradation and demonstrates how rewriting the query to fetch primary keys first and then joining reduces execution time from seconds to milliseconds, supported by buffer‑pool analysis and practical test results.

LIMITMySQLbuffer pool
0 likes · 8 min read
Optimizing MySQL LIMIT Queries by Reducing Row Lookups
Architecture Digest
Architecture Digest
Mar 12, 2020 · Databases

Understanding MySQL Indexes: Types, Implementation, and Best Practices

This article explains what MySQL indexes are, the different categories such as ordinary, unique, composite, clustered and non‑clustered, how B‑Tree, B+Tree and hash indexes are implemented in InnoDB and MyISAM, and why auto‑increment primary keys are recommended for optimal performance.

B-TreeHash IndexInnoDB
0 likes · 7 min read
Understanding MySQL Indexes: Types, Implementation, and Best Practices
Aikesheng Open Source Community
Aikesheng Open Source Community
Mar 3, 2020 · Databases

Optimizing a Slow MySQL Query with Derived Tables and UNION ALL

This article analyzes a slow MySQL aggregation query that joins a large table with a derived table built from many UNION ALL subqueries, explains the execution plan and derived‑table materialization, and presents two optimization approaches—adding taskname indexes and rewriting the query or using a temporary indexed table—to dramatically reduce execution time while preserving result semantics.

Derived TableMySQLSQL
0 likes · 11 min read
Optimizing a Slow MySQL Query with Derived Tables and UNION ALL
Aikesheng Open Source Community
Aikesheng Open Source Community
Jan 19, 2020 · Databases

MySQL Join Optimization: Understanding BNL vs NLJ and Index Issues with Character Set Mismatches

This article analyzes a slow MySQL LEFT JOIN query, explains why the optimizer chose the inefficient Block Nested Loop algorithm instead of Index Nested Loop, shows how character‑set and collation differences cause index loss, and demonstrates how converting to INNER JOIN or fixing indexes restores high performance.

BNLJOIN optimizationMySQL
0 likes · 10 min read
MySQL Join Optimization: Understanding BNL vs NLJ and Index Issues with Character Set Mismatches
Architecture Digest
Architecture Digest
Dec 6, 2019 · Databases

Understanding InnoDB Index Structures: B+ Trees, Covering Indexes, and Best Practices

This article explains how MySQL InnoDB implements indexes with B+ trees, describes primary and secondary (clustered and non‑clustered) indexes, the concepts of row lookup, covering indexes, composite indexes, the left‑most prefix rule, index push‑down, and provides practical guidelines for creating and maintaining efficient indexes.

B+TreeDatabase OptimizationInnoDB
0 likes · 10 min read
Understanding InnoDB Index Structures: B+ Trees, Covering Indexes, and Best Practices
Java Backend Technology
Java Backend Technology
Dec 1, 2019 · Databases

Why Large LIMIT Offsets Slow MySQL Queries and How to Fix Them

This article explains how using a large OFFSET in a MySQL LIMIT clause forces the server to scan hundreds of thousands of index and data pages, causing massive random I/O, and demonstrates a faster rewrite with an inner join that dramatically reduces buffer‑pool usage and execution time.

InnoDBLIMITMySQL
0 likes · 7 min read
Why Large LIMIT Offsets Slow MySQL Queries and How to Fix Them
Big Data Technology Architecture
Big Data Technology Architecture
Aug 23, 2019 · Databases

How Many Rows Can an InnoDB B+ Tree Store? Explanation, Calculations, and Practical Verification

This article explains the storage capacity of InnoDB B+‑tree indexes by detailing page size, record size, pointer calculations, tree height effects, and real‑world verification using page‑level metadata, showing that a typical B+‑tree can hold tens of millions of rows with only 1‑3 I/O operations.

B+TreeDatabaseInnoDB
0 likes · 10 min read
How Many Rows Can an InnoDB B+ Tree Store? Explanation, Calculations, and Practical Verification
Aikesheng Open Source Community
Aikesheng Open Source Community
Aug 9, 2019 · Databases

InnoDB Index Build Process and Fill Factor in MySQL 5.7+

Since MySQL 5.7 InnoDB builds secondary indexes using a bottom‑up, sorted‑index approach, the article explains the three build phases, presents a step‑by‑step B‑tree construction example with SQL code, and discusses the innodb_fill_factor setting, its impact, advantages, and drawbacks.

B+TreeDatabaseInnoDB
0 likes · 11 min read
InnoDB Index Build Process and Fill Factor in MySQL 5.7+
DataFunTalk
DataFunTalk
Jun 28, 2019 · Databases

Deep Dive into Phoenix Index Creation, Maintenance, and SQL Compilation

This article provides a detailed technical analysis of Phoenix's native index creation and maintenance mechanisms, the underlying source code for index building, the role of coprocessors, and the complete SQL compilation pipeline from parsing to execution, highlighting how hints and optimizers influence index usage.

CompilerCoprocessorDatabase
0 likes · 26 min read
Deep Dive into Phoenix Index Creation, Maintenance, and SQL Compilation
Python Crawling & Data Mining
Python Crawling & Data Mining
Jun 26, 2019 · Databases

Why Your MySQL Queries Run Slow and How to Fix Common Index Issues

This article explains why MySQL queries can become slow—covering occasional delays from dirty‑page flushing or lock contention and persistent slowness caused by missing, unused, or mis‑chosen indexes—while offering practical tips such as checking process lists, correcting index usage, and refreshing statistics.

MySQLSQLindex
0 likes · 12 min read
Why Your MySQL Queries Run Slow and How to Fix Common Index Issues
Programmer DD
Programmer DD
Nov 17, 2018 · Databases

How InnoDB Indexes Work: From Data Pages to B+ Trees

This article explains how InnoDB stores data pages, builds page directories, and uses B+‑tree indexes—including clustered, secondary, and composite indexes—to enable fast record lookup, while also covering MyISAM indexing differences and the SQL statements for creating and dropping indexes.

B+TreeInnoDBMySQL
0 likes · 27 min read
How InnoDB Indexes Work: From Data Pages to B+ Trees
Youzan Coder
Youzan Coder
Nov 16, 2018 · Databases

Optimizing Large-Scale Pagination Queries in MySQL

When paginating millions of rows in MySQL, avoid large OFFSET scans by using delayed joins or bookmark techniques that first fetch primary‑key values via covering indexes, then retrieve the needed rows, dramatically reducing I/O and query latency.

DatabaseMySQLbookmark pagination
0 likes · 10 min read
Optimizing Large-Scale Pagination Queries in MySQL
Java Backend Technology
Java Backend Technology
Oct 15, 2018 · Databases

Master MySQL Indexes and Locks: Boost Performance and Avoid Pitfalls

This article explains MySQL's index structures, how indexes accelerate queries but slow down writes, the differences between B‑tree and hash indexes, clustered versus non‑clustered indexes, the left‑most prefix rule, and provides a comprehensive overview of MySQL locking mechanisms and best practices.

DatabaseInnoDBLock
0 likes · 18 min read
Master MySQL Indexes and Locks: Boost Performance and Avoid Pitfalls
dbaplus Community
dbaplus Community
May 16, 2018 · Databases

Master MySQL: Essential Commands, Indexes, Stored Procedures & Triggers

This guide provides a comprehensive overview of MySQL fundamentals—including CRUD statements, table creation, index management, data manipulation, transaction control, stored procedures, and trigger creation—complete with syntax examples and practical tips for reliable database development.

CRUDDatabaseMySQL
0 likes · 26 min read
Master MySQL: Essential Commands, Indexes, Stored Procedures & Triggers
Java Captain
Java Captain
Apr 6, 2018 · Databases

Understanding MySQL Indexes and Using EXPLAIN for Query Optimization

This article explains how MySQL indexes work, why queries can become slow, how to interpret the EXPLAIN output—including id, select_type, table, type, possible_keys, key, key_len, ref, rows, and extra columns—and provides practical examples and optimization cases to improve query performance while balancing the cost of maintaining indexes.

Database PerformanceMySQLSQL
0 likes · 13 min read
Understanding MySQL Indexes and Using EXPLAIN for Query Optimization
Architect's Tech Stack
Architect's Tech Stack
Mar 23, 2018 · Databases

MySQL Interview Questions and Answers: ACID, Isolation Levels, Storage Engines, Indexes, Locks, and More

This article compiles essential MySQL interview topics, covering ACID transaction properties, isolation levels and their issues, storage engine differences, index types, query execution order, lock mechanisms, temporary tables, normalization, read‑write splitting, performance tuning, and recovery logs, providing concise explanations and examples for each concept.

LockMySQLSQL
0 likes · 36 min read
MySQL Interview Questions and Answers: ACID, Isolation Levels, Storage Engines, Indexes, Locks, and More
ITPUB
ITPUB
Mar 23, 2018 · Databases

Why Classic MySQL Pagination “Optimization” Often Fails and When It Actually Helps

This article investigates the classic MySQL pagination “optimization” technique, reproduces tests on a low‑end server, compares plain LIMIT queries with the sub‑query rewrite on both clustered and non‑clustered indexes, and explains why the method sometimes improves performance and sometimes adds overhead.

MySQLSQLindex
0 likes · 15 min read
Why Classic MySQL Pagination “Optimization” Often Fails and When It Actually Helps
21CTO
21CTO
Oct 20, 2017 · Databases

Master MySQL Indexes: Boost Query Performance with Smart B+Tree Strategies

This guide explains MySQL B+Tree index structures, their advantages, how to create primary, unique, ordinary, full‑text, composite and prefix indexes, and provides practical rules and tips for designing efficient indexes, query patterns, and sorting strategies to dramatically improve database performance.

B+TreeDatabaseMySQL
0 likes · 14 min read
Master MySQL Indexes: Boost Query Performance with Smart B+Tree Strategies
ITPUB
ITPUB
Sep 7, 2017 · Databases

Master MySQL Index Usage: When and How Indexes Improve Queries

This guide explains how MySQL decides whether to use an index for range queries, LIKE patterns, and ORDER BY operations, detailing key_len calculation, index key vs filter vs table filter concepts, and practical examples for BETWEEN and sorting optimization.

BETWEENMySQLOrder By
0 likes · 10 min read
Master MySQL Index Usage: When and How Indexes Improve Queries
dbaplus Community
dbaplus Community
Jun 22, 2017 · Databases

Turning a 5‑Second Oracle Pagination Query into Sub‑Second Speed

This article walks through a real‑world Oracle pagination query that took over five seconds for just fifteen rows, analyzes why ORDER BY caused the slowdown, and demonstrates a step‑by‑step rewrite using a WITH clause and index‑driven access that reduces execution time to under one second.

OracleSQLindex
0 likes · 10 min read
Turning a 5‑Second Oracle Pagination Query into Sub‑Second Speed
dbaplus Community
dbaplus Community
Nov 30, 2016 · Databases

Why Updating a View Can Kill Oracle Performance—and How to Fix It

A detailed Oracle case study shows how a seemingly harmless view modification triggered a cascade of performance regressions, how nested UNION ALLs, missing indexes, and optimizer choices prevented predicate pushdown, and the step‑by‑step rewrites—including hints, index creation, and scalar subqueries—that finally restored fast query execution.

OraclePredicate PushdownSQL
0 likes · 13 min read
Why Updating a View Can Kill Oracle Performance—and How to Fix It
dbaplus Community
dbaplus Community
Oct 25, 2016 · Databases

Master MySQL: Essential Database Operations, Table Management, and Index Optimization

This guide walks through MySQL fundamentals—including viewing, creating, and using databases, managing users and privileges, creating and altering tables, inserting, updating, deleting, and querying data, as well as index types, best‑practice tips, and execution‑plan analysis—providing concrete commands and examples for each step.

DatabaseMySQLSQL
0 likes · 14 min read
Master MySQL: Essential Database Operations, Table Management, and Index Optimization
ITPUB
ITPUB
Oct 19, 2016 · Databases

Why InnoDB Returns Rows in Primary‑Key Order When Scanning Without Indexes

This article demonstrates how InnoDB returns rows in primary‑key order during full‑table scans, while queries that use a secondary index follow the leaf‑node order of that index, and it compares performance implications of index‑only scans versus clustered‑index scans.

DatabaseInnoDBMySQL
0 likes · 12 min read
Why InnoDB Returns Rows in Primary‑Key Order When Scanning Without Indexes
21CTO
21CTO
Oct 23, 2015 · Databases

How to Efficiently Paginate 100M User IDs in MySQL

This article examines three SQL pagination strategies for a 100‑million‑row favorites table, compares their correctness and performance using EXPLAIN analysis, and demonstrates why a GROUP BY approach with proper indexing yields the most reliable and fast results.

Large DataMySQLSQL
0 likes · 5 min read
How to Efficiently Paginate 100M User IDs in MySQL
21CTO
21CTO
Sep 16, 2015 · Databases

Mastering MySQL Indexes: B‑Tree, B+Tree, and Optimization Strategies

This article examines MySQL indexing theory, covering B‑Tree and B+Tree structures, storage‑engine differences between MyISAM and InnoDB, left‑most prefix rules, index selectivity, prefix indexes, and practical optimization techniques for high‑performance query execution.

B+TreeB-TreeDatabase Optimization
0 likes · 29 min read
Mastering MySQL Indexes: B‑Tree, B+Tree, and Optimization Strategies
Efficient Ops
Efficient Ops
Aug 5, 2015 · Databases

Mastering MongoDB Explain Plans: Optimize Queries with Index Strategies

This article explains MongoDB's three explain modes, details the fields returned by queryPlanner and executionStats, shows how IndexFilters influence the optimizer, and walks through practical examples that demonstrate how proper indexing eliminates collection scans and in‑memory sorts for faster query performance.

EXPLAINMongoDBindex
0 likes · 15 min read
Mastering MongoDB Explain Plans: Optimize Queries with Index Strategies