Skip to content
Tagged articles

Indexing

500 articles · Page 5 of 5
DeWu Technology
DeWu Technology
Jul 2, 2021 · Databases

MySQL Deep Pagination Optimization

MySQL deep pagination can be dramatically accelerated by ordering on the primary key, indexing the sort column, and using keyset pagination or a sub‑query join instead of scanning millions of rows, while only minor tweaks like increasing sort_buffer_size provide negligible gains.

Deep PaginationIndexingPagination
0 likes · 10 min read
MySQL Deep Pagination Optimization
Top Architect
Top Architect
Jun 30, 2021 · Databases

Analyzing and Optimizing MySQL Pagination Performance with Large Offsets

This article investigates why MySQL queries with large LIMIT offsets become extremely slow, demonstrates the issue with simulated millions‑of‑row datasets, and presents three optimization strategies—including index‑covering subqueries, remembering the last primary‑key position, and applying offset throttling—to achieve consistent, fast pagination performance.

IndexingOptimizationPagination
0 likes · 12 min read
Analyzing and Optimizing MySQL Pagination Performance with Large Offsets
Java Architect Essentials
Java Architect Essentials
Jun 21, 2021 · Databases

Understanding MySQL Slow Queries, Index Optimization, and Integration with Elasticsearch and HBase

This article explains why MySQL queries become slow, how index design and common pitfalls affect performance, introduces MDL locks and large‑table strategies, then compares Elasticsearch and HBase as complementary storage and search solutions, providing practical code examples and best‑practice recommendations.

HBaseIndexingdatabase optimization
0 likes · 16 min read
Understanding MySQL Slow Queries, Index Optimization, and Integration with Elasticsearch and HBase
Open Source Linux
Open Source Linux
Jun 10, 2021 · Databases

MySQL vs MariaDB: Real-World Performance Benchmark and Analysis

This article reviews the history of MySQL and MariaDB, describes a test environment, runs insertion, batch, and query benchmarks (with and without indexes), and concludes that MariaDB generally outperforms MySQL in speed and memory usage while highlighting trade‑offs of indexing.

BenchmarkDatabase PerformanceIndexing
0 likes · 9 min read
MySQL vs MariaDB: Real-World Performance Benchmark and Analysis
Efficient Ops
Efficient Ops
Jun 9, 2021 · Databases

Why MySQL Queries Go Slow and How to Fix Them with Indexes, ES, and HBase

This article explains why MySQL queries become slow, explores index-related pitfalls and optimization techniques, and then compares ElasticSearch and HBase as complementary solutions for large‑scale data and search scenarios, offering practical tips and code examples.

Database PerformanceHBaseIndexing
0 likes · 21 min read
Why MySQL Queries Go Slow and How to Fix Them with Indexes, ES, and HBase
IT Architects Alliance
IT Architects Alliance
Jun 7, 2021 · Databases

Analyzing and Optimizing MySQL Pagination Performance with Large Offsets

The article examines a production MySQL query that suffers severe slowdown due to large LIMIT offsets, demonstrates how to reproduce the issue with massive test data, analyzes the root cause, and presents three optimization strategies—including index covering, keyset pagination, and offset limiting—to dramatically improve query performance.

IndexingOptimizationPagination
0 likes · 13 min read
Analyzing and Optimizing MySQL Pagination Performance with Large Offsets
IT Architects Alliance
IT Architects Alliance
May 26, 2021 · Databases

Understanding MySQL Slow Queries, Elasticsearch, and HBase: Causes and Practical Solutions

This article explains why MySQL queries become slow, how indexes work and fail, the impact of MDL locks, large‑table challenges, sharding and read‑write splitting strategies, then introduces Elasticsearch’s search capabilities and HBase’s column‑family storage, offering practical guidance for each technology.

Database PerformanceElasticsearchHBase
0 likes · 17 min read
Understanding MySQL Slow Queries, Elasticsearch, and HBase: Causes and Practical Solutions
Programmer DD
Programmer DD
May 22, 2021 · Databases

Master MySQL Indexes: B+Tree Deep Dive & Optimization Tips

This article explains MySQL's B‑Tree index fundamentals, compares MyISAM and InnoDB implementations, outlines how B+Tree structures work, and provides practical guidelines for designing effective indexes and tuning MySQL configuration to boost query performance.

B+ TreeIndexingInnoDB
0 likes · 27 min read
Master MySQL Indexes: B+Tree Deep Dive & Optimization Tips
Java Interview Crash Guide
Java Interview Crash Guide
May 21, 2021 · Backend Development

Mastering Elasticsearch: Core Concepts, Indexing, and Real‑Time Search Explained

This comprehensive guide walks through Elasticsearch fundamentals, including its architecture, core concepts like indices, shards, and replicas, the write and update processes, search workflow, consistency mechanisms, master election, performance tuning, and strategies for deep pagination and scroll searches.

