BestHub
Sign in
Tagged articles

index design

41 articles · Page 1 of 1
Cloud Architecture
Cloud Architecture
Jul 12, 2026 · Databases

Database Performance Optimization: 100× Speed Gains Without Changing SQL

Even without rewriting any SQL, database performance can improve up to a hundredfold by first diagnosing bottlenecks, reducing unnecessary traffic, layering read paths, optimizing indexes, tuning connection pools, and progressively evolving from a single‑node setup to read‑write separation, sharding, and distributed read models.

Connection PoolMySQLRead-Write Separation
0 likes · 38 min read
Database Performance Optimization: 100× Speed Gains Without Changing SQL
Cloud Architecture
Cloud Architecture
May 11, 2026 · Operations

3 Billion Products, 500 Million Daily Queries: How I Boosted a Failing Elasticsearch Cluster 5×

A large‑scale e‑commerce search system handling 3 billion product documents and 500 million daily queries was on the brink of collapse, but a systematic overhaul across query modeling, index design, write pipelines, cluster topology, and application‑level governance lifted throughput five‑fold while cutting P99 latency from 3.2 seconds to 180 ms.

ElasticsearchQuery OptimizationScaling
0 likes · 30 min read
3 Billion Products, 500 Million Daily Queries: How I Boosted a Failing Elasticsearch Cluster 5×
Cloud Architecture
Cloud Architecture
Apr 16, 2026 · Databases

EXPLAIN ANALYZE: The CT Scan That Triggers a Query Performance Revolution

When a core order‑query suddenly slowed from milliseconds to seconds, the team discovered that using PostgreSQL’s EXPLAIN ANALYZE revealed hidden plan regressions, outdated statistics, and index misuse, leading to a systematic, production‑grade methodology for diagnosing and fixing query performance issues.

EXPLAIN ANALYZEPostgreSQLQuery Optimization
0 likes · 32 min read
EXPLAIN ANALYZE: The CT Scan That Triggers a Query Performance Revolution
Architect-Kip
Architect-Kip
Mar 4, 2026 · Databases

Master MySQL Naming, Field, and Index Standards for Scalable Databases

This guide presents a comprehensive set of mandatory and recommended conventions for MySQL database objects—including table, column, and index naming, field design rules, index selection principles, and best‑practice development tips—to help engineers build maintainable, high‑performance schemas.

MySQLdatabase best practicesindex design
0 likes · 22 min read
Master MySQL Naming, Field, and Index Standards for Scalable Databases
Rare Earth Juejin Tech Community
Rare Earth Juejin Tech Community
Nov 19, 2025 · Backend Development

Master Elasticsearch: Index Design, Field Types, and Cluster Management Tips

An experienced engineer shares practical Elasticsearch insights covering index design with aliases and routing, field type choices, query optimization techniques, pagination strategies, real‑time refresh settings, memory limits, and cluster management, offering concrete examples and actionable recommendations for robust search implementations.

Cluster ManagementElasticsearchField Types
0 likes · 12 min read
Master Elasticsearch: Index Design, Field Types, and Cluster Management Tips
Laravel Tech Community
Laravel Tech Community
Aug 16, 2025 · Databases

Unlock 10× Faster Queries: The Database Performance Optimization Pyramid Explained

This article presents a comprehensive, layered approach to database performance optimization—covering SQL tuning, index design, configuration parameters, hardware choices, and architectural strategies—complete with practical code examples, benchmark results, and actionable checklists for MySQL environments.

Hardware TuningMySQL PerformanceSQL Tuning
0 likes · 13 min read
Unlock 10× Faster Queries: The Database Performance Optimization Pyramid Explained
MaGe Linux Operations
MaGe Linux Operations
Aug 15, 2025 · Databases

Master Database Performance: From SQL Tuning to Hardware Optimization

This comprehensive guide walks you through database performance optimization—from identifying bottlenecks and tuning SQL queries, indexes, and configuration parameters, to selecting storage, memory, and CPU resources, applying architectural patterns, and using monitoring tools—to dramatically boost throughput and reduce latency.

Hardware TuningSQL Tuningdatabase optimization
0 likes · 22 min read
Master Database Performance: From SQL Tuning to Hardware Optimization
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 IndexMySQLQuery Optimization
0 likes · 28 min read
How to Design the Optimal Index for a 27‑Column Table Query (Alibaba Interview)
dbaplus Community
dbaplus Community
May 13, 2025 · Databases

Master Elasticsearch Index Design: From Mapping to Sharding Best Practices

