Tagged articles
index design
43 articles · Page 1 of 1
This comprehensive guide covers MySQL slow query analysis from log configuration to index design, EXPLAIN plan interpretation, SQL rewriting techniques, and production troubleshooting workflows with real-world examples and concrete metrics.
EXPLAINMySQLSQL Optimization
0 likes · 84 min read

This article distills production-hardened Elasticsearch patterns from a billion-item e-commerce search system, covering index mapping pitfalls, IK analyzer tuning, search_after pagination, split-brain prevention, hot-warm-cold ILM tiering, dual-path MySQL-ES sync via Canal and MQ, and a 7-point architecture review checklist.
CanalCluster ArchitectureConsistency Guarantee
0 likes · 25 min read

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.
CachingMySQLRead-Write Separation
0 likes · 38 min read

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.
ElasticsearchJavabulk processing
0 likes · 30 min read

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 ANALYZEPerformance TroubleshootingPostgreSQL
0 likes · 32 min read

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

This guide presents fourteen practical Elasticsearch best‑practice recommendations—including index design, field‑type choices, query tuning, cluster layout, and architecture patterns—plus four detailed business‑scenario solutions for logging, e‑commerce, monitoring, and user‑behavior analytics.
Best PracticesElasticsearcharchitecture
0 likes · 8 min read

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.
ElasticsearchField Typescluster management
0 likes · 12 min read

This guide walks you through the fundamentals of MySQL performance tuning—covering the optimization pyramid, how to read EXPLAIN plans, practical index design, safe online DDL for large tables, and interview‑ready case studies that turn SQL expertise into a powerful hiring advantage.
EXPLAINInterview PreparationMySQL
0 likes · 20 min read

Understanding MySQL's clustered indexes—including their definition, selection rules, customization via primary key design, practical creation examples, and key design considerations—helps developers optimize query performance and storage efficiency in InnoDB tables for various workloads.
InnoDBMySQLSQL
0 likes · 8 min read

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.
MySQL PerformanceSQL Tuningdatabase optimization
0 likes · 13 min read

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.
SQL Tuningdatabase optimizationhardware tuning
0 likes · 22 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 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.
BackendElasticsearchMapping
0 likes · 21 min read

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.
ElasticsearchMappingSharding
0 likes · 23 min read

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.
ElasticsearchMappingSharding
0 likes · 20 min read

This article compares ClickHouse and MySQL by examining benchmark results, MPP architecture, columnar storage, compression techniques, vectorized execution, and index designs, showing why ClickHouse delivers dramatically higher query performance on massive data sets.
ClickHouseMPPMySQL
0 likes · 8 min read

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

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

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.
Primary KeySQLSharding
0 likes · 10 min read

This guide walks you through the full Elasticsearch data‑modeling workflow, covering business‑driven design, handling large data volumes, optimal settings, mapping choices, and complex parent‑child relationships, while providing practical code examples and visual diagrams for immediate application.
Data ModelingElasticsearchIngest Pipeline
0 likes · 18 min read

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.
Primary KeyShardingUUID
0 likes · 11 min read

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.
Primary KeyUUIDdistributed databases
0 likes · 10 min read

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.
InnoDBMySQLPerformance Tuning
0 likes · 14 min read

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

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.
MappingShardingindex design
0 likes · 26 min read

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.
ElasticsearchMappingSettings
0 likes · 27 min read

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+TreeMyBatisReplication
0 likes · 10 min read

This article explains MySQL's three‑layer logical architecture, the end‑to‑end query execution flow, and the inner workings of the optimizer, cache, and storage engine, then offers practical schema, index, and specific‑query tuning techniques to boost database performance.
Database PerformanceMySQLSQL
0 likes · 36 min read


ITPUBJun 21, 2021 · Databases
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

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

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

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.
CuratorElasticsearchMapping
0 likes · 24 min read

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 PerformanceMySQLcost-based optimizer
0 likes · 32 min read

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.
SQL Best Practicesdatabaseindex design
0 likes · 21 min read

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.
ElasticsearchSearch Architecturebackend development
0 likes · 10 min read

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 ArchitectureMySQLPerformance Tuning
0 likes · 35 min read

This article explains MySQL's logical architecture, query processing steps, and the inner workings of the optimizer, then provides practical performance‑tuning advice covering data types, index design, cache usage, and specific query patterns such as COUNT, JOIN, LIMIT pagination and UNION.
MySQLPerformance TuningQuery Cache
0 likes · 37 min read

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

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.
MySQLPerformance TuningRDS
0 likes · 5 min read

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

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.
Performance Tuningindex designquery optimization
0 likes · 38 min read


21CTOOct 31, 2017 · Databases
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