ElasticsearchIndexingreal-time-search
0 likes · 17 min read
Mastering Elasticsearch: Core Concepts, Indexing, and Real‑Time Search Explained
Java Tech Enthusiast
Java Tech Enthusiast
May 18, 2021 · Backend Development

Master ElasticSearch: Install, Index, and Run Advanced Java Queries

This guide walks you through downloading and installing ElasticSearch, explains core concepts like indices, types, documents, and fields, demonstrates CRUD operations via RESTful APIs, shows advanced query techniques, and provides complete Java integration examples using Maven and Docker.

Advanced QueriesElasticsearchIndexing
0 likes · 18 min read
Master ElasticSearch: Install, Index, and Run Advanced Java Queries
Architecture Digest
Architecture Digest
May 9, 2021 · Databases

Optimizing MySQL Pagination with LIMIT: Methods, Experiments, and Index Strategies

This article examines the performance drawbacks of MySQL's LIMIT pagination on large tables, presents six practical query methods—including direct LIMIT, primary‑key indexing, index‑based ordering, prepared statements, covering indexes, and sub‑query/join techniques—provides extensive benchmark results, and offers concrete indexing recommendations to achieve fast, stable pagination even with millions of rows.

IndexingLIMITLarge Data
0 likes · 12 min read
Optimizing MySQL Pagination with LIMIT: Methods, Experiments, and Index Strategies
360 Tech Engineering
360 Tech Engineering
May 7, 2021 · Databases

MongoDB Sharding Key Query Failure and Unique Index Violation Explained

This article reproduces a MongoDB issue where queries using a hashed sharding key return no results and a unique compound index appears to fail, then explains the root cause—index entries exceeding the 1024‑byte limit and the failIndexKeyTooLong parameter—providing step‑by‑step commands, configuration changes, and practical recommendations.

IndexingUnique IndexfailIndexKeyTooLong
0 likes · 35 min read
MongoDB Sharding Key Query Failure and Unique Index Violation Explained
Open Source Linux
Open Source Linux
Apr 30, 2021 · Databases

Master MySQL Query Optimization: Architecture, Caching, and Index Strategies

This article explains MySQL's logical architecture, query execution flow, client‑server protocol, query cache behavior, parsing and optimization stages, cost‑based optimizer, execution engine, and provides practical performance‑tuning advice such as schema design, data‑type choices, index creation, B‑Tree fundamentals, covering indexes, and handling COUNT, JOIN, LIMIT, and UNION queries.

B+ TreeIndexingQuery Optimization
0 likes · 35 min read
Master MySQL Query Optimization: Architecture, Caching, and Index Strategies
Full-Stack Internet Architecture
Full-Stack Internet Architecture
Apr 25, 2021 · Databases

Overview of Database System Design

This article provides a comprehensive overview of database system design, covering the historical evolution, classification of relational and NoSQL databases, common architectures, consistency models, indexing techniques, storage formats, compression, and practical considerations for selecting and optimizing database solutions.

ConsistencyIndexingStorage
0 likes · 45 min read
Overview of Database System Design
Programmer DD
Programmer DD
Apr 24, 2021 · Databases

50 Essential SQL Performance Optimization Tips Every Developer Should Know

This article compiles 50 practical SQL performance optimization techniques—from indexing strategies and query rewriting to avoiding full table scans, using temporary tables, proper data types, and efficient backup methods—helping developers write faster, more scalable database queries.

IndexingSQLmysql
0 likes · 22 min read
50 Essential SQL Performance Optimization Tips Every Developer Should Know
Intelligent Backend & Architecture
Intelligent Backend & Architecture
Apr 23, 2021 · Big Data

Mastering Elasticsearch: Core Concepts, Architecture, and Performance Tips

This comprehensive guide explains Elasticsearch’s fundamentals, including its distributed architecture, indexing process, shard and replica mechanisms, query execution, near‑real‑time search, segment management, and practical optimization techniques, providing developers and engineers with the knowledge needed to design, operate, and troubleshoot large‑scale search clusters.

Distributed SystemsIndexingNear Real-Time
0 likes · 71 min read
Mastering Elasticsearch: Core Concepts, Architecture, and Performance Tips
Intelligent Backend & Architecture
Intelligent Backend & Architecture
Apr 21, 2021 · Databases

Unlocking MySQL: Core Features, Architecture, and Index Optimization Explained

This comprehensive guide explores MySQL’s origins, key characteristics, logical architecture, storage engines, transaction handling, concurrency control, and detailed indexing strategies, offering practical insights and best‑practice recommendations for developers and database administrators seeking to master MySQL performance and reliability.

IndexingSQLTransactions
0 likes · 41 min read
Unlocking MySQL: Core Features, Architecture, and Index Optimization Explained
360 Smart Cloud
360 Smart Cloud
Apr 15, 2021 · Information Security

Improving Product Quality through Code Vulnerability Scanning and Deep Code Search