This article provides a comprehensive guide to Elasticsearch index architecture, covering fundamental concepts, index mapping, field types, alias usage, shard and replica strategies, shard planning, resource impact, and practical recommendations for optimizing performance and stability in production environments.

ElasticsearchShardingbackend
0 likes · 21 min read
Master Elasticsearch Index Design: From Mapping to Sharding Best Practices
Architect
Architect
Apr 23, 2025 · Databases

Mastering Elasticsearch Index Design: From Basics to Shard Planning

This article provides a comprehensive guide to Elasticsearch index concepts, covering index definitions, alias usage, mapping and field types, shard architecture, and practical recommendations for planning shards and optimizing resource consumption to ensure stable and efficient ES clusters.

ElasticsearchShardingindex design
0 likes · 23 min read
Mastering Elasticsearch Index Design: From Basics to Shard Planning
DeWu Technology
DeWu Technology
Apr 9, 2025 · Databases

Understanding Elasticsearch Indexes, Mappings, and Shard Architecture

The article explains Elasticsearch indexes as logical namespaces for JSON documents, details how mappings define immutable field types such as text, keyword, and numeric, describes primary and replica shard roles, and offers practical guidance on alias usage, shard sizing, replica settings, and performance optimizations to ensure cluster stability.

ElasticsearchShardingindex design
0 likes · 20 min read
Understanding Elasticsearch Indexes, Mappings, and Shard Architecture
dbaplus Community
dbaplus Community
Dec 27, 2023 · Backend Development

Boost Elasticsearch Performance: 24 Proven Tips and Pitfalls to Avoid

This article compiles practical Elasticsearch recommendations—covering query caching, aggregation design, pagination strategies, scripting, mapping choices, shard and replica settings, and bulk indexing—to help engineers write faster, more reliable search queries while avoiding common performance traps.

Query Tuningindex designperformance optimization
0 likes · 26 min read
Boost Elasticsearch Performance: 24 Proven Tips and Pitfalls to Avoid
php Courses
php Courses
Aug 14, 2023 · Backend Development

PHP Data Caching: Structures, Index Design Principles, and Code Examples

This article explains PHP data caching techniques—including memory, file, and database caches—provides practical code samples, and outlines key index design principles such as uniqueness, expiration, hotspot optimization, efficient updates, and hierarchical caching to improve performance.

File CacheRedisdatabase cache
0 likes · 5 min read
PHP Data Caching: Structures, Index Design Principles, and Code Examples
Architect's Guide
Architect's Guide
Jan 26, 2023 · Databases

Primary Key and Index Design for Distributed Databases

The article explains why auto‑increment primary keys are unsuitable for sharded databases, recommends globally unique ordered identifiers, and details index and global‑table strategies—including redundant tables, index tables, and embedding shard information in keys—to achieve efficient single‑shard queries.

Distributed DatabasesSQLSharding
0 likes · 10 min read
Primary Key and Index Design for Distributed Databases
IT Architects Alliance
IT Architects Alliance
Sep 15, 2022 · Databases

Designing Indexes and Primary Keys for Distributed Database Architectures

Effective index and primary key design in distributed database architectures requires using globally unique, ordered identifiers instead of auto‑increment keys, embedding shard information in keys, employing auxiliary index tables, and handling global tables to ensure queries target a single shard, thereby preserving scalability and performance.

Distributed DatabasesShardingglobal tables
0 likes · 11 min read
Designing Indexes and Primary Keys for Distributed Database Architectures
Top Architect
Top Architect
Aug 11, 2022 · Databases

Primary Key and Index Design for Distributed Databases

The article explains why auto‑increment primary keys are unsuitable for sharded databases, recommends using globally unique ordered identifiers such as UUIDs or Snowflake IDs, and discusses index design strategies—including index tables, embedding shard keys, and handling global tables—to ensure efficient single‑shard queries.

Distributed Databasesglobal tablesindex design
0 likes · 10 min read
Primary Key and Index Design for Distributed Databases
dbaplus Community
dbaplus Community
Aug 7, 2022 · Databases

Mastering MySQL Performance: Practical Strategies for Slow‑SQL Optimization

This comprehensive guide examines why slow SQL queries degrade system performance, outlines key factors such as data volume, access patterns, and processing methods, and provides concrete optimization techniques, case studies, index design principles, and advanced features like MRR and index push‑down for MySQL‑InnoDB.

InnoDBMySQLQuery Planning
0 likes · 14 min read
Mastering MySQL Performance: Practical Strategies for Slow‑SQL Optimization
Su San Talks Tech
Su San Talks Tech
Jun 16, 2022 · Databases

