BestHub
Sign in
Tagged articles

database optimization

509 articles · Page 2 of 6
Architect
Architect
Nov 4, 2024 · Databases

SQL Refactoring Case Study: Optimizing Complex Queries in the JDL Routing System

This article examines how uncontrolled growth of SQL code leads to performance and maintainability issues, then demonstrates a step‑by‑step refactoring of a complex routing‑system query—formatting, layer decomposition, merging, condition push‑down, join optimization, and testing—to achieve clearer, faster, and more reliable database operations.

Query TuningSQLdatabase optimization
0 likes · 14 min read
SQL Refactoring Case Study: Optimizing Complex Queries in the JDL Routing System
JD Cloud Developers
JD Cloud Developers
Nov 4, 2024 · Databases

How Redis LFU Detects Hot Keys and Enables Client‑Side Caching

This article explains Redis 4.0's LFU‑based hot‑key detection mechanism, the probabilistic counter algorithm, its configuration options, and how Redis 6.0 introduces client‑side caching with tracking and broadcasting modes to keep client data consistent with the server.

Client Side CachingHot Key DetectionLFU
0 likes · 13 min read
How Redis LFU Detects Hot Keys and Enables Client‑Side Caching
Baidu Geek Talk
Baidu Geek Talk
Oct 21, 2024 · Databases

TDE-ClickHouse Optimization Practice at Baidu MEG: Query Performance, Data Import, and Distributed Architecture

Baidu MEG’s TDE‑ClickHouse optimization in the Turing 3.0 ecosystem boosts query speed up to 10×, halves latency, enables billion‑row bulk imports in under two hours, and migrates to a cloud‑native, ZooKeeper‑free architecture supporting 350 k CPU cores, 10 PB storage, and sub‑3‑second responses for 150 k daily BI queries.

Baidu MEGClickHouseCloud Native
0 likes · 19 min read
TDE-ClickHouse Optimization Practice at Baidu MEG: Query Performance, Data Import, and Distributed Architecture
Efficient Ops
Efficient Ops
Oct 13, 2024 · Databases

Why Your MySQL Queries Are Slow and How to Fix Them with Indexes, ES & HBase

This article explains why MySQL queries become slow—covering index misuse, MDL locks, flush waits, large‑table bottlenecks, and read/write splitting—then shows how ElasticSearch’s inverted index and HBase’s column‑family design can complement MySQL for faster search and scalable storage.

HBaseIndexingMySQL
0 likes · 20 min read
Why Your MySQL Queries Are Slow and How to Fix Them with Indexes, ES & HBase
MaGe Linux Operations
MaGe Linux Operations
Oct 11, 2024 · Databases

Comprehensive MySQL Optimization Guide: Hardware, OS, Config & Query Tuning

This guide details a step‑by‑step MySQL performance optimization strategy covering hardware selection, disk I/O planning, operating‑system tweaks, vertical and horizontal sharding, my.cnf parameter tuning, query cache configuration, resource limits, table and index caching, and practical query‑level best practices.

ConfigurationMySQLQuery Cache
0 likes · 18 min read
Comprehensive MySQL Optimization Guide: Hardware, OS, Config & Query Tuning
Java Tech Enthusiast
Java Tech Enthusiast
Oct 3, 2024 · Databases

MySQL Large Table Data Deletion Optimization Strategy

To efficiently purge old user‑operation logs while retaining the most recent three months and preserving type‑c entries, the article recommends abandoning offset pagination and MyBatis PageHelper, using primary‑key‑based pagination with a tracked startId, in‑memory filtering, intelligent startId recalculation, and optional sleep intervals to avoid database overload.

MySQLdata cleanupdatabase optimization
0 likes · 6 min read
MySQL Large Table Data Deletion Optimization Strategy
Architecture Digest
Architecture Digest
Sep 23, 2024 · Databases

Evaluating and Optimizing Large MySQL Tables: Partitioning, Sharding, and Hot‑Cold Archiving Strategies

This article explains how to assess MySQL table size using table capacity, disk usage, and instance resources, describes why large tables degrade performance, and presents three practical solutions—partitioning, sharding (horizontal/vertical), and hot‑cold data archiving—along with their trade‑offs and implementation tips.

B+TreeHotColdArchivingMySQL
0 likes · 15 min read
Evaluating and Optimizing Large MySQL Tables: Partitioning, Sharding, and Hot‑Cold Archiving Strategies
Open Source Tech Hub
Open Source Tech Hub
Sep 19, 2024 · Databases

Boost PHP App Speed: Advanced Database Performance Optimization Strategies

Optimizing database performance is crucial for PHP applications, and this comprehensive guide explores advanced strategies—including efficient query design, indexing, caching, connection pooling, batch processing, sharding, and partitioning—to maximize speed, scalability, and user experience while reducing latency and server load.

PHPcachingdatabase optimization
0 likes · 6 min read
Boost PHP App Speed: Advanced Database Performance Optimization Strategies
vivo Internet Technology
vivo Internet Technology
Sep 11, 2024 · Databases

Redis Cluster Slot Migration Optimization Based on Master-Slave Replication Mechanism

Vivo’s storage team redesigned Redis cluster slot migration by disguising the target as a slave and using master‑slave replication with progressive loading, which eliminates ask‑move round trips, reduces latency spikes, consolidates topology updates, and ensures stable access and high availability during horizontal scaling.

ClusterRedisSlot Migration
0 likes · 14 min read
Redis Cluster Slot Migration Optimization Based on Master-Slave Replication Mechanism
macrozheng
macrozheng
Aug 29, 2024 · Databases

How to Safely Perform Billion-Row MySQL Updates Without Overloading Replication

This article explains why a direct full‑table UPDATE on a massive MySQL table can overwhelm binlog replication, explores the pitfalls of deep pagination and IN‑clauses, and presents a batch‑processing strategy using NO_CACHE and FORCE INDEX to achieve efficient, low‑impact updates.