This article explains why and when to scan product code for vulnerabilities, describes static source‑code and binary scanning methods, introduces deep code‑search techniques and a real‑time Sphinx‑based indexing architecture, and shows how these practices can significantly raise overall product quality.

IndexingProduct QualitySphinx
0 likes · 13 min read
Improving Product Quality through Code Vulnerability Scanning and Deep Code Search
macrozheng
macrozheng
Apr 14, 2021 · Databases

Why You Should Avoid NULL Columns in MySQL: Performance, Storage, and Index Impacts

This article explains why setting MySQL columns to NOT NULL improves query optimization, reduces index complexity, saves storage space, and prevents unexpected behavior in calculations, while also detailing default values, handling of NULL in aggregates, and the internal row format differences between NULLable and NOT NULL columns.

IndexingNOT NULLNULL
0 likes · 12 min read
Why You Should Avoid NULL Columns in MySQL: Performance, Storage, and Index Impacts
Full-Stack Internet Architecture
Full-Stack Internet Architecture
Apr 10, 2021 · Databases

Comprehensive MySQL Knowledge Points and Optimization Guide

This article provides a thorough overview of MySQL fundamentals—including schema design, storage engines, transaction mechanisms, logging, indexing, and performance tuning—along with practical SQL examples, code snippets, and optimization strategies for both hardware and database configuration.

IndexingOptimizationTransactions
0 likes · 34 min read
Comprehensive MySQL Knowledge Points and Optimization Guide
Top Architect
Top Architect
Mar 24, 2021 · Databases

Understanding the Use of WHERE 1=1 in Dynamic SQL

The article explains why developers insert the clause "WHERE 1=1" in dynamically generated SQL statements, shows example Java code, discusses how it prevents syntax errors when concatenating AND conditions, and offers advice on adding selective filters and indexes to avoid full table scans.

Dynamic QueryIndexingSQL
0 likes · 3 min read
Understanding the Use of WHERE 1=1 in Dynamic SQL
Java Captain
Java Captain
Mar 21, 2021 · Databases

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

This article investigates why MySQL recommends auto_increment primary keys over UUID or non‑sequential snowflake IDs by building three tables, running insert‑and‑query benchmarks with Spring Boot's JdbcTemplate, analyzing index structures, and summarizing the performance trade‑offs and practical drawbacks of each key strategy.

Indexingauto-incrementprimary-key
0 likes · 10 min read
Performance Comparison of Auto‑Increment, UUID, and Random Snowflake IDs in MySQL
Architect's Tech Stack
Architect's Tech Stack
Mar 15, 2021 · Databases

Optimizing a 20‑Million‑Row MySQL Table: Design, Indexing, Partitioning, and Migration Strategies

This article describes how to improve the performance of a massive MySQL 5.6 user‑log table by redesigning schema, applying proper indexes, using partitioning, considering table sharding, and evaluating upgrade paths to compatible cloud or big‑data databases, with concrete SQL examples and cost analysis.

IndexingLarge TablesPartitioning
0 likes · 16 min read
Optimizing a 20‑Million‑Row MySQL Table: Design, Indexing, Partitioning, and Migration Strategies
Top Architect
Top Architect
Mar 4, 2021 · Databases

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

This article investigates why MySQL recommends auto_increment over UUID or non‑sequential snowflake IDs by creating three tables, running insert and query benchmarks with Spring Boot and JdbcTemplate, analyzing index structures, and summarizing the performance trade‑offs and security considerations of each primary‑key strategy.

IndexingPrimary Keyauto-increment
0 likes · 10 min read
Performance Comparison of Auto‑Increment, UUID, and Random Keys in MySQL
Code Ape Tech Column
Code Ape Tech Column
Mar 3, 2021 · Databases

When INSERT INTO SELECT Breaks MySQL: A Cautionary Data‑Migration Story

An engineer’s costly mistake using MySQL’s INSERT INTO SELECT for nightly data migration led to out‑of‑memory crashes, full‑table scans, and payment record loss, prompting a deep dive into locking behavior, transaction isolation, and how proper indexing can safely rescue large‑scale inserts.

INSERT INTO SELECTIndexingdata migration
0 likes · 7 min read
When INSERT INTO SELECT Breaks MySQL: A Cautionary Data‑Migration Story
Architect
Architect
Mar 2, 2021 · Databases

SQL Query Optimization for Large-Scale MES Reporting Using Stored Procedures

An in-depth case study describes how to dramatically improve the performance of a massive MES reporting query—reducing execution from over half an hour to seconds—by analyzing inefficient SQL, adding proper indexes, avoiding full table scans, redesigning with stored procedures, temporary tables, and selective use of NOLOCK.

IndexingMESPerformance Optimization
0 likes · 22 min read
SQL Query Optimization for Large-Scale MES Reporting Using Stored Procedures
ITPUB
ITPUB
Feb 18, 2021 · Databases

52 Proven SQL Performance Optimization Techniques to Supercharge Your Queries