Master MySQL Index Design: Essential Principles for Optimal Performance

This article explains MySQL index fundamentals, covering clustered and secondary indexes, best‑practice design rules such as using auto‑increment primary keys, avoiding large or low‑cardinality columns, respecting the left‑most prefix, and common scenarios that cause index loss, with practical SQL examples.

MySQLSQLindex design
0 likes · 12 min read
Master MySQL Index Design: Essential Principles for Optimal Performance
dbaplus Community
dbaplus Community
Dec 6, 2021 · Backend Development

Mastering Elasticsearch Index Design: From Sharding to Mapping

This comprehensive guide explains Elasticsearch index design, covering shard and replica planning, settings optimization, dynamic and static mappings, template management, lifecycle automation, and practical best‑practice recommendations for high‑performance, stable search clusters.

Lifecycle ManagementShardingindex design
0 likes · 26 min read
Mastering Elasticsearch Index Design: From Sharding to Mapping
Qunar Tech Salon
Qunar Tech Salon
Nov 24, 2021 · Databases

Comprehensive Guide to Elasticsearch Index Design, Settings, and Mapping

This article provides a detailed guide on Elasticsearch index design, covering index settings, shard and replica planning, mapping strategies, complex types, lifecycle management, template usage, and practical best‑practice recommendations for large‑scale log data clusters.

Big DataElasticsearchSettings
0 likes · 27 min read
Comprehensive Guide to Elasticsearch Index Design, Settings, and Mapping
Programmer DD
Programmer DD
Aug 3, 2021 · Databases

Why MySQL Uses B+ Trees for Indexes and How to Design Effective Indexes

This article explains why MySQL chooses B+ trees for its indexes, covers the left‑most prefix rule for composite indexes, offers practical index‑design tips, discusses MyBatis caching, master‑slave replication, and sharding strategies for large‑scale databases.

B+TreeMyBatisSharding
0 likes · 10 min read
Why MySQL Uses B+ Trees for Indexes and How to Design Effective Indexes
ITPUB
ITPUB
Jun 21, 2021 · Databases

Mastering TiDB: Essential Naming, Design, and SQL Best Practices

This comprehensive guide provides TiDB developers and administrators with standardized naming conventions, object‑design principles, data‑model considerations, SQL development rules, transaction limits, implicit type‑conversion pitfalls, result‑set stability tips, index strategies, auto‑increment handling, timeout settings, JDBC configuration, hotspot mitigation, pagination techniques, and unique ID generation methods.

SQL Best PracticesTiDBTransaction Limits
0 likes · 53 min read
Mastering TiDB: Essential Naming, Design, and SQL Best Practices
Full-Stack Internet Architecture
Full-Stack Internet Architecture
May 10, 2021 · Databases

MySQL Index Design Principles and Common Index Pitfalls

This article explains MySQL index fundamentals, covering primary and unique indexes, best practices for choosing indexed columns, handling functions and large fields, avoiding excessive or frequently updated indexes, and enumerates typical scenarios where indexes become ineffective.

MySQLSQLdatabase optimization
0 likes · 12 min read
MySQL Index Design Principles and Common Index Pitfalls
Selected Java Interview Questions
Selected Java Interview Questions
Mar 6, 2020 · Databases

Best Practices for Choosing Database Indexes

This article outlines essential principles for selecting and managing database indexes, covering unique indexes, indexing frequently sorted or filtered columns, limiting index count, using short or prefix indexes, removing unused indexes, left‑most prefix matching, column cardinality, avoiding functions on indexed columns, and extending existing indexes.

MySQLUnique Indexindex design
0 likes · 7 min read
Best Practices for Choosing Database Indexes
Big Data Technology Architecture
Big Data Technology Architecture
Feb 5, 2020 · Big Data

Elasticsearch Index Design: Scaling to PB/TP Levels and Best Practices

This article provides a comprehensive guide on designing Elasticsearch indices for massive data volumes, covering shard and replica sizing, mapping strategies, rollover templates, curator cleanup, tokenization choices, query type selection, and multi‑table association techniques to achieve efficient, reliable search at PB‑scale.

CuratorElasticsearchRollover
0 likes · 24 min read
Elasticsearch Index Design: Scaling to PB/TP Levels and Best Practices
MaGe Linux Operations
MaGe Linux Operations
Dec 1, 2019 · Databases

Unlock MySQL Performance: Deep Dive into Query Optimization & Index Design

This article explains MySQL's logical architecture, query execution steps, client‑server protocol, query cache behavior, cost‑based optimizer mechanics, and practical index design strategies—including B+Tree fundamentals, covering indexes, and common pitfalls—to help developers achieve measurable performance improvements.