Batch Updatebinlogdatabase optimization
0 likes · 8 min read
How to Safely Perform Billion-Row MySQL Updates Without Overloading Replication
Architect
Architect
Aug 13, 2024 · Databases

Optimizing HBase for a Large‑Scale Content Platform: Selection, Performance Tuning, and Best Practices

This article examines why the unified content platform switched from MongoDB to HBase, outlines HBase’s high‑performance, scalability, and consistency features, and details four optimization techniques—including cluster upgrade, connection pooling, column‑read strategy, and compaction tuning—that significantly improved read/write latency and operational stability.

HBaseNoSQLdatabase optimization
0 likes · 15 min read
Optimizing HBase for a Large‑Scale Content Platform: Selection, Performance Tuning, and Best Practices
php Courses
php Courses
Aug 2, 2024 · Backend Development

Optimizing WordPress Database Performance with Advanced PHP Techniques

This comprehensive guide explains how to boost WordPress site speed and SEO by applying PHP‑based strategies such as query optimization, regular database maintenance, custom tables, option‑table tuning, indexing, and caching, complete with practical code examples.

SQLWordPresscaching
0 likes · 9 min read
Optimizing WordPress Database Performance with Advanced PHP Techniques
JD Tech Talk
JD Tech Talk
Jul 30, 2024 · Databases

Database Performance Optimization and Governance for a High‑Load Application

This technical report analyzes a high‑traffic MySQL deployment with three servers, detailing disk usage, table space, QPS and slow‑SQL issues, and presents a comprehensive governance plan that includes data migration, read‑traffic offloading to replicas, MyBatis interceptor implementation, JSF monitoring, and Python automation to reduce disk pressure and improve query performance.

Data MigrationMySQLSlow Query
0 likes · 18 min read
Database Performance Optimization and Governance for a High‑Load Application
Top Architect
Top Architect
Jul 24, 2024 · Databases

Top 20 SQL Optimization Techniques for Better Query Performance

This article presents twenty practical SQL optimization techniques—including index usage, selective column queries, avoiding functions in WHERE clauses, replacing subqueries with joins, limiting result sets, using EXISTS, batch inserts, covering indexes, proper data types, pagination, and transaction management—to significantly improve query performance and database efficiency.

SQLbest practicesdatabase optimization
0 likes · 11 min read
Top 20 SQL Optimization Techniques for Better Query Performance
Senior Tony
Senior Tony
Jul 20, 2024 · Databases

How MySQL’s Skip Scan Defies the Leftmost Prefix Rule

This article explains MySQL’s composite index leftmost‑prefix rule, demonstrates how the Skip Scan Range Access Method introduced in MySQL 8.0.13 can use indexes without following the rule, provides step‑by‑step examples, code snippets, and discusses its implementation, benefits, and limitations.

Composite IndexMySQLSQL
0 likes · 10 min read
How MySQL’s Skip Scan Defies the Leftmost Prefix Rule
vivo Internet Technology
vivo Internet Technology
Jul 10, 2024 · Databases

HBase Optimization Practice in Vivo's Unified Content Platform

Vivo's unified content platform replaced its unwieldy 60 TB MongoDB store with HBase, then upgraded the cluster, introduced table‑specific connection pools, column‑only reads, tuned compaction, and leveraged multi‑version cells, cutting response times from seconds to under ten milliseconds and dramatically lowering operational costs while boosting read/write performance.

Compaction OptimizationHBaseHBase Performance Tuning
0 likes · 16 min read
HBase Optimization Practice in Vivo's Unified Content Platform
Senior Tony
Senior Tony
Jul 10, 2024 · Databases

When to Use MySQL Hash Join vs. Nested Loop: A Practical Guide

This article explains MySQL's hash join algorithm, compares it with nested‑loop joins, shows how to read execution steps and EXPLAIN output, discusses extended hash‑join scenarios, and weighs the trade‑offs of performing multi‑table joins inside the database versus in application code.

Hash JoinJoin AlgorithmsMySQL
0 likes · 8 min read
When to Use MySQL Hash Join vs. Nested Loop: A Practical Guide
Wukong Talks Architecture
Wukong Talks Architecture
Jul 8, 2024 · Backend Development

Design and Evolution of Ctrip Ticket Booking System for Flash‑Sale Events

This article analyzes the challenges faced by Ctrip's ticket‑booking transaction system during high‑traffic flash‑sale events and presents a series of architectural optimizations—including Redis caching, database load mitigation, supplier‑system safeguards, and fine‑grained traffic‑control strategies—to achieve stable, consistent, and fast performance under extreme concurrency.

Flash SaleRedisbackend
0 likes · 15 min read
Design and Evolution of Ctrip Ticket Booking System for Flash‑Sale Events
dbaplus Community
dbaplus Community
Jul 7, 2024 · Databases

Why MySQL 5.7 Still Beats 8.x in Real‑World Benchmarks – A Deep Dive

Percona’s analysis of MySQL version adoption reveals a slow migration to 8.x, prompting extensive performance tests that compare default and tuned configurations of MySQL 5.7, 8.0.36, 8.4, Percona Server, and MariaDB using sysbench and TPC‑C benchmarks, with detailed results showing consistent performance regressions in newer releases.

MySQLPerconaSysbench
0 likes · 11 min read
Why MySQL 5.7 Still Beats 8.x in Real‑World Benchmarks – A Deep Dive
Top Architect
Top Architect
Jun 16, 2024 · Databases

20 Essential SQL Optimization Techniques for Better Query Performance

This article presents twenty practical SQL optimization techniques—including index usage, selective column queries, avoiding functions in WHERE clauses, replacing subqueries with joins, limiting result sets, using prepared statements, proper data types, and transaction handling—to improve query performance and overall database efficiency.

QueriesSQLdatabase optimization
0 likes · 11 min read
20 Essential SQL Optimization Techniques for Better Query Performance
Su San Talks Tech
Su San Talks Tech
May 18, 2024 · Databases