This article compiles fifty‑two practical SQL tuning strategies—including proper indexing, query rewriting, avoiding full table scans, using temporary tables, managing locks, and choosing the right storage engine—to help developers dramatically improve database query performance and scalability.

IndexingQuery TuningSQL
0 likes · 22 min read
52 Proven SQL Performance Optimization Techniques to Supercharge Your Queries
Code Ape Tech Column
Code Ape Tech Column
Feb 16, 2021 · Databases

52 Proven SQL Optimization Techniques to Supercharge Your Queries

This article compiles 52 practical SQL performance‑tuning strategies—including indexing rules, query‑writing shortcuts, join optimizations, lock handling, temporary tables, storage‑engine choices, data‑type recommendations, backup tips, and useful code examples—to help developers dramatically improve query speed and overall database efficiency.

IndexingOptimizationQuery Tuning
0 likes · 20 min read
52 Proven SQL Optimization Techniques to Supercharge Your Queries
php Courses
php Courses
Feb 9, 2021 · Databases

Impact of Quoting String Primary Keys on MySQL Query Performance

Testing shows that omitting single quotes around a string primary key in MySQL queries can cause a full table scan and increase execution time by about 100‑fold, while quoting the value enables index usage and dramatically improves performance, a difference that disappears when the column is an INT.

IndexingQuery OptimizationSQL
0 likes · 4 min read
Impact of Quoting String Primary Keys on MySQL Query Performance
vivo Internet Technology
vivo Internet Technology
Jan 20, 2021 · Databases

MySQL Deadlock Investigation Record

The article documents a MySQL 5.6.35 deadlock occurring during inventory deduction, explains how Index Merge caused inconsistent lock ordering across multiple indexes, and offers solutions such as forcing specific indexes, disabling Index Merge, adding composite indexes, or restructuring the SQL to prevent the deadlock.

DeadlockIndexingdatabase
0 likes · 13 min read
MySQL Deadlock Investigation Record
Full-Stack Internet Architecture
Full-Stack Internet Architecture
Jan 17, 2021 · Databases

Comprehensive Overview of MySQL Architecture, Logs, Indexes, Transactions, Locks, MVCC, Buffer Pool, and Optimization

This article provides an in‑depth guide to MySQL internals, covering the SQL execution process, server and storage engine layers, binlog/redo/undo logs, index structures, transaction isolation levels, lock types, MVCC implementation, buffer pool management, and practical optimization techniques.

IndexingOptimizationTransactions
0 likes · 31 min read
Comprehensive Overview of MySQL Architecture, Logs, Indexes, Transactions, Locks, MVCC, Buffer Pool, and Optimization
ITPUB
ITPUB
Jan 16, 2021 · Databases

Why a Missing Index Parameter Crashed Our Production DB—and How to Fix It

During a production outage, a high‑volume SQL query without a required leftmost index field caused full table scans that saturated CPU, prompting a temporary index fix and revealing a deeper bug involving missing @NotNull validation and improper parameter handling, highlighting MySQL’s left‑most index rule.

IndexingJavaSQL performance
0 likes · 9 min read
Why a Missing Index Parameter Crashed Our Production DB—and How to Fix It
Java Interview Crash Guide
Java Interview Crash Guide
Jan 9, 2021 · Databases

Master Elasticsearch Performance: Practical Tuning Tips for Faster Clusters

This guide consolidates everyday Elasticsearch tuning techniques—covering configuration file tweaks, system‑level settings, and usage‑level optimizations such as memory locking, discovery settings, fault detection, queue sizing, translog handling, bulk indexing, shard management, and disk I/O—to help you build a stable, high‑throughput search cluster.

Cluster OptimizationElasticsearchIndexing
0 likes · 18 min read
Master Elasticsearch Performance: Practical Tuning Tips for Faster Clusters
Code Ape Tech Column
Code Ape Tech Column
Jan 8, 2021 · Databases

Database Standards and Guidelines for Backend Development

This article presents a comprehensive set of mandatory and recommended conventions for MySQL table creation, SQL writing, and index design, aiming to improve backend development efficiency, reduce erroneous DDL and queries, and enhance overall database performance.

Best PracticesIndexingSQL Guidelines
0 likes · 11 min read
Database Standards and Guidelines for Backend Development
Architecture Digest
Architecture Digest
Jan 2, 2021 · Databases

52 SQL Query Performance Optimization Strategies

This article presents a comprehensive list of 52 practical SQL optimization techniques, covering index usage, query rewriting, avoiding full table scans, proper data types, transaction handling, backup strategies, and other best practices to improve database performance and scalability.

IndexingOptimizationperformance
0 likes · 18 min read
52 SQL Query Performance Optimization Strategies
Java Architect Essentials
Java Architect Essentials
Dec 31, 2020 · Databases

MySQL Single Table Optimization, Sharding, Partitioning, and Scaling Techniques

When a MySQL table grows large, performance degrades sharply, so this guide explains single‑table tuning, proper field choices, index strategies, query best practices, engine differences, system parameters, hardware upgrades, read‑write splitting, caching layers, table partitioning, vertical and horizontal sharding, and how to choose suitable sharding solutions.