Database PerformanceMySQLQuery Optimization
0 likes · 32 min read
Unlock MySQL Performance: Deep Dive into Query Optimization & Index Design
Architect's Tech Stack
Architect's Tech Stack
Jul 25, 2019 · Databases

Database Command and Design Standards

The article outlines comprehensive MySQL database standards covering naming conventions, table and column design, index strategies, SQL development practices, and operational guidelines to improve performance, maintainability, and security, including recommendations on data types, character sets, reserved fields, binary data storage, and safe query patterns.

DatabaseSQL Best Practicesindex design
0 likes · 21 min read
Database Command and Design Standards
Youzan Coder
Youzan Coder
Apr 7, 2019 · Industry Insights

How Youzan Scaled Order Search: Hot‑State Indexing and AKF Expansion

This article reviews the evolution of Youzan's order search architecture over two years, detailing challenges from data growth, the creation of a hot‑state index covering half of search traffic, time‑sharded indexes, and the AKF expansion cube that guides multi‑axis scalability.

Big DataElasticsearchSearch Architecture
0 likes · 10 min read
How Youzan Scaled Order Search: Hot‑State Indexing and AKF Expansion
MaGe Linux Operations
MaGe Linux Operations
Mar 19, 2019 · Databases

Unlock MySQL Performance: Deep Dive into Query Optimization & Architecture

This article explains MySQL's logical architecture, query processing steps, and the inner workings of its optimizer, cache, and execution engine, then offers practical performance‑tuning advice on schema design, index creation, and specific query patterns to help developers make informed optimization decisions.

Database ArchitectureMySQLQuery Optimization
0 likes · 35 min read
Unlock MySQL Performance: Deep Dive into Query Optimization & Architecture
DataFunTalk
DataFunTalk
Dec 6, 2018 · Databases

HBase RowKey and Index Design: Principles, Practices, and Case Studies

This article introduces HBase fundamentals, explores effective RowKey and secondary index design principles, discusses demand analysis, presents techniques such as reversing, salting, hashing, and reviews real-world case studies for OpenTSDB, JanusGraph, and GeoMesa, offering practical guidance for scalable NoSQL data modeling.

Database ArchitectureHBaseNoSQL
0 likes · 19 min read
HBase RowKey and Index Design: Principles, Practices, and Case Studies
Youzan Coder
Youzan Coder
Nov 24, 2018 · Databases

Youzan Database Technology Open Day 2018 – Sessions on RDS System, MySQL Index Design, and Operations

At Youzan’s 2018 Database Technology Open Day in Hangzhou, 53 companies and 64 participants heard sessions on the evolving RDS architecture, MySQL index design best practices, the ZanDB operations platform, and classic MySQL performance case studies, while showcasing micro‑mall integration and outlining upcoming data‑archiving and multi‑data‑center initiatives.

MySQLRDSindex design
0 likes · 5 min read
Youzan Database Technology Open Day 2018 – Sessions on RDS System, MySQL Index Design, and Operations
Architecture Digest
Architecture Digest
Jul 9, 2018 · Databases

Elasticsearch Index Design and Sharding Principles

This article outlines practical guidelines for designing Elasticsearch indices, comparing single versus time‑based indexes, detailing mapping settings, shard allocation strategies, and deduplication methods, while providing concrete examples and code snippets for effective search infrastructure management.

Data ModelingElasticsearchindex design
0 likes · 5 min read
Elasticsearch Index Design and Sharding Principles
MaGe Linux Operations
MaGe Linux Operations
May 21, 2018 · Databases

Unlock MySQL Performance: Deep Dive into Query Optimization Principles

This article explains MySQL's logical architecture, query execution flow, and the inner workings of its optimizer, then provides practical performance‑tuning advice—including index design, query cache handling, and specific query‑type optimizations—to help developers make informed decisions for real‑world workloads.

Query Optimizationindex designperformance-tuning
0 likes · 38 min read
Unlock MySQL Performance: Deep Dive into Query Optimization Principles
21CTO
21CTO
Oct 31, 2017 · Databases

Mastering InnoDB: Table & Index Design Strategies for High‑Performance MySQL

This article explains InnoDB's record storage mechanics, compares auto‑increment and business primary keys, details primary, secondary, and composite index structures, and shares practical table‑design guidelines and real‑world case studies from ZhaiZhai's MySQL usage.

Composite IndexInnoDBMySQL
0 likes · 12 min read
Mastering InnoDB: Table & Index Design Strategies for High‑Performance MySQL