Boost Large Table Queries with Query Separation: When and How to Implement

This article explains query separation as a strategy to accelerate slow large‑table queries by duplicating data to a dedicated query store, outlines when to adopt it, compares synchronous, asynchronous, and binlog approaches, discusses storage choices such as MongoDB, HBase, Elasticsearch, and addresses consistency and MQ challenges.

Asynchronous ReplicationElasticsearchMessage Queue
0 likes · 10 min read
Boost Large Table Queries with Query Separation: When and How to Implement
macrozheng
macrozheng
Apr 26, 2024 · Databases

Boost MySQL Insert Performance with Batch Techniques and MyISAM

This article explains how to dramatically improve MySQL insert speed by adjusting database settings, choosing the MyISAM engine, and applying batch insertion methods such as concatenated SQL, transaction‑wrapped inserts, and MyBatis‑Flex saveBatch, complete with test code and performance results.

Batch InsertInsert PerformanceMyBatis
0 likes · 10 min read
Boost MySQL Insert Performance with Batch Techniques and MyISAM
IT Services Circle
IT Services Circle
Apr 18, 2024 · Databases

Improving MySQL Insert Performance with Batch Operations and MyBatis Flex

This article explains how to boost MySQL insertion speed by adjusting server hardware, tuning database parameters, choosing the MyISAM engine, and especially applying three batch‑insert techniques—SQL concatenation, MyBatis‑Flex saveBatch, and manual transaction batching—while showing test results and configuration tips.

Batch InsertMyBatisMySQL
0 likes · 8 min read
Improving MySQL Insert Performance with Batch Operations and MyBatis Flex
Zhuanzhuan Tech
Zhuanzhuan Tech
Apr 17, 2024 · Databases

Database Storage Optimization and Migration Strategy for ZhaiZhai Financial System

This article presents a comprehensive analysis of the ZhaiZhai financial system's growing data volume and slow‑query issues, evaluates four storage solutions—including sharding, hot‑cold separation, TiDB, and OceanBase—selects TiDB as the optimal choice, and details the migration steps and Elasticsearch integration to improve performance and scalability.

Data MigrationTiDBdatabase optimization
0 likes · 13 min read
Database Storage Optimization and Migration Strategy for ZhaiZhai Financial System
Mike Chen's Internet Architecture
Mike Chen's Internet Architecture
Apr 17, 2024 · Backend Development

Mastering High Concurrency: Boost Your Backend Performance

This article explains what high concurrency is, why it matters for large‑scale systems, and presents practical techniques such as distributed caching, load balancing, database optimization, traffic shaping, and distributed architecture to dramatically improve a backend's ability to handle massive simultaneous requests.

Backend PerformanceLoad Balancingdatabase optimization
0 likes · 9 min read
Mastering High Concurrency: Boost Your Backend Performance
Sanyou's Java Diary
Sanyou's Java Diary
Apr 15, 2024 · Databases

30 Proven SQL Optimization Tips to Boost Query Performance

This article presents thirty practical SQL optimization techniques, ranging from avoiding SELECT * and using LIMIT 1 to proper indexing, join strategies, and query rewriting, each illustrated with code examples and clear explanations to help developers write faster, more efficient database queries.

IndexingQuery TuningSQL
0 likes · 28 min read
30 Proven SQL Optimization Tips to Boost Query Performance
IT Niuke
IT Niuke
Apr 13, 2024 · Databases

JOIN vs IN in MySQL: Performance Comparison and Best Practices

This article compares MySQL's JOIN and IN constructs, showing that IN can be slightly faster for simple filters while JOIN offers greater flexibility for complex, multi‑table queries, and it outlines when to use each approach along with additional optimization tips such as indexing and engine selection.

JOINMySQLdatabase optimization
0 likes · 7 min read
JOIN vs IN in MySQL: Performance Comparison and Best Practices
Bilibili Tech
Bilibili Tech
Mar 22, 2024 · Backend Development

Design and Evolution of Incremental Indexing for Advertising Retrieval Systems

The article describes how an advertising retrieval system evolved from serial to parallel full builds and finally to a hybrid incremental indexing approach that records direct entity relationships during assembly, enabling fast reverse‑lookup of changed units via inverted indexes, reducing database load, latency, and rebuild overhead.

Data Pipelineadvertising systembackend development
0 likes · 20 min read
Design and Evolution of Incremental Indexing for Advertising Retrieval Systems
Architecture & Thinking
Architecture & Thinking
Mar 19, 2024 · Databases

Why MySQL Indexes Fail: 9 Common Pitfalls and How to Fix Them

This article enumerates the most frequent situations that cause MySQL indexes to become ineffective—such as violating the left‑most rule, using functions or expressions on indexed columns, type conversions, improper LIKE patterns, OR/IN/NOT IN usage, and inequality operators—and demonstrates how to reproduce and verify each case with sample data and SQL scripts.

IndexingMySQLSQL
0 likes · 21 min read
Why MySQL Indexes Fail: 9 Common Pitfalls and How to Fix Them
JD Retail Technology
JD Retail Technology
Feb 29, 2024 · Databases

Optimizing Large‑Scale Batch Processing for an Advertising Platform: From Query Tuning to Load‑Balanced Execution

This article presents a real‑world case study of optimizing massive batch‑processing tasks in an ad‑platform by applying query‑level improvements, cursor‑based pagination, shard‑aware batch updates, JVM‑tuned garbage collection, and distributed load‑balancing, ultimately reducing CPU usage from 80% to under 2% and cutting query‑per‑minute volume from millions to a few thousand.

Batch ProcessingCursor PaginationSharding
0 likes · 22 min read
Optimizing Large‑Scale Batch Processing for an Advertising Platform: From Query Tuning to Load‑Balanced Execution
Architect's Guide
Architect's Guide
Feb 4, 2024 · Databases