DatabaseScalingIndexingPartitioning
0 likes · 25 min read
MySQL Single Table Optimization, Sharding, Partitioning, and Scaling Techniques
Architect
Architect
Dec 30, 2020 · Databases

Overview of Database System Design

This article provides a comprehensive overview of database system design, covering its historical evolution, classification of relational and NoSQL databases, key architectural patterns, consistency models, indexing techniques, storage formats, compression methods, and practical considerations for selecting the right database solution.

DesignIndexingNoSQL
0 likes · 43 min read
Overview of Database System Design
JD Tech Talk
JD Tech Talk
Dec 30, 2020 · Databases

Architecture and Application Practice of JD Urban Spatio-Temporal Data Engine (JUST)

The presentation details the design, implementation, and real‑world applications of the JD Urban Spatio‑Temporal Data Engine (JUST), a distributed, scalable database that handles massive, complex spatio‑temporal data with novel storage, indexing, and query techniques, demonstrating high performance and ease of use across smart‑city scenarios.

GISIndexingUrban Computing
0 likes · 26 min read
Architecture and Application Practice of JD Urban Spatio-Temporal Data Engine (JUST)
MaGe Linux Operations
MaGe Linux Operations
Dec 28, 2020 · Backend Development

Mastering Elasticsearch: Core Concepts and Indexing Workflow Explained

This article introduces Elasticsearch’s core concepts—including clusters, node roles, documents, mappings, and shards—and walks through the complete indexing workflow from client request to replica synchronization, highlighting key settings, routing calculations, and the role of refresh and flush operations.

ClusterDistributed SystemsElasticsearch
0 likes · 13 min read
Mastering Elasticsearch: Core Concepts and Indexing Workflow Explained
Architect
Architect
Dec 27, 2020 · Big Data

Optimizing Billion‑Scale Hive Queries: Partitioning, Indexing, Bucketing, Active‑User Segmentation, and Data Structure Refactoring

This article walks through the challenges of querying a 300‑billion‑row Hive table, analyzes why traditional partitioning, indexing, and bucketing fall short, and presents a practical solution that combines active‑user segmentation and a redesigned array‑based data model to cut query time from hours to minutes.

Data ModelingData PartitioningHive
0 likes · 10 min read
Optimizing Billion‑Scale Hive Queries: Partitioning, Indexing, Bucketing, Active‑User Segmentation, and Data Structure Refactoring
Liangxu Linux
Liangxu Linux
Dec 26, 2020 · Databases

Master MySQL Performance: Practical Soft and Hard Optimization Techniques

This guide explains how to boost MySQL performance through soft optimizations like query analysis, index usage, and table restructuring, as well as hard optimizations involving hardware upgrades, configuration tuning, sharding, read‑write splitting, and cache clustering.

CacheIndexingPerformance Tuning
0 likes · 8 min read
Master MySQL Performance: Practical Soft and Hard Optimization Techniques
Code Ape Tech Column
Code Ape Tech Column
Dec 23, 2020 · Databases

Master MySQL Indexes: Why B+Tree Outperforms Other Structures

This article explains how proper index creation boosts MySQL query performance, detailing the mechanics of indexes, why B+Tree is chosen over binary and balanced trees, the storage differences between MyISAM and InnoDB, and practical guidelines for designing effective single‑column, composite, and covering indexes.

B+ TreeIndexingSQL
0 likes · 19 min read
Master MySQL Indexes: Why B+Tree Outperforms Other Structures
Big Data Technology & Architecture
Big Data Technology & Architecture
Dec 13, 2020 · Big Data

Elasticsearch Write, Read, Search Processes and Performance Tuning Guide

This article explains Elasticsearch's data ingestion, retrieval, and search workflows, details the underlying indexing mechanisms, and provides comprehensive system‑level, shard‑level, and query‑level tuning recommendations—including configuration snippets and best‑practice strategies for high‑throughput and low‑latency deployments.

Cluster ConfigurationElasticsearchIndexing
0 likes · 20 min read
Elasticsearch Write, Read, Search Processes and Performance Tuning Guide
Big Data Technology & Architecture
Big Data Technology & Architecture
Dec 10, 2020 · Databases

MySQL Performance Optimization, Architecture, and High‑Availability Guide

This comprehensive guide covers the factors that impact MySQL server performance, strategies for handling large tables and transactions, hardware and OS tuning, storage‑engine selection, replication modes, high‑availability architectures, indexing techniques, and practical SQL query‑optimization methods.

HighAvailabilityIndexingOptimization
0 likes · 38 min read
MySQL Performance Optimization, Architecture, and High‑Availability Guide
ITFLY8 Architecture Home
ITFLY8 Architecture Home
Dec 3, 2020 · Databases

Essential MySQL Design & Development Standards for Reliable Databases

