BestHub
Sign in
Tag

index

1 views collected around this technical thread.

Java Tech Enthusiast
Java Tech Enthusiast
Jun 3, 2025 · Backend Development

Common MySQL Index Failure Scenarios and How to Diagnose Them

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.

DatabaseMySQLQuery Optimization
0 likes · 8 min read
Common MySQL Index Failure Scenarios and How to Diagnose Them
Architect's Guide
Architect's Guide
May 29, 2025 · Databases

Understanding Database Indexes: How Indexes Accelerate Queries

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.

DatabaseOptimizationSQL
0 likes · 13 min read
Understanding Database Indexes: How Indexes Accelerate Queries
Full-Stack Internet Architecture
Full-Stack Internet Architecture
May 1, 2025 · Databases

Common Scenarios Where MySQL Indexes Fail

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.

Database OptimizationMySQLPerformance Tuning
0 likes · 7 min read
Common Scenarios Where MySQL Indexes Fail
Java Captain
Java Captain
Apr 24, 2025 · Databases

Why Using UUID as a Primary Key in MySQL Causes Performance Issues and How to Optimize It

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.

DatabaseMySQLOptimization
0 likes · 7 min read
Why Using UUID as a Primary Key in MySQL Causes Performance Issues and How to Optimize It
Architect's Guide
Architect's Guide
Apr 23, 2025 · Databases

Understanding MySQL Indexes: Types, B+Tree Structure, and Clustered vs. Non‑Clustered Indexes

This article explains MySQL indexes, their purpose and working principle, compares primary, ordinary, composite and full‑text indexes, describes the B+Tree storage structure versus B‑Tree, and clarifies the differences between clustered and non‑clustered (auxiliary) indexes along with their advantages and drawbacks.

B+ TreeDatabaseMySQL
0 likes · 10 min read
Understanding MySQL Indexes: Types, B+Tree Structure, and Clustered vs. Non‑Clustered Indexes
Architect's Tech Stack
Architect's Tech Stack
Apr 2, 2025 · Databases

Understanding the Impact of WHERE 1=1 on MySQL Index Usage in MyBatis Queries

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.

DatabaseMyBatisSQL
0 likes · 4 min read
Understanding the Impact of WHERE 1=1 on MySQL Index Usage in MyBatis Queries
Spring Full-Stack Practical Cases
Spring Full-Stack Practical Cases
Mar 14, 2025 · Databases

Master MySQL JSON: From Basics to Advanced Queries and Indexing

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.

DatabaseJSONMySQL
0 likes · 10 min read
Master MySQL JSON: From Basics to Advanced Queries and Indexing
Raymond Ops
Raymond Ops
Jan 23, 2025 · Databases

Mastering SQL Execution Order & Multi‑Table Join Optimization

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.

Query OptimizationSQLdatabase performance
0 likes · 8 min read
Mastering SQL Execution Order & Multi‑Table Join Optimization
macrozheng
macrozheng
Jan 22, 2025 · Databases

Do Varchar Lengths Really Impact MySQL Storage and Query Performance?

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.

Database DesignMySQLVARCHAR
0 likes · 10 min read
Do Varchar Lengths Really Impact MySQL Storage and Query Performance?
Architecture Digest
Architecture Digest
Dec 15, 2024 · Databases

Impact of VARCHAR Length on MySQL Storage and Query Performance

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.

DatabaseMySQLVARCHAR
0 likes · 10 min read
Impact of VARCHAR Length on MySQL Storage and Query Performance
Java Tech Enthusiast
Java Tech Enthusiast
Dec 13, 2024 · Databases

Impact of VARCHAR Length on MySQL Storage and Query Performance

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.

MySQLSQLVARCHAR
0 likes · 10 min read
Impact of VARCHAR Length on MySQL Storage and Query Performance
Architecture Digest
Architecture Digest
Nov 9, 2024 · Databases

MySQL Query Optimization Techniques and Common Pitfalls

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.

MySQLSQL Optimizationindex
0 likes · 11 min read
MySQL Query Optimization Techniques and Common Pitfalls
IT Services Circle
IT Services Circle
Oct 1, 2024 · Databases

Effectiveness of Adding an Index on a Status Column in a Tens‑of‑Millions Row MySQL Table

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.

DatabaseEXPLAINMySQL
0 likes · 9 min read
Effectiveness of Adding an Index on a Status Column in a Tens‑of‑Millions Row MySQL Table
Top Architect
Top Architect
Sep 26, 2024 · Databases

Performance Comparison of Auto‑Increment, UUID, and Random Keys in MySQL

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.

DatabaseMySQLUUID
0 likes · 12 min read
Performance Comparison of Auto‑Increment, UUID, and Random Keys in MySQL
Sanyou's Java Diary
Sanyou's Java Diary
Sep 9, 2024 · Databases

Master MySQL Indexes: B+Tree, Hash, Clustering & Optimization

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.

DatabaseMySQLOptimization
0 likes · 34 min read
Master MySQL Indexes: B+Tree, Hash, Clustering & Optimization
JD Tech
JD Tech
Aug 19, 2024 · Databases

Understanding MySQL Indexes: Models, Maintenance, Utilization, and Optimization

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.

B+ TreeDatabase OptimizationMySQL
0 likes · 17 min read
Understanding MySQL Indexes: Models, Maintenance, Utilization, and Optimization
JD Retail Technology
JD Retail Technology
Jul 30, 2024 · Databases

Understanding MySQL Indexes: Models, Maintenance, Utilization, and Optimization

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.

B+ TreeDatabaseMySQL
0 likes · 17 min read
Understanding MySQL Indexes: Models, Maintenance, Utilization, and Optimization
Aikesheng Open Source Community
Aikesheng Open Source Community
Jul 16, 2024 · Databases

How to Resolve MySQL "Index column size too large" (Error 1709) After Upgrading to 8.0.21

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.

Database upgradeDynamicError 1709
0 likes · 12 min read
How to Resolve MySQL "Index column size too large" (Error 1709) After Upgrading to 8.0.21