Evaluating Large MySQL Table Size and Optimizing Query Performance with Partitioning, Sharding, and Archiving

This article explains how to assess MySQL table size through table capacity, disk usage, and instance resources, illustrates the impact of B+‑tree index depth on query speed, and presents three practical solutions—table partitioning, database sharding, and hot‑cold data archiving—to improve performance.

Data ArchivingMySQLPartitioning
0 likes · 15 min read
Evaluating Large MySQL Table Size and Optimizing Query Performance with Partitioning, Sharding, and Archiving
Sohu Tech Products
Sohu Tech Products
Jan 24, 2024 · Databases

Optimizing Database Expression Evaluation with JIT Technology Using Gandiva

The article explains how database expression evaluation—especially in WHERE and SELECT clauses—can be dramatically accelerated by replacing interpreted AST traversal with Just‑In‑Time compilation using Apache Gandiva, which leverages LLVM to generate SIMD‑optimized machine code for Arrow columnar data, and discusses extensions such as timestamp, array, higher‑order functions, and UDF support.

Apache ArrowApache GandivaExpression Evaluation
0 likes · 17 min read
Optimizing Database Expression Evaluation with JIT Technology Using Gandiva
Efficient Ops
Efficient Ops
Jan 8, 2024 · Databases

Eight Proven Strategies to Supercharge Database Performance

This article outlines why databases become slow, introduces a four‑layer optimization framework, and presents eight practical solutions—including data reduction, archiving, sharding, caching, and read‑replica strategies—to help engineers systematically diagnose and resolve the majority of performance bottlenecks.

Data ArchivingNoSQLSharding
0 likes · 20 min read
Eight Proven Strategies to Supercharge Database Performance
ITPUB
ITPUB
Jan 4, 2024 · Databases

Fast CPU Performance Evaluation on Domestic Servers & OceanBase Optimization

This article explains why domestic Haiguang/Kunpeng servers lag behind Intel CPUs, presents a quick stored‑procedure method to benchmark CPU performance, details OceanBase tenant CPU specifications and tuning parameters, and offers concrete optimization techniques for high‑concurrency TP and massive‑data AP scenarios, including queuing tables, sequence cache, and index redesign.

CPU performanceOceanBasedatabase optimization
0 likes · 21 min read
Fast CPU Performance Evaluation on Domestic Servers & OceanBase Optimization
JD Cloud Developers
JD Cloud Developers
Jan 4, 2024 · Operations

How to Boost Inventory Reservation Performance by 2300% with Redis Caching

This article details a comprehensive performance‑testing case study of an inventory‑reservation system, describing the test scenarios, bottleneck analysis, Redis‑based cache redesign, re‑testing results, cache‑preheat strategies, invalid‑call reduction, and operational best practices that together increased TPS by over twenty‑three‑fold while keeping latency under 500 ms.

Load TestingPerformance Testingdatabase optimization
0 likes · 16 min read
How to Boost Inventory Reservation Performance by 2300% with Redis Caching
Huolala Tech
Huolala Tech
Dec 27, 2023 · Big Data

How HBase Compaction Tuning Boosts Performance at Scale

This article explains LSM‑Tree based HBase compaction concepts, compares Minor and Major compactions, and shares practical tuning steps—including disabling automatic major compactions, controlling merge size, leveraging off‑peak windows, and improving merge efficiency—to reduce I/O, CPU usage, and latency in production environments.

Big DataHBaseLSM Tree
0 likes · 11 min read
How HBase Compaction Tuning Boosts Performance at Scale
Code Ape Tech Column
Code Ape Tech Column
Dec 26, 2023 · Databases

Eight Major Database Optimization Strategies for Backend Engineers

This article outlines eight comprehensive database optimization approaches—reducing data volume, using space for performance, selecting appropriate storage systems, and related techniques such as data archiving, intermediate tables, serialization, sharding, distributed caching, read‑write splitting, and CQRS—to help backend engineers systematically address common performance bottlenecks.

Shardingbackend engineeringcqrs
0 likes · 23 min read
Eight Major Database Optimization Strategies for Backend Engineers
php Courses
php Courses
Dec 16, 2023 · Databases

Why EXISTS() Is More Efficient Than COUNT() in MySQL and Laravel

Using MySQL's EXISTS() function for existence checks is generally more efficient and readable than COUNT(), especially in large datasets, and Laravel provides convenient query builder methods to implement EXISTS() queries, offering performance gains over COUNT() in PHP applications.

COUNTMySQLPHP
0 likes · 6 min read
Why EXISTS() Is More Efficient Than COUNT() in MySQL and Laravel
JD Tech
JD Tech
Dec 15, 2023 · Databases

Systematic Approach to Reducing MySQL Database Storage Costs

This article presents a comprehensive, step‑by‑step methodology for cutting MySQL storage expenses—including background analysis, challenge identification, a nine‑grid systematic framework, benefit calculation, safety and stability verification, rollout, gray‑release, and rollback—demonstrating over 50% disk space reduction while managing risk.

MySQLStorage Managementcost reduction
0 likes · 14 min read
Systematic Approach to Reducing MySQL Database Storage Costs
DataFunTalk
DataFunTalk
Dec 8, 2023 · Databases

Interview with Wu Li on Database Evolution: Columnar Storage, JIT Compilation, and Push Mode

The article presents an interview with Wu Li, a research engineer at Shanghai Yanhuang Data, discussing how hardware limits have driven database evolution toward columnar storage, the adoption of Apache Arrow and Gandiva for SIMD‑enabled JIT compilation, and the shift from pull to push processing modes to improve OLAP performance.

Apache ArrowGandivadatabase optimization
0 likes · 10 min read
Interview with Wu Li on Database Evolution: Columnar Storage, JIT Compilation, and Push Mode
JavaEdge
JavaEdge
Nov 29, 2023 · Databases

How Quora Optimizes MySQL Reads, Writes, and Storage with Caching and MyRocks

