This comprehensive guide explains MySQL’s logical architecture, common performance bottlenecks, and practical techniques for writing efficient SQL, designing tables, configuring parameters, and optimizing indexes to maintain system stability during updates and scaling.
Understanding MySQL indexes—from hash tables, ordered arrays, and B+ trees—to their practical use cases, including clustered and secondary indexes, reveals why they boost query speed, when they may hinder performance, and how factors like selectivity, dirty page flushing, type mismatches, and function usage affect query efficiency.
This article introduces MySQL fundamentals, covering logical architecture, locking, transactions, storage engines, schema and data type design, as well as index types and their constraints, plus practical creation‑time and query‑time optimization techniques with code examples and best‑practice recommendations.
PostgreSQL 12 brings major enhancements—including up to 40% faster queries on large datasets, improved indexing, partitioning, JSON path queries, generated columns, pluggable table access methods, stronger authentication, and built‑in JIT compilation—while also adding better localization, multi‑constraint authorization, and convenient maintenance commands.
Fusion‑NewSQL is a NewSQL layer built atop Didi’s distributed KV store Fusion, translating MySQL queries into Redis‑style hashmaps, asynchronously maintaining secondary indexes, supporting fast Hive‑to‑Fusion loads and Elasticsearch integration, thereby delivering over 2 million QPS, 600 TB storage and flexible schema evolution for dozens of services.
This article provides a comprehensive guide to Elasticsearch’s architecture, node roles, data organization, and the most important performance metrics—including search, indexing, memory, JVM garbage collection, host‑level system metrics, cluster health, and resource saturation—offering practical advice on monitoring and tuning the cluster for reliability and efficiency.
Alibaba’s 10‑year‑old search engine combines data source aggregation, incremental and real‑time indexing, and online services through platforms like Tisplus, Bahamut, Maat, Ha3, Build Service and Drogo, illustrating a comprehensive architecture that powers 1688’s search capabilities across multiple engines and deployment pipelines.
This tutorial explains Python lists—how to create them, access elements by index, modify items, add or remove entries, sort permanently or temporarily, generate numeric lists with range, perform basic statistics, and use slicing to extract or copy sub‑lists.
This article demonstrates how to dramatically improve MySQL query performance by analyzing execution plans, adding appropriate single‑column and composite indexes, converting slow subqueries to efficient joins, and applying covering indexes and ordering optimizations, with detailed examples and timing results.
This guide walks through diagnosing a Java portal's high CPU usage by checking system load, identifying the offending thread, analyzing JVM stack traces, pinpointing a slow MySQL query, and resolving the issue with data cleanup and index creation, reducing CPU load from 800% to 40%.
This article provides a comprehensive guide to MySQL performance optimization, covering database design principles, field type selection, indexing strategies, storage engine choices, query caching, partitioning methods, master‑slave replication setup, read/write splitting, load balancing, and profiling tools for diagnosing slow queries.
This comprehensive guide introduces Apache Phoenix as an HBase SQL layer, covering quick‑start steps, supported data types, DML syntax, salted tables to prevent hotspots, various secondary index types, bulk‑load methods, auto‑increment IDs, dynamic columns, pagination, query plan analysis, and data migration techniques.
To avoid building separate search services for each Xianyu business, the team created a unified, generic search architecture based on Alibaba’s HA3 engine and a control layer that automates data dumping, indexing, query translation, and result ranking across five subsystems, enabling new services to be onboarded in minutes instead of weeks.
This guide compiles nineteen practical MySQL optimization tips—from using EXPLAIN and avoiding costly IN clauses to leveraging full‑text indexes, proper pagination, and join strategies—providing concrete SQL examples and actionable advice to improve query performance and reduce resource consumption.
This guide compiles comprehensive MySQL conventions covering object naming, storage engine choices, character sets, table and column design, indexing strategies, query optimization, and operational safeguards to improve performance, maintainability, and data integrity in production environments.
This article explains the inner workings of relational databases, covering their architecture, indexing, time‑complexity analysis, query optimization, merge sort, client and query managers, caching strategies, transaction handling, ACID properties, concurrency control, and lock management.
This article explains how 360 Search processes billions of web pages daily, detailing its backend architecture, offline indexing, online retrieval, index organization, and relevance models that enable efficient search over a hundred‑billion‑scale web corpus.
This article examines the limitations of Prometheus's original V2 time‑series storage, proposes a block‑oriented V3 architecture that tackles series churn, write amplification, and indexing inefficiencies, and validates the new design with extensive benchmarks showing dramatic reductions in memory, CPU, and disk usage.
This article analyzes MongoDB sort operation memory limits, explains why queries may exceed the 32 MB in‑memory sort threshold, demonstrates how to inspect and adjust server parameters, create appropriate compound indexes, and use allowDiskUse in aggregation pipelines to avoid sort‑related errors.
This article compiles common Elasticsearch interview questions and detailed answers covering cluster architecture, inverted index fundamentals, index design, write/query optimizations, master election, document indexing flow, search process, Linux tuning, and Lucene internals, providing practical guidance for candidates.
This article explores MySQL performance when storing and querying billions of financial transaction records, detailing table creation, massive data generation, the impact of indexes on query and insert speed, pagination limits, and a practical time‑based sharding approach for bank‑level cash‑flow logs.
This article presents an in‑depth interview with Alibaba Cloud senior expert Sun Tingtao, detailing the architecture, core features, design challenges, and operational strategies of the Alibaba Cloud Log Service that handles over 10 PB of daily log data for massive, diverse production workloads.
An InnoDB storage engine sits above the file system and below SQL tools, providing transactions with redo/undo logs, row and table locking, MVCC, B+‑tree and adaptive hash indexes, compression, encryption, checkpointing, multi‑threaded buffering, backup and replication mechanisms, and extensive performance‑monitoring commands.
This article explains how Fangdd leverages Elasticsearch to boost search performance across consumer, broker, and internal products, detailing a platformized architecture that separates indexing and querying, addresses operational challenges, and outlines design patterns for index management and incremental updates.
This article explains the new descending index feature in MySQL 8.0, compares its execution plans and resource usage against traditional ascending indexes, and provides practical examples and composite index strategies to improve query performance.
This article explains why using NULL columns in MySQL can lead to extra storage, complex indexing, and query pitfalls, and provides concrete reasons and examples showing how NOT NULL constraints improve data integrity and performance.
This article explains how Elasticsearch stores JSON documents, indexes them, and uses RESTful PUT, GET, DELETE, and search APIs to perform create, read, update, and delete operations, illustrating each step with concrete employee examples and query syntax.
This article explains how to map relational table rows and various indexes—including primary key, integer, float, string, and composite indexes—into RocksDB key‑value pairs using custom serialization, big‑endian ordering, and encoding functions to enable efficient point, range, and multi‑field queries.
MySQL can avoid a filesort for ORDER BY clauses that match an index’s direction by scanning the index forward or backward, and MySQL 8.0’s descending indexes let mixed‑direction sorts be satisfied similarly; for older versions you can emulate this by retrieving rows in index order and reversing groups in the application.
This article reviews the most notable enhancements introduced in MySQL 8.0.13 and 8.0.14, covering query speed optimizations, the new Skip Scan access method, changes to index design rules, deprecated features, and several operational and security improvements for database administrators and developers.
This article explains how MySQL storage engines differ, clarifies the structures and search mechanisms of B‑Tree and B+Tree indexes, and outlines practical principles for creating efficient indexes to reduce I/O and speed up query execution.
This guide outlines comprehensive MySQL naming conventions, table and column design rules, indexing best practices, SQL coding standards, and operational recommendations to improve performance, maintainability, and security while avoiding common pitfalls such as redundant indexes, unsafe data types, and excessive batch operations.
This article presents a comprehensive set of MySQL high‑performance optimization guidelines, covering naming conventions, table design, data types, index strategies, SQL coding standards, replication, backup, and operational best practices to improve efficiency, reliability, and scalability of database systems.
This guide explains how to improve MySQL database performance by identifying bottlenecks and applying both soft (query, indexing, table design) and hard (hardware, configuration, sharding, caching) optimizations, offering concrete commands, parameter settings, and practical examples.
This article explains the main index structures used in MySQL—including hash tables, ordered arrays, search trees, and B+ trees—covers why InnoDB prefers B+ trees, the benefits of auto‑increment primary keys, and key concepts such as covering indexes, the left‑most prefix rule, and index‑pushdown for query optimization.
This article presents a detailed set of MySQL database naming conventions, design principles, field type recommendations, index strategies, and SQL development best practices to improve performance, maintainability, and security while avoiding common pitfalls such as reserved keywords, oversized tables, and inefficient queries.
This article presents SlimTrie, a trie‑based indexing structure that dramatically reduces memory consumption while maintaining fast query speeds, detailing its design, compression techniques, implementation steps, memory analysis, and performance comparisons with map and B‑Tree structures for large‑scale storage systems.
This guide outlines comprehensive MySQL best practices, covering naming conventions, basic schema design, field type selection, index strategies, foreign‑key usage, SQL coding standards, batch operations, online schema changes, and permission principles to ensure performant and maintainable databases.
This article presents practical SQL performance tips—ranging from using proper comparison operators and LIMIT clauses to indexing strategies and query consistency—to help developers significantly speed up database queries and improve overall application responsiveness.
This comprehensive guide explains the main factors that slow down MySQL queries—including inefficient SQL, concurrency limits, CPU and disk I/O bottlenecks, network issues, large tables, and transaction overhead—while offering concrete configuration tweaks, storage‑engine choices, indexing strategies, and sharding techniques to dramatically improve performance.
This article explains MySQL’s storage engines, compares B‑Tree and B+Tree indexes, details their structures and search processes, and outlines key principles for designing efficient indexes to avoid slow queries in relational databases.
The article recounts a pre‑promotion MySQL replication incident at JD.com, explains the master‑slave architecture and thread roles, identifies slow‑SQL and missing indexes as root causes of replication lag, and proposes practical measures to reduce latency and improve system stability.
This guide presents comprehensive Elasticsearch tuning strategies—including limiting result sets, avoiding large documents, optimizing indexing with bulk requests and refresh intervals, enhancing search speed through field modeling and caching, and reducing disk usage via compression and shard management—to improve both indexing and query performance.
This article compiles 52 practical SQL performance optimization strategies—including indexing best practices, query rewriting, use of temporary tables, storage engine selection, data type choices, backup procedures, and diagnostic tools—to help developers write faster, more efficient database queries.
This guide outlines comprehensive MySQL best‑practice rules covering object naming, table engine and charset choices, column design, index strategies, SQL coding conventions, and operational safeguards to improve performance, maintainability, and data integrity in production environments.
This comprehensive guide explains Neo4j's label‑property graph model, node and relationship creation, Cypher syntax, indexing, constraints, schema inspection, and best practices for avoiding duplicate data, providing practical examples and performance tips.
The article presents an in‑depth technical overview of JD's self‑developed JMQ middleware, detailing its performance improvements over previous versions, comparing write throughput with Kafka under synchronous and asynchronous scenarios, and explaining JMQ4's storage architecture, dense indexing, high‑performance I/O, and cache management strategies.
This guide details how to optimize Elasticsearch for handling billions of daily records, covering core Lucene concepts, index and shard configuration, performance‑tuning parameters, and practical testing methods to achieve sub‑second query responses and long‑term data retention.
This article presents 19 practical MySQL optimization tips, ranging from using EXPLAIN and proper indexing to rewriting IN/EXISTS queries, avoiding costly ORDER BY RAND(), improving pagination, leveraging full‑text indexes, and applying join and execution‑plan strategies for faster, more efficient database performance.
This article shares extensive practical experience from Qunar's large‑scale Elasticsearch deployment, covering suitable use cases, index‑type design, document ID strategies, scaling considerations for index and data volume, hardware sizing, and storage architecture recommendations to help newcomers avoid common pitfalls.
This article walks through a real‑world ERP database performance case, detailing the symptoms, metric analysis, root‑cause investigation across hardware, environment, and code, and a three‑phase optimization strategy that dramatically reduced latency and improved system stability.
This article presents a comprehensive overview of constructing a search engine using Elasticsearch, covering architecture components, data read/write mechanisms, shard management, caching strategies, and real‑world case studies that illustrate performance tuning, isolation, and deployment best practices.
This article examines MySQL database fundamentals, including storage architecture, block-based data access, disk I/O bottlenecks, and detailed explanations of B+Tree and hash indexes in MyISAM and InnoDB, while offering practical guidance on index design, selectivity, left‑most prefix rules, and using EXPLAIN for SQL optimization.
This article explains how large data volumes and differing environments can degrade MySQL query performance, demonstrates using EXPLAIN to analyze index usage and row scans, and offers practical steps such as adding filters, pagination, and code adjustments to improve speed.
This article provides a comprehensive overview of MySQL indexing, covering B‑Tree and B+Tree structures, common search algorithms, storage engine differences, and practical optimization techniques such as composite and prefix indexes to improve query performance.
This guide explains why MySQL performance drops when a single table grows huge and provides a comprehensive set of optimization steps—including field type choices, index design, query tuning, engine selection, system parameters, hardware scaling, read/write splitting, caching, partitioning, vertical and horizontal sharding, and architecture recommendations—to keep MySQL fast and reliable at scale.
Tencent Cloud Academy will livestream a MySQL schema design and development standards session on September 17 at 20:00, featuring experts Ye Jinrong and Wu Bingxi, covering best practices such as default InnoDB engine, utf8mb4 charset, row size limits, partitioning, appropriate data types, index guidelines, and operational recommendations.
The article describes how 58's search technology team standardized and modularized their vertical search services into a cloud-native platform, detailing the overall architecture, Kubernetes‑based deployment, service and deployment designs, stateful indexing challenges, and solutions for high‑availability and scalability.
This guide explains Python’s built‑in enumerate() function, covering its purpose, syntax, parameters, return type, and practical examples with lists, tuples, strings, dictionaries and sets to help you iterate with automatic indexing in for‑loops.
This article details a big‑data precise‑marketing solution that leverages HBase storage and Roaring BitMap indexing to efficiently handle billions of user records, describing project background, technology selection, architecture, partitioning strategy, and coprocessor implementation for fast multidimensional queries.
Meituan‑Dianping built a custom C++11 real‑time advertising index featuring a hierarchical model, lock‑free three‑layer architecture, specialized memory allocators, and forward/inverted indexes to achieve millisecond updates, high throughput, and scalability for search ads, with plans for Java integration and SQL support.
This article demystifies MySQL query optimization by explaining the server's logical architecture, the end‑to‑end query processing flow, the role of the client/server protocol, query cache, parsing, optimizer, execution engine, and result delivery, and then offers concrete performance‑tuning recommendations on schema design, data types, indexing strategies, and specific query patterns such as COUNT(), JOIN, LIMIT pagination, and UNION.
This guide outlines practical MySQL design principles covering storage engine selection, naming conventions, field type choices, indexing strategies, query optimization, and table partitioning to help developers build efficient, reliable databases.
This article explains the fundamentals of full‑text search, describing how Lucene builds and uses inverted indexes, the steps of tokenization, linguistic processing, term weighting, and relevance scoring, and illustrates these concepts with examples, tables, and diagrams.
This guide explains practical, rule‑based steps for creating effective compound indexes in MySQL, MariaDB, and PerconaDB, illustrates the process with a concrete query example, and warns against common indexing mistakes such as over‑indexing, OR conditions, and wrong column order.
This article explains the core of search engine technology by describing what an inverted index is, how it is built using single‑pass memory and multi‑way merge methods, how indexes can be partitioned and incrementally updated, and how Hadoop can be used for large‑scale indexing.
This tutorial walks you through installing Elasticsearch, understanding its core concepts such as nodes, clusters, indexes, documents and types, configuring Chinese analyzers, performing CRUD operations, and executing various search queries with practical command‑line examples.
Learn ten practical strategies to accelerate database queries—including proper schema design, using EXPLAIN, caching, selective column retrieval, LIMIT clauses, avoiding loops, replacing subqueries with joins, careful wildcard use, UNION over OR, and indexing—to reduce server load and improve application responsiveness.
This article presents seven practical techniques—including using EXPLAIN, creating proper indexes, adjusting default settings, loading data into memory, leveraging SSD storage, scaling horizontally, and improving observability—to keep MySQL databases fast, stable, and responsive as workloads grow.
This article presents seven practical MySQL performance‑tuning techniques—including mastering EXPLAIN, building effective indexes, overriding default settings, optimizing memory usage, adopting SSD storage, scaling horizontally, and improving visibility—while explaining key configuration variables and monitoring tools to keep your database fast and reliable.
Learn a comprehensive methodology for SQL performance tuning, from identifying resource bottlenecks and understanding query processing stages to applying practical optimization tactics such as efficient joins, index usage, avoiding costly operations, and best‑practice guidelines that improve execution speed and resource consumption.
The article describes the requirements, architecture, data‑sync strategy, monitoring, and operational workflow of a search open platform that enables fast, zero‑code creation of searchable interfaces, supporting real‑time indexing, customizable ranking, and extensible backend services.
This article explains MongoDB indexing fundamentals, covering B‑Tree structures, index selectivity, unique and compound indexes, practical code examples, and guidelines to optimize query performance and reduce I/O operations.
This article explains the design, table schema, indexing workflow, and query processing of a distributed full‑text search system that stores documents and token information separately in a distributed database, improving scalability and performance over traditional Lucene‑based solutions.
Part 3 of the Elasticsearch indexing optimization guide advises balancing shard and replica counts, using index aliases, leveraging the Bulk API with appropriately sized batches, dedicating data nodes, and upgrading storage (SSD/RAID 0) while monitoring resources to achieve higher throughput and scalable, reliable clusters.
Database sharding, or splitting databases into multiple instances and tables, is essential for scaling backend systems; this article explains its importance, historical context, core concepts, storage stretching techniques, index and consistency handling, and provides a practical design example for transaction systems.
This article explains the motivations behind database sharding, defines the concept, explores its historical background, discusses storage stretching, indexing and consistency challenges, and presents a practical sharding design example for transaction systems.
This article walks through diagnosing a sluggish Oracle search query with many optional filters, removing unnecessary joins and function calls, applying conditional MyBatis joins, and creating composite indexes to dramatically lower the execution plan cost from 13902 to under 100.
This article describes the motivation, design, and implementation of a label‑property graph database built as a thin layer over Aerospike, detailing its data model, API operations for graphs, indexes, vertices, edges, and query capabilities.
This guide presents ten practical MySQL performance techniques—from workload profiling and resource awareness to smart indexing, pagination, and monitoring—offering concrete steps, tool recommendations, and common pitfalls to help database administrators keep their servers fast, stable, and scalable.
A developer added a seemingly harmless filter to an Oracle SQL statement, causing the optimizer to switch the driving table and join method, which inflated execution time from 2 seconds to 90 seconds, prompting a deep dive into execution plans, indexing, and hint techniques.
This article compiles a DBAplus online session that presents a systematic SQL‑optimization methodology, rewrite tricks, common pitfalls, and a series of concrete Oracle examples—including CASE WHEN, ROWNUM pagination, hint usage, column selection, function‑call reduction, trigger avoidance, and ROWID access—plus a detailed Q&A that clarifies practical tuning questions.
This guide outlines practical principles for creating Oracle indexes, compares B‑Tree and bitmap index types, explains their creation syntax, highlights performance characteristics, and lists common situations where indexes may be ignored or unsuitable.
Before profiling performance you must locate slow queries, enable MySQL's slow query log, use EXPLAIN to analyze execution plans, and add appropriate indexes—such as on picture.album_id—to dramatically reduce scanned rows and boost query speed by hundreds of times, while balancing read/write trade‑offs.
This article explains the data structures and algorithms behind MySQL indexing, covering B‑Tree fundamentals, insertion and deletion steps with visual examples, the differences between B‑Tree and B+Tree, InnoDB's clustered and secondary index implementations, and why monotonically increasing primary keys improve performance.
This article explains the inner workings of relational databases, covering their core components, query optimization flow, indexing, merge sort, transaction handling, concurrency control, and lock management, while illustrating concepts with examples and diagrams.
This guide outlines practical MySQL optimization strategies—including table schema design, index creation, SQL tuning, connection‑pool configuration, and historical data archiving—to help developers maintain fast, scalable databases as data volume grows.
This article reviews the latest Elasticsearch 5.x enhancements—including append‑only indexing, range fields, removal of the _all field, unified highlighter, keyword normalizer, multi‑word synonyms, field collapsing, cancellable searches, partitioned term aggregations, cluster allocation explain, Java REST client updates, cross‑cluster search, batched reduce phases—and previews the major features expected in Elasticsearch 6.0 such as sparse doc values, index sorting, sequence numbers, seamless rolling upgrades, type removal, index‑template inheritance, load‑aware shard routing, and X‑Pack extensions like SQL and machine learning.
The article details Tencent File System (TFS), describing its platform components, 3D indexing techniques, high‑performance key‑value storage (TSSD) with MHT, dual‑read and smooth scaling mechanisms, hybrid index storage, host‑level FTL, Append‑Only and erasure‑coding storage engines, and how these innovations deliver scalable, low‑cost, high‑performance data storage for massive workloads.
This article introduces the architecture, key modules, and implementation details of the Little Boy platform, a Greenplum optimization and operations system that parses SQL, applies index and distribution‑key tuning, manages resources, and outlines future enhancements for large‑scale data warehouses.
This article explains the fundamentals of search engine architecture, covering text collection, indexing, ranking and evaluation, and then traces Twitter's internal search evolution from MySQL full‑text search to the Earlybird index server, Blender aggregation, and smart memory‑SSD strategies.
This guide walks through identifying the root cause of a SQL Server instance's 100% CPU usage using Profiler, analyzing the offending queries, and applying targeted index and query optimizations to dramatically reduce CPU consumption.
This article compiles frequently used MySQL SQL commands—including data migration, multi‑table updates, conditional updates, aggregation inserts, and various ALTER TABLE operations such as adding indexes, dropping and adding columns—to serve as a practical reference for database developers.
A detailed case study describes how a rapidly growing edit‑log feature caused query times to soar to 30 seconds, and walks through the step‑by‑step investigation, identification of a custom function bottleneck, data‑volume analysis, and the eventual implementation of partitioning, mandatory time filters, and composite indexing to restore acceptable performance.
A detailed Oracle performance case study shows how adding selective predicates and a supporting index to an UPDATE that runs hourly can reduce redo log generation from over 100 MB to under 1 MB and cut logical reads by more than 95%, with step‑by‑step measurements and code examples.
This article presents a comprehensive set of practical strategies for improving database query performance, covering index design, SQL statement refinements, Java backend considerations, hardware tuning, and storage‑procedure usage to accelerate operations on large‑scale relational databases.
A .NET MVC warehouse system’s MySQL view was taking over 30 seconds to fetch 70,000 inventory records, but by adding a composite index on the dictionary table, removing a UNION ALL, and adjusting view algorithms, the query time dropped to a few seconds, with a stored‑procedure alternative for flexible use.
The discussion explores practical strategies for adding an index to a MySQL table containing tens of millions of rows without stopping service, covering intermediate tables, double‑write syncing, rename operations, traffic considerations, lock types, and tooling such as Percona.
This article presents practical MySQL optimization techniques covering table schema design, character set choices, primary key strategies, field definitions, index creation, SQL query tuning, pagination tricks, join handling, connection‑pool parameter tuning, and historical data archiving for large‑scale databases.
This guide shares ten practical MongoDB performance tips—including proper index ordering, using explain() and hint(), avoiding costly query operators, leveraging capped collections, upsert, sorting limits, aggregation, drop vs remove, batch inserts, and optimal indexing for arrays and range queries—to help developers boost database efficiency.