This guide outlines comprehensive MySQL best‑practice standards covering naming conventions, storage engine selection, charset usage, table and column comments, size limits, partitioning, hot‑cold data separation, index design, SQL coding rules, and operational safeguards to ensure performant, maintainable, and secure database systems.

Best PracticesIndexingSQL standards
0 likes · 22 min read
Essential MySQL Design & Development Standards for Reliable Databases
Tencent Cloud Developer
Tencent Cloud Developer
Dec 2, 2020 · Big Data

WeChat Pay Log System at Scale: Practices with Hermes

WeChat Pay’s Hermes‑based log system ingests trillions of entries daily, storing petabytes across a 200‑node HDFS cluster with four‑nine availability, while LSM‑style writes, separate inverted indexes and hot‑cold tiering cut memory, disk and cost by up to 70 % and keep 95 % of queries under five seconds.

HDFSHermesIndexing
0 likes · 7 min read
WeChat Pay Log System at Scale: Practices with Hermes
JavaEdge
JavaEdge
Dec 1, 2020 · Backend Development

How Kafka’s OffsetIndex and TimeIndex Optimize Message Retrieval

This article explains Kafka’s internal index files—OffsetIndex and TimeIndex—including their file formats, how they store relative offsets and timestamps, the space‑saving optimizations, the processes for appending, truncating, and looking up entries, and best‑practice cautions for handling these indexes.

IndexingKafkaOffsetIndex
0 likes · 8 min read
How Kafka’s OffsetIndex and TimeIndex Optimize Message Retrieval
Programmer DD
Programmer DD
Nov 26, 2020 · Databases

Unveiling Elasticsearch: Inside Nodes, Shards, and Lucene’s Inverted Index

This article explains Elasticsearch’s internal architecture, from cloud clusters and nodes to shards and Lucene’s inverted index, covering indexing, storage structures, query processing, caching, scaling, routing, and real‑world request handling, with detailed diagrams and examples.

IndexingLuceneShards
0 likes · 13 min read
Unveiling Elasticsearch: Inside Nodes, Shards, and Lucene’s Inverted Index
php Courses
php Courses
Nov 20, 2020 · Databases

Technical Interview Q&A: MySQL, Indexing, Design Patterns, Algorithms, Networking, and Concurrency

This article compiles a series of technical interview questions and answers covering MySQL query best practices, index optimization, B+‑tree storage, binlog recovery, master‑slave replication, design pattern usage in Laravel, a PHP binary‑search insertion algorithm, transport‑layer protocols, TCP three‑handshake, and concurrency control strategies.

Design PatternsIndexingdatabase
0 likes · 9 min read
Technical Interview Q&A: MySQL, Indexing, Design Patterns, Algorithms, Networking, and Concurrency
MaGe Linux Operations
MaGe Linux Operations
Nov 19, 2020 · Backend Development

Supercharging Elasticsearch: Practical Index & Search Optimizations for Billion-Row Queries

This article shares practical Elasticsearch and Lucene optimization techniques—including index structure tuning, shard routing, DocValues management, and query pagination—to achieve sub‑second search performance on datasets exceeding a billion records while supporting multi‑year historical queries.

ElasticsearchIndexingLucene
0 likes · 13 min read
Supercharging Elasticsearch: Practical Index & Search Optimizations for Billion-Row Queries
DataFunTalk
DataFunTalk
Nov 8, 2020 · Backend Development

Design and Optimization of an In-Memory Search Engine

This article shares the author's exploration of in‑memory search engine design, covering system understanding, core architecture, thread and task models, intersection algorithms, lookup optimizations, and componentization, aiming to fill the scarce documentation on memory‑based retrieval engines.

IndexingQuery ProcessingTask Scheduling
0 likes · 48 min read
Design and Optimization of an In-Memory Search Engine
DevOps Cloud Academy
DevOps Cloud Academy
Nov 7, 2020 · Databases

How to Use Elasticsearch Search Templates for Batch Terms Queries

This article explains why a terms query with an array fails inside an Elasticsearch search template, demonstrates the correct Mustache‑based template syntax, and provides step‑by‑step code examples for defining the index, inserting data, creating the template, and executing the query.

DSLElasticsearchIndexing
0 likes · 8 min read
How to Use Elasticsearch Search Templates for Batch Terms Queries
Fulu Network R&D Team
Fulu Network R&D Team
Nov 2, 2020 · Databases

Optimizing Large MySQL Tables on Alibaba Cloud: Design, Indexing, PolarDB Migration, X‑Engine Partitioning, and Parallel Query

This article details how to improve performance of massive MySQL tables on Alibaba Cloud by redesigning schemas and indexes, migrating to PolarDB with read‑write separation, partitioning historical data using the X‑Engine storage engine, and leveraging PolarDB's parallel query capabilities for faster analytics.

IndexingParallel QueryPartitioning
0 likes · 12 min read
Optimizing Large MySQL Tables on Alibaba Cloud: Design, Indexing, PolarDB Migration, X‑Engine Partitioning, and Parallel Query
php Courses
php Courses
Oct 30, 2020 · Big Data