This article explains how Quora reduces database load by optimizing read patterns, improving high‑QPS queries with smarter caching, shrinking table size using MyRocks, and boosting write throughput through parallel replication settings, providing concrete steps, code snippets, and real‑world results.

MyRocksMySQLRead/Write Performance
0 likes · 12 min read
How Quora Optimizes MySQL Reads, Writes, and Storage with Caching and MyRocks
Architect
Architect
Nov 18, 2023 · Databases

JOIN vs IN: Choosing the Faster MySQL Query Method

Through a series of local experiments on MySQL tables with varying data volumes, this article analyzes the performance trade‑offs between JOIN queries and IN‑list queries, detailing execution plans, PHP benchmarking, and the impact of large IN lists on query speed and reliability.

JOINMySQLPHP
0 likes · 10 min read
JOIN vs IN: Choosing the Faster MySQL Query Method
政采云技术
政采云技术
Nov 8, 2023 · Databases

Understanding MySQL ORDER BY and LIMIT: Execution Principles, Sorting Algorithms, and Pagination Optimization

This article provides a comprehensive technical analysis of how MySQL processes ORDER BY and LIMIT clauses during pagination queries, detailing index utilization, filesort mechanisms, memory-based sorting algorithms, and practical optimization strategies for resolving deep pagination performance bottlenecks.

Filesort AlgorithmMySQLQuery Performance Tuning
0 likes · 11 min read
Understanding MySQL ORDER BY and LIMIT: Execution Principles, Sorting Algorithms, and Pagination Optimization
StarRocks
StarRocks
Nov 3, 2023 · Databases

How StarRocks’ Spill to Disk Boosts Query Stability and Performance

StarRocks introduces a spill-to-disk mechanism that writes intermediate results of heavy operators to disk, freeing memory and enabling stable execution of ETL and ad‑hoc queries, while combined with materialized views it dramatically improves query success rates and delivers up to 4.35× faster performance than Spark.

Big DataMaterialized ViewsSpill
0 likes · 10 min read
How StarRocks’ Spill to Disk Boosts Query Stability and Performance
Java High-Performance Architecture
Java High-Performance Architecture
Nov 2, 2023 · Databases

How to Tackle Large MySQL Tables: Partitioning, Sharding, and Archiving Strategies

When MySQL tables grow to millions of rows, insert and query latency increase, schema changes become costly, and irrelevant data bloats storage, so this guide evaluates table size, explains why B+‑tree depth hurts performance, and presents partitioning, sharding, and hot‑cold archiving solutions with practical SQL examples.

B+TreeMySQLSharding
0 likes · 15 min read
How to Tackle Large MySQL Tables: Partitioning, Sharding, and Archiving Strategies
Aikesheng Open Source Community
Aikesheng Open Source Community
Oct 25, 2023 · Databases

Understanding and Optimizing Indexes in OceanBase

This article explains the concepts of local and global indexes in OceanBase, demonstrates how to create tables and indexes, compares six practical scenarios—including no index, local index, partitioned queries, unique and global indexes—and provides performance metrics to guide database optimization.

IndexingLocal IndexOceanBase
0 likes · 8 min read
Understanding and Optimizing Indexes in OceanBase
php Courses
php Courses
Oct 17, 2023 · Databases

Choosing and Optimizing Indexes in PHP and MySQL

This article explains the fundamentals and types of MySQL indexes, provides guidelines for selecting appropriate indexes based on query frequency, uniqueness and complexity, and presents PHP code examples for adding and using indexes, along with optimization strategies such as proper schema design, data types, and index maintenance.

IndexingMySQLPHP
0 likes · 5 min read
Choosing and Optimizing Indexes in PHP and MySQL
Architect's Guide
Architect's Guide
Oct 10, 2023 · Databases

Advantages and Disadvantages of Application‑Layer Association vs JOIN in MySQL

The article examines why using application‑layer association instead of MySQL JOIN can improve cache efficiency, reduce lock contention, simplify scaling, and enhance query performance, while also outlining scenarios where JOIN is still beneficial and offering practical alternatives for large‑scale data retrieval.

Application LayerJOINMySQL
0 likes · 6 min read
Advantages and Disadvantages of Application‑Layer Association vs JOIN in MySQL
Code Ape Tech Column
Code Ape Tech Column
Sep 16, 2023 · Backend Development

Batch Request Merging in Java to Reduce Database Connections

This article explains how to merge multiple user‑detail requests on the server side using a blocking queue, scheduled thread pool and CompletableFuture in Spring Boot, thereby converting many individual SQL calls into a single batch query, saving database connections and improving high‑concurrency performance.

Batch ProcessingCompletableFutureSpring Boot
0 likes · 13 min read
Batch Request Merging in Java to Reduce Database Connections
Senior Tony
Senior Tony
Aug 12, 2023 · Databases

Mastering SQL Optimization: 15 Practical Tips to Supercharge Your Queries

This article presents a comprehensive guide to SQL performance tuning, covering real‑world interview scenarios, deep‑pagination pitfalls, and fifteen concrete optimization techniques—including index usage, limit tricks, proper joins, batch operations, and data‑type choices—to help developers write faster, more efficient MySQL queries.

IndexingMySQLSQL
0 likes · 12 min read
Mastering SQL Optimization: 15 Practical Tips to Supercharge Your Queries
Top Architect
Top Architect
Aug 2, 2023 · Databases

Implementing Multi‑Business Product Sales Statistics Queries Using Row‑Comparison in MySQL

This article explains how to query sales statistics for multiple business units and their associated products in MySQL by preparing the environment, presenting several candidate solutions—including loop queries, OR concatenation, mixed filtering, and finally row‑comparison—and selecting the row‑comparison approach as the optimal implementation.

Dynamic SQLMySQLRow Comparison
0 likes · 8 min read
Implementing Multi‑Business Product Sales Statistics Queries Using Row‑Comparison in MySQL
Aikesheng Open Source Community
Aikesheng Open Source Community
Aug 2, 2023 · Databases