Introduction to Elasticsearch and Its Integration with Laravel

This article explains Elasticsearch's foundation on Lucene, compares its concepts to MySQL, describes inverted indexing, and provides a step‑by‑step guide for installing, configuring, and using the basemkhirat/elasticsearch Laravel plugin with code examples and tips for Chinese analysis.

BackendElasticsearchIndexing
0 likes · 4 min read
Introduction to Elasticsearch and Its Integration with Laravel
Open Source Linux
Open Source Linux
Oct 23, 2020 · Databases

Boost MySQL Performance: 8 Proven Query Optimization Techniques

This article explores eight common MySQL performance pitfalls—including inefficient LIMIT usage, implicit type conversion, subquery updates, mixed sorting, EXISTS clauses, condition push‑down, early result narrowing, and intermediate result push‑down—and provides rewritten SQL examples that dramatically reduce execution time.

CTEIndexingQuery Optimization
0 likes · 13 min read
Boost MySQL Performance: 8 Proven Query Optimization Techniques
Architect
Architect
Oct 22, 2020 · Databases

Understanding MySQL Index Types, Structures, and Best Practices

This article explains the various MySQL index implementations—including B‑Tree, B+Tree, hash, clustered, non‑clustered, and covering indexes—illustrates their internal structures with diagrams, provides SQL examples, and offers practical guidelines for selecting and using indexes efficiently.

B-TreeIndexingmysql
0 likes · 13 min read
Understanding MySQL Index Types, Structures, and Best Practices
ITFLY8 Architecture Home
ITFLY8 Architecture Home
Oct 2, 2020 · Databases

Essential MySQL Optimization Tips: From EXPLAIN to Advanced Index Strategies

This article presents a comprehensive guide to MySQL performance tuning, covering the use of EXPLAIN, proper handling of IN clauses, selective column queries, limit usage, index best practices, avoiding costly operations, pagination techniques, full‑text search, join optimization, and advanced directives such as FORCE INDEX and STRAIGHT_JOIN.

IndexingQuery OptimizationSQL
0 likes · 12 min read
Essential MySQL Optimization Tips: From EXPLAIN to Advanced Index Strategies
Top Architect
Top Architect
Sep 19, 2020 · Databases

MySQL Performance Optimization: Design, Indexing, Partitioning, and Migration Strategies

This article analyzes a severe MySQL performance issue caused by massive user‑access logs, then presents three solution paths—optimizing the existing database, migrating to a compatible high‑performance database, and adopting big‑data technologies—detailing design best practices, indexing, partitioning, sharding, and cloud‑native options to restore query speed and scalability.

Cloud DatabaseIndexingPartitioning
0 likes · 18 min read
MySQL Performance Optimization: Design, Indexing, Partitioning, and Migration Strategies
Architecture Digest
Architecture Digest
Sep 18, 2020 · Databases

Optimizing MySQL RDS for Large‑Scale User Activity Logs: Design, Indexing, Partitioning, and Migration Strategies

This article analyzes the performance problems of a massive MySQL 5.6 RDS table storing billions of user‑activity records and presents three practical solutions—optimizing the existing schema and queries, migrating to a MySQL‑compatible high‑performance database, and adopting a big‑data platform—detailing design, indexing, partitioning, sharding, and cloud‑native options.

Cloud DatabaseIndexingPartitioning
0 likes · 15 min read
Optimizing MySQL RDS for Large‑Scale User Activity Logs: Design, Indexing, Partitioning, and Migration Strategies
Java Architect Essentials
Java Architect Essentials
Sep 17, 2020 · Databases

Optimizing Large-Scale SQL Queries with Stored Procedures and Indexing

The article details how a half‑hour SSRS report query on a massive MES database was transformed into a sub‑second operation by analyzing the original SQL, eliminating full table scans, adding proper indexes, using temporary tables and a well‑designed stored procedure, while also discussing common pitfalls and best‑practice tips for high‑performance database querying.

IndexingPerformance TuningSQL
0 likes · 20 min read
Optimizing Large-Scale SQL Queries with Stored Procedures and Indexing
Programmer DD
Programmer DD
Sep 14, 2020 · Databases

Master MySQL Performance: Practical Tips for Scaling and Optimization

This article explores MySQL performance limits, concurrency settings, query‑time best practices, table design choices, index classifications, and a comprehensive set of SQL optimizations—including batch processing, operator rewrites, and pagination tricks—to help developers eliminate slow queries and build scalable database solutions.

IndexingOptimizationSQL
0 likes · 15 min read
Master MySQL Performance: Practical Tips for Scaling and Optimization
ITPUB
ITPUB
Sep 9, 2020 · Databases

How to Speed Up Massive MySQL User‑Log Tables: Partitioning, Indexing, and Migration Strategies

This article examines performance problems with a 20‑million‑row MySQL user‑log table on Alibaba Cloud RDS, outlines three solution paths—optimizing the existing database, migrating to a MySQL‑compatible high‑performance service, and adopting a big‑data engine—and provides detailed guidance on schema design, indexing, partitioning, and practical SQL tweaks.

Cloud MigrationIndexingPartitioning
0 likes · 17 min read
How to Speed Up Massive MySQL User‑Log Tables: Partitioning, Indexing, and Migration Strategies
Programmer DD
Programmer DD
Sep 7, 2020 · Databases

How to Speed Up Massive MySQL Tables: Practical Optimization Strategies

This article examines why a MySQL 5.6 RDS table with tens of millions of rows becomes unbearably slow, then presents three concrete approaches—optimizing the existing database, migrating to a MySQL‑compatible service, and adopting a big‑data engine—detailing design, indexing, partitioning, sharding, and cloud options to restore performance.

IndexingPartitioningPerformance Tuning
0 likes · 18 min read
How to Speed Up Massive MySQL Tables: Practical Optimization Strategies
Code Ape Tech Column
Code Ape Tech Column
Sep 1, 2020 · Databases

Comprehensive Guide to MySQL Database Optimization: SQL Tuning, Index Strategies, and Best Practices

This article presents a thorough overview of MySQL performance tuning, covering step‑by‑step SQL optimization techniques, common pitfalls, index and table‑structure improvements, cache parameter adjustments, and practical guidelines to reduce I/O and CPU bottlenecks while maintaining data integrity.

Database PerformanceIndexingQuery Tuning
0 likes · 28 min read
Comprehensive Guide to MySQL Database Optimization: SQL Tuning, Index Strategies, and Best Practices
Top Architect
Top Architect
Aug 18, 2020 · Databases

Comprehensive Guide to MySQL SQL Optimization Techniques

This article explains why SQL optimization is a cost‑effective way to boost system performance, outlines fundamental principles, common pitfalls such as leading wildcards, IN/OR/NULL misuse, and provides practical tips on indexing, execution order, hints, DML batching, pagination, and table design for MySQL databases.

IndexingQueryOptimizationSQL
0 likes · 21 min read
Comprehensive Guide to MySQL SQL Optimization Techniques
Sohu Tech Products
Sohu Tech Products
Aug 12, 2020 · Big Data

Elasticsearch Basics: Concepts, Installation, and Search Operations

This article introduces Elasticsearch as a distributed open‑source search and analytics engine, explains its core concepts and architecture, compares it with relational databases, details installation steps, configuration, indexing, analyzers, query DSL, pagination, sorting, and provides practical examples for building search functionality.

AnalyzersElasticsearchIndexing
0 likes · 22 min read
Elasticsearch Basics: Concepts, Installation, and Search Operations
Selected Java Interview Questions
Selected Java Interview Questions
Aug 8, 2020 · Databases

MySQL Single‑Table Optimization, Sharding, and Scaling Strategies

This article explains why MySQL tables with massive row counts suffer performance degradation and provides practical guidance on single‑table optimization, field and index design, query tuning, engine selection, system parameters, hardware upgrades, read‑write splitting, caching layers, partitioning, vertical and horizontal sharding, as well as client‑side and proxy‑side sharding solutions.

IndexingPerformance Tuningdatabase optimization
0 likes · 26 min read
MySQL Single‑Table Optimization, Sharding, and Scaling Strategies
Programmer DD
Programmer DD
Aug 8, 2020 · Artificial Intelligence

How Elasticsearch Handles Write, Read, and Search: Inside the Engine

This article explains Elasticsearch's internal mechanisms for indexing, querying, and retrieving data, covering the roles of coordinating nodes, primary and replica shards, the refresh and commit cycles, near‑real‑time search, and the underlying Lucene inverted index.

Data IngestionElasticsearchIndexing
0 likes · 12 min read
How Elasticsearch Handles Write, Read, and Search: Inside the Engine
Top Architect
Top Architect
Aug 3, 2020 · Databases

Case Study: Insert‑Into‑Select Migration Failure and Resolution in MySQL

This article analyzes a real‑world MySQL incident where using INSERT INTO SELECT to migrate billions of rows caused table‑wide locking and payment failures, explains the underlying locking behavior, and presents a solution using proper indexing and FORCE INDEX to avoid full‑table scans.

INSERT INTO SELECTIndexingSQL performance
0 likes · 7 min read
Case Study: Insert‑Into‑Select Migration Failure and Resolution in MySQL
Laravel Tech Community
Laravel Tech Community
Aug 2, 2020 · Databases

MySQL Database Design and SQL Writing Standards

This document outlines comprehensive MySQL database design standards and SQL writing guidelines, covering naming conventions, character sets, table and column specifications, index design, partitioning, data types, transaction handling, and prohibited statements to ensure stable, performant, and maintainable production databases.

IndexingPartitioningSQL standards
0 likes · 18 min read
MySQL Database Design and SQL Writing Standards