Understanding the Construction Process of Adaptive Hash Index (AHI) in MySQL 8.0.32

This article explains why MySQL's Adaptive Hash Index (AHI) can cause slow TRUNCATE operations, describes its benefits, and provides a detailed step‑by‑step walkthrough of the AHI construction process—including index counting, construction‑info counting, data‑page counting, and the final hash‑index building—using MySQL 8.0.32 source code examples.

Adaptive Hash IndexIndexingInnoDB
0 likes · 27 min read
Understanding the Construction Process of Adaptive Hash Index (AHI) in MySQL 8.0.32
Code Ape Tech Column
Code Ape Tech Column
Jul 13, 2023 · Databases

Using Row‑Row Comparison to Query Sales Statistics for Multiple Business Units and Products in MySQL

This article explains how to design a MySQL table for product sales statistics, prepares sample data, describes the business requirement of querying sales for multiple business units and their associated products, and evaluates several query strategies—looping, OR concatenation, mixed filtering, and finally row‑row comparison—highlighting why the latter is chosen as the optimal solution.

Dynamic SQLMySQLQuery Design
0 likes · 9 min read
Using Row‑Row Comparison to Query Sales Statistics for Multiple Business Units and Products in MySQL
php Courses
php Courses
Jul 1, 2023 · Databases

MySQL Queries That Cannot Use Indexes

This article explains five common MySQL query patterns—using functions, leading wildcards with LIKE, OR operators, inequality comparisons, and NULL checks—that prevent the database engine from utilizing indexes, and provides example SQL statements illustrating each case.

MySQLSQLdatabase optimization
0 likes · 3 min read
MySQL Queries That Cannot Use Indexes
Top Architect
Top Architect
Jun 30, 2023 · Databases

Optimizing MySQL LIMIT Pagination: Analysis and Solutions

This article examines why MySQL LIMIT pagination becomes slower with deeper offsets on a 500,000‑row table, demonstrates the performance impact with concrete queries, and presents three optimization strategies—including using ordered primary keys, subqueries, and join‑based approaches—to reduce scan range and improve query speed.

IndexingMySQLSQL
0 likes · 8 min read
Optimizing MySQL LIMIT Pagination: Analysis and Solutions
Su San Talks Tech
Su San Talks Tech
Jun 12, 2023 · Databases

Master MySQL Indexes: From B+ Trees to Index Merge and Optimization

This article explains MySQL InnoDB index fundamentals, covering index types, data structures, clustered and secondary indexes, covering indexes, index pushdown, index merge, cost‑based index selection, common index‑inefficiency scenarios, and practical guidelines for designing effective indexes.

B+TreeIndexInnoDB
0 likes · 31 min read
Master MySQL Indexes: From B+ Trees to Index Merge and Optimization
vivo Internet Technology
vivo Internet Technology
May 10, 2023 · Databases

Design and Optimization of a Disk‑Based KV Store Compatible with Redis on TiKV

The article details a Redis‑compatible, disk‑based KV service built atop TiKV using a compute‑storage split (Tula), describes custom key encoding and expiration mechanisms, and explains four optimization stages that introduce slot‑based hashing and adaptive concurrency to dramatically cut garbage‑collection latency while preserving write performance.

Garbage CollectionKV storeRedis compatibility
0 likes · 21 min read
Design and Optimization of a Disk‑Based KV Store Compatible with Redis on TiKV
Tencent Database Technology
Tencent Database Technology
Apr 27, 2023 · Databases

Understanding MySQL Partition Pruning: Which Partition Should Be Queried?

This article explains how MySQL implements partition pruning, describing the conditions under which the optimizer can skip irrelevant partitions, the internal data structures such as read_partitions and lock_partitions, the role of SEL_TREE and SEL_ARG, and provides a detailed example with code to illustrate the pruning process.

MySQLSQLdatabase optimization
0 likes · 15 min read
Understanding MySQL Partition Pruning: Which Partition Should Be Queried?
DataFunSummit
DataFunSummit
Apr 13, 2023 · Databases

AI-Driven and Cost-Based Index Recommendation for Slow Queries at Meituan

This article describes Meituan's collaboration with East China Normal University to improve slow‑query performance by combining traditional cost‑based index recommendation with a novel AI‑plus‑data‑driven approach, detailing the overall architecture, modeling process, experimental results, and future plans for large‑model integration.

AIIndex RecommendationMeituan
0 likes · 15 min read
AI-Driven and Cost-Based Index Recommendation for Slow Queries at Meituan
Tencent Database Technology
Tencent Database Technology
Mar 30, 2023 · Databases

TXSQL Parallel DDL: Design, Implementation, and Performance

This article explains the challenges of slow DDL operations in MySQL, introduces TXSQL’s Parallel DDL framework—including Copy, Inplace, and Instant algorithms—details their design, implementation, code examples, and performance evaluations, and compares them with other database solutions.

DDLMySQLParallel DDL
0 likes · 22 min read
TXSQL Parallel DDL: Design, Implementation, and Performance
Sanyou's Java Diary
Sanyou's Java Diary
Mar 9, 2023 · Databases

21 Essential SQL Habits to Boost Performance and Avoid Regrets

This guide presents 21 practical SQL habits covering performance tuning, safe data manipulation, and coding standards—such as using EXPLAIN, adding LIMIT to deletes, documenting tables, consistent formatting, proper indexing, and transaction handling—to help developers write more efficient, reliable, and maintainable queries.

SQLcoding standardsdatabase optimization
0 likes · 15 min read
21 Essential SQL Habits to Boost Performance and Avoid Regrets
DataFunTalk
DataFunTalk
Feb 21, 2023 · Databases

Building a Stream‑Batch Integrated Data Architecture with Apache Doris at SelectDB

This article details how SelectDB’s data technology architect designed and implemented a new stream‑batch unified data platform using Apache Doris, covering the shortcomings of the early CDH‑based architecture, the selection process, data modeling, ingestion pipelines, performance testing, operational optimizations, and future plans.

Apache DorisBatch ProcessingBig Data
0 likes · 17 min read
Building a Stream‑Batch Integrated Data Architecture with Apache Doris at SelectDB
ITPUB
ITPUB
Feb 14, 2023 · Databases

Mastering Database Read‑Write Splitting: Techniques, Use‑Cases, and Top Tools

This article provides a comprehensive overview of database read‑write splitting, detailing its benefits, core technical implementations, practical best‑practice guidelines, and a comparison of popular open‑source and commercial products that enable efficient read‑write traffic distribution.

Load BalancingMiddlewareRead‑Write Splitting
0 likes · 21 min read
Mastering Database Read‑Write Splitting: Techniques, Use‑Cases, and Top Tools
Code Ape Tech Column
Code Ape Tech Column
Jan 5, 2023 · Databases

Eight Database Optimization Strategies for Backend Engineers

This article outlines eight comprehensive database optimization techniques—including reducing data volume, using space‑for‑performance methods, and selecting appropriate storage systems—explaining their underlying principles, trade‑offs, and practical implementation steps for backend engineers facing slow queries and high load.

NoSQLbackend engineeringcqrs
0 likes · 19 min read
Eight Database Optimization Strategies for Backend Engineers
dbaplus Community
dbaplus Community
Jan 4, 2023 · Databases

Mastering MySQL Indexes: Costs, Pitfalls, and Best Practices

This article walks through practical MySQL index usage, illustrating space and time costs, back‑table overhead, covering indexes and index condition pushdown, common scenarios where indexes fail, and concrete best‑practice guidelines, all demonstrated with a 5‑million‑row InnoDB table and real SQL examples.

InnoDBMySQLSQL
0 likes · 15 min read
Mastering MySQL Indexes: Costs, Pitfalls, and Best Practices
Xingsheng Youxuan Technology Community
Xingsheng Youxuan Technology Community
Nov 23, 2022 · Databases

How X‑star Secured Huawei’s Cloud Innovation Award with High‑Performance User Profiling

The X‑star solution from XingSheng YouXuan’s tech team won second prize in Huawei’s 2022 Developer Competition by delivering high‑performance user‑profile selection and cost‑effective database innovations on Huawei Cloud, showcasing business, commercial, and technical value for improved marketing efficiency and platform experience.

Case studyHuawei Cloudcloud competition
0 likes · 5 min read
How X‑star Secured Huawei’s Cloud Innovation Award with High‑Performance User Profiling
Baidu Intelligent Cloud Tech Hub
Baidu Intelligent Cloud Tech Hub
Nov 18, 2022 · Databases

Redis Persistence Evolution and Baidu Cloud’s Forkless & Multi‑AOF Optimizations

This article examines the evolution of Redis’s persistence mechanisms—from early RDB snapshots and AOF logging to recent innovations such as Baidu Cloud’s forkless RDB implementation, mixed RDB/AOF formats, and Multi‑AOF architectures—detailing design principles, version milestones, and practical performance optimizations.

AOFBaidu CloudPersistence
0 likes · 14 min read
Redis Persistence Evolution and Baidu Cloud’s Forkless & Multi‑AOF Optimizations
DataFunTalk
DataFunTalk
Nov 14, 2022 · Databases

Performance Optimization and Tuning of Apache Doris Vectorized Version for Xiaomi's A/B Experiment Platform

Xiaomi upgraded its Apache Doris from version 0.13 to the vectorized 1.1.2 release for its A/B experiment platform, conducting extensive single‑SQL and concurrent tests, identifying CPU, memory, and fragment timeout issues, and applying tuning such as memory decommit settings, string matching improvements, and patches to achieve up to 5× query speed gains and enhanced stability.

Apache Dorisdatabase optimizationperformance-tuning
0 likes · 20 min read
Performance Optimization and Tuning of Apache Doris Vectorized Version for Xiaomi's A/B Experiment Platform
Code Ape Tech Column
Code Ape Tech Column
Nov 9, 2022 · Operations

System Performance Optimization: Definitions, Testing, Bottleneck Identification, and Common Strategies

This article explains system performance concepts such as throughput and latency, describes how to design and run performance tests, outlines methods for locating bottlenecks at the OS, code, network, and database levels, and presents practical optimization techniques ranging from algorithmic improvements to I/O and TCP tuning.

Throughputdatabase optimizationlatency
0 likes · 35 min read
System Performance Optimization: Definitions, Testing, Bottleneck Identification, and Common Strategies
政采云技术
政采云技术
Nov 1, 2022 · Databases

Understanding How Indexes Influence MySQL JOIN Queries and the Mechanics of Index and Block Nested-Loop Joins

This article explains how MySQL uses indexes during JOIN operations, compares indexed and non‑indexed join execution, introduces Index Nested‑Loop Join and Block Nested‑Loop Join algorithms, discusses the role of the join buffer, and provides practical guidance on choosing the optimal driving table for efficient query performance.

Block Nested Loop JoinIndexJOIN
0 likes · 14 min read
Understanding How Indexes Influence MySQL JOIN Queries and the Mechanics of Index and Block Nested-Loop Joins
Top Architect
Top Architect
Oct 20, 2022 · Databases

Performance Evaluation of Inserting Billion‑Scale Data into MySQL Using Java: MyBatis vs JDBC vs Batch Processing

This article presents a detailed performance test of inserting massive data into MySQL with Java, comparing three strategies—MyBatis lightweight insertion without transactions, direct JDBC handling with and without transactions, and JDBC batch processing—showing timing results for millions of rows and offering practical recommendations for high‑throughput data loading.

Batch ProcessingJDBCMyBatis
0 likes · 14 min read
Performance Evaluation of Inserting Billion‑Scale Data into MySQL Using Java: MyBatis vs JDBC vs Batch Processing
Ctrip Technology
Ctrip Technology
Oct 20, 2022 · Databases

Practical Slow Query Optimization for MySQL at Ctrip

This article describes Ctrip's practical approach to identifying and optimizing MySQL slow queries, covering background, improved SQL review workflow, execution plan analysis, common index problems, query rewriting, pagination issues, resource contention, and best‑practice recommendations for long‑term performance.

IndexingMySQLQuery Planning
0 likes · 10 min read
Practical Slow Query Optimization for MySQL at Ctrip
ITPUB
ITPUB
Oct 17, 2022 · Databases

Boost Database Performance: 4 Key Optimization Dimensions Explained

This article explores database optimization from four angles—architecture, hardware, instance parameters, and SQL tuning—detailing practical techniques like distributed caching, read/write splitting, sharding, SSD upgrades, parameter tuning for Oracle, MySQL, PostgreSQL, and DaMeng, and step‑by‑step SQL rewrite with real‑world performance results.

HardwareSQL Tuningdatabase optimization
0 likes · 17 min read
Boost Database Performance: 4 Key Optimization Dimensions Explained
DataFunTalk
DataFunTalk
Oct 11, 2022 · Databases

Enhancing ClickHouse Multi‑Table Join Capability with ByteHouse

This article explains the limitations of ClickHouse for multi‑table joins, describes ByteHouse’s staged execution model, various join strategies (Shuffle, Broadcast, Colocate) and runtime filters, and presents performance benchmarks that show significant speed‑ups over the original ClickHouse engine.

ByteHouseClickHouseMulti-table join
0 likes · 10 min read
Enhancing ClickHouse Multi‑Table Join Capability with ByteHouse
Top Architect
Top Architect
Oct 8, 2022 · Databases

Optimizing MySQL Pagination for Large Datasets: Techniques and Examples

This article analyzes why MySQL LIMIT pagination becomes slower on large tables, demonstrates the performance impact with real query timings, explains the underlying full‑table scan behavior, and presents three practical optimization strategies—including ordered primary‑key scans, sub‑queries, and join‑based approaches—to improve query speed.

IndexingMySQLSQL
0 likes · 7 min read
Optimizing MySQL Pagination for Large Datasets: Techniques and Examples
DataFunSummit
DataFunSummit
Oct 7, 2022 · Databases

Optimizing Complex Queries in ClickHouse: Multi‑Stage Execution, Exchange Management, and Performance Enhancements

This article explains how ByteHouse (a heavily optimized ClickHouse variant) tackles complex query challenges by introducing a multi‑stage execution model, exchange mechanisms, runtime filters, and network optimizations, and it presents performance results and future directions for large‑scale OLAP workloads.

ByteHouseClickHouseDistributed Query
0 likes · 21 min read
Optimizing Complex Queries in ClickHouse: Multi‑Stage Execution, Exchange Management, and Performance Enhancements
Code Ape Tech Column
Code Ape Tech Column
Sep 19, 2022 · Databases

Query Separation: Optimizing Large Table Queries with Separate Read Stores

The article explains query separation as a technique to improve performance of massive database tables by storing write‑optimized data and read‑optimized copies in separate systems, discusses when to apply it, implementation methods, storage choices, consistency handling, and practical considerations.

Asynchronous Replicationbinlogdatabase optimization
0 likes · 8 min read
Query Separation: Optimizing Large Table Queries with Separate Read Stores
Big Data Technology Architecture
Big Data Technology Architecture
Sep 17, 2022 · Databases

Design and Optimization of Bilibili Log Service 2.0 Using ClickHouse and OpenTelemetry

This article describes how Bilibili redesigned its log service by replacing Elasticsearch with ClickHouse, introducing OpenTelemetry‑based logging, optimizing storage, query, and alerting components, and enhancing ClickHouse features such as configuration tuning, Map types, and implicit columns to achieve higher performance, lower cost, and better observability.

ClickHousedatabase optimizationlog infrastructure
0 likes · 28 min read
Design and Optimization of Bilibili Log Service 2.0 Using ClickHouse and OpenTelemetry
WeChat Client Technology Team
WeChat Client Technology Team
Aug 30, 2022 · Databases

How WeChat Optimized Its Desktop Database for Speed, Size, and Reliability

This article analyzes the performance and storage problems of the Windows version of WeChat, explains why message growth causes slowness, large file size, and corruption risk, and presents a multi‑pronged solution involving sharding, indexing, payload compression, and robustness improvements that halve database size and boost I/O performance by about ten percent.

IndexingSQLiteSharding
0 likes · 11 min read
How WeChat Optimized Its Desktop Database for Speed, Size, and Reliability
ByteDance Data Platform
ByteDance Data Platform
Aug 22, 2022 · Databases

How ByteHouse Supercharges ClickHouse with Upsert, Joins, and High Availability

ByteHouse, built on ClickHouse, addresses key limitations such as missing upsert/delete, weak multi‑table joins, scalability issues, and lack of resource isolation by introducing a modular, stage‑based execution engine, advanced join strategies, runtime filters, and a custom optimizer, delivering dramatically faster query performance.

ByteHouseClickHouseMulti-table join
0 likes · 11 min read
How ByteHouse Supercharges ClickHouse with Upsert, Joins, and High Availability
Java Architect Essentials
Java Architect Essentials
Aug 5, 2022 · Fundamentals

Bloom Filter, Counting Bloom Filter, and Cuckoo Filter: Principles, Problems, and Optimizations

This article explains the concept and operation of Bloom filters, discusses their limitations such as false positives and deletion difficulties, introduces the Counting Bloom filter and Cuckoo filter as improvements, and explores their underlying hash mechanisms, performance trade‑offs, and optimization strategies.

Bloom filterCounting BloomCuckoo Filter
0 likes · 13 min read
Bloom Filter, Counting Bloom Filter, and Cuckoo Filter: Principles, Problems, and Optimizations