BestHub
Sign in
Tagged articles
636 articles
Page 4 of 7
Selected Java Interview Questions
Selected Java Interview Questions
Apr 25, 2022 · Databases

MySQL SQL and Index Optimization Techniques

This article provides a comprehensive guide to optimizing MySQL queries and indexes, covering best practices such as avoiding subqueries, using IN instead of OR, proper LIMIT usage, eliminating unnecessary ORDER BY, leveraging UNION ALL, batch inserts, selective column retrieval, distinguishing IN and EXISTS, improving GROUP BY, using numeric fields, optimizing JOINs, and applying index rules like left‑most prefix, avoiding functions on indexed columns, handling type conversions, covering indexes, and effective use of composite and prefix indexes.

SQL Optimizationdatabaseindexing
0 likes · 19 min read
MySQL SQL and Index Optimization Techniques
IT Services Circle
IT Services Circle
Apr 19, 2022 · Databases

Why MySQL Uses B+ Trees for Indexes Instead of Skip Lists

This article explains the structures of B+ trees and skip lists, compares their read and write performance, and details why MySQL chooses B+ trees for indexing while Redis and RocksDB prefer skip lists, highlighting trade‑offs in disk I/O, fan‑out, and balancing overhead.

B+Treedatabaseindexing
0 likes · 13 min read
Why MySQL Uses B+ Trees for Indexes Instead of Skip Lists
IT Architects Alliance
IT Architects Alliance
Apr 10, 2022 · Backend Development

Understanding Elasticsearch: Core Concepts, Architecture, and Performance Tips

This article provides a comprehensive overview of Elasticsearch, covering data types, Lucene fundamentals, cluster discovery, node roles, shard and replica management, mapping, installation, health monitoring, indexing mechanics, storage strategies, refresh and translog processes, segment merging, and practical performance optimizations for production deployments.

Distributed SystemsElasticsearchindexing
0 likes · 39 min read
Understanding Elasticsearch: Core Concepts, Architecture, and Performance Tips
Top Architect
Top Architect
Apr 9, 2022 · Big Data

Elasticsearch Overview: Architecture, Core Concepts, Indexing Mechanics, and Performance Optimization

This comprehensive article explains what Elasticsearch is, how it builds on Lucene to provide distributed real‑time search and analytics, covering data types, cluster components, shard routing, indexing pipelines, storage formats, segment merging, and practical performance‑tuning tips for production deployments.

ElasticsearchPerformance Optimizationindexing
0 likes · 36 min read
Elasticsearch Overview: Architecture, Core Concepts, Indexing Mechanics, and Performance Optimization
Architect's Tech Stack
Architect's Tech Stack
Apr 8, 2022 · Databases

Comprehensive Guide to MySQL Query Optimization and Common Pitfalls

This article explains why SQL performance degrades as data grows, outlines a step‑by‑step optimization workflow—including slow‑query detection, EXPLAIN analysis, profiling, optimizer tracing, and corrective actions—while detailing type and extra fields, index usage, and practical scenarios such as large pagination, IN‑order‑by, range queries, and complex joins.

SQLdatabaseindexing
0 likes · 10 min read
Comprehensive Guide to MySQL Query Optimization and Common Pitfalls
Java Captain
Java Captain
Apr 2, 2022 · Backend Development

Building a Site Search Engine with Java Indexing and File Parsing

This article explains how to build a site‑wide search engine using Java, covering crawling concepts, forward and inverted indexing, module design, tokenization methods, and detailed code examples for file enumeration, HTML parsing, and index generation.

Site Searchindexingparsing
0 likes · 14 min read
Building a Site Search Engine with Java Indexing and File Parsing
Laravel Tech Community
Laravel Tech Community
Mar 31, 2022 · Databases

SQL Optimization Steps and Common Cases for Improving Query Performance

This article outlines a systematic approach to identifying and fixing slow MySQL queries, explains how to interpret EXPLAIN output and profiling information, and presents multiple real‑world cases illustrating index usage, implicit conversion, large pagination, IN queries, and other optimization techniques.

Database OptimizationExplain PlanSQL
0 likes · 10 min read
SQL Optimization Steps and Common Cases for Improving Query Performance
NiuNiu MaTe
NiuNiu MaTe
Mar 30, 2022 · Databases

Why Is MySQL Query Slow? Hidden Factors and Proven Speed‑Up Tricks

This article explains why MySQL queries can become sluggish beyond missing indexes, covering the full query execution flow, profiling tools, index pitfalls, connection‑pool limits, InnoDB buffer‑pool sizing, and practical steps to diagnose and accelerate database performance.

Connection Poolbuffer poolindexing
0 likes · 16 min read
Why Is MySQL Query Slow? Hidden Factors and Proven Speed‑Up Tricks
Open Source Linux
Open Source Linux
Mar 29, 2022 · Databases

Essential MySQL SQL Optimization Tips to Speed Up Your Queries

This guide presents a comprehensive set of MySQL optimization techniques—including proper use of EXPLAIN, limiting IN clause size, avoiding SELECT *, using LIMIT 1, minimizing sorting, replacing OR with UNION ALL, preferring UNION ALL over UNION, eliminating ORDER BY RAND(), distinguishing IN versus EXISTS, applying efficient pagination, segmenting large scans, avoiding NULL checks and leading wildcards, rewriting expressions, preventing implicit type conversion, respecting composite index leftmost rules, forcing indexes, handling range queries, and optimizing JOINs—to dramatically improve query performance.

Database PerformanceJOINSQL Optimization
0 likes · 11 min read
Essential MySQL SQL Optimization Tips to Speed Up Your Queries
Architecture & Thinking
Architecture & Thinking
Mar 17, 2022 · Databases

Why Large OFFSETs Slow Down MySQL Queries and How to Fix Them

This article analyzes a production incident where massive OFFSET values in MySQL pagination caused severe query slowdown, demonstrates the problem with simulated data, benchmarks the impact, and presents three practical solutions—including index‑covering subqueries, remembering the last primary key, and limiting offset size—to dramatically improve performance and protect the database.

SQLindexingmysql
0 likes · 13 min read
Why Large OFFSETs Slow Down MySQL Queries and How to Fix Them
ITPUB
ITPUB
Mar 14, 2022 · Databases

Master MySQL Performance: Practical SQL, Index, Join, and Hardware Tuning

This article provides a comprehensive guide to MySQL performance improvement, covering pagination, index, join, sorting, UNION, slow‑query‑log, schema design, and hardware optimizations with concrete SQL examples, best‑practice tips, and cautionary notes for real‑world deployments.

Database designHardware OptimizationSQL Optimization
0 likes · 16 min read
Master MySQL Performance: Practical SQL, Index, Join, and Hardware Tuning
Xianyu Technology
Xianyu Technology
Mar 2, 2022 · Databases

Optimizing a Slow SQL Query with AnalyticDB and Data Synchronization Strategies

By migrating a latency‑heavy MySQL query to AnalyticDB, employing binlog‑based real‑time sync, refining composite indexes, adding clustering and filter hints, and batching inserts, the team cut execution time from about two seconds to roughly one hundred milliseconds, achieving over 99 % of queries under one second.

AnalyticDBSQL Optimizationindexing
0 likes · 12 min read
Optimizing a Slow SQL Query with AnalyticDB and Data Synchronization Strategies
Laravel Tech Community
Laravel Tech Community
Feb 17, 2022 · Backend Development

Key New Features and Changes in Elasticsearch 8.0 Release

Elasticsearch 8.0 introduces major updates including 7.x REST API compatibility headers, default‑enabled security with registration tokens, protected system indices, a preview KNN search API, storage‑efficient keyword/match_only_text/text fields, faster indexing for geo_point and geo_shape, PyTorch model support, and numerous deprecations and enhancements across aggregations, allocation, analysis, authentication, cluster coordination, and engine components.

BackendElasticsearchindexing
0 likes · 8 min read
Key New Features and Changes in Elasticsearch 8.0 Release
MaGe Linux Operations
MaGe Linux Operations
Feb 15, 2022 · Backend Development

What’s New in Elasticsearch 8.0? Key Features, Security, and API Changes

Elasticsearch 8.0 introduces major updates including 7.x REST API compatibility headers, default-enabled security with enrollment tokens, protected system indices, a preview KNN search API, storage‑saving field encodings, faster geo indexing, and numerous deprecations and enhancements across aggregations, authentication, cluster coordination, and packaging.

API compatibilityElasticsearchSecurity
0 likes · 10 min read
What’s New in Elasticsearch 8.0? Key Features, Security, and API Changes
Top Architect
Top Architect
Feb 14, 2022 · Databases

Elasticsearch Fundamentals: Architecture, Indexing, Sharding, and Performance Optimization

This comprehensive guide explains Elasticsearch’s core concepts—including its distributed architecture, indexing process, shard routing, refresh and translog mechanisms, segment merging, and performance tuning—while providing practical examples and configuration tips for building scalable, near‑real‑time search solutions.

Elasticsearchindexingperformance tuning
0 likes · 35 min read
Elasticsearch Fundamentals: Architecture, Indexing, Sharding, and Performance Optimization
dbaplus Community
dbaplus Community
Feb 13, 2022 · Databases

Mastering SQL Execution Order and Optimization: Practical Tips and Best Practices

This guide walks through MySQL's query execution sequence, then presents concrete optimization techniques—from selecting specific columns and avoiding OR conditions to proper indexing, using EXPLAIN, handling VARCHAR vs CHAR, limiting result sets, and improving joins, GROUP BY, and UNION operations—complete with code examples and visual explain plans.

SQLdatabaseindexing
0 likes · 20 min read
Mastering SQL Execution Order and Optimization: Practical Tips and Best Practices
Programmer DD
Programmer DD
Feb 12, 2022 · Databases

What’s New in Elasticsearch 8.0? Key Features and Migration Tips

Elasticsearch 8.0 introduces major changes such as 7.x REST API compatibility headers, default‑enabled security with registration tokens, protected system indices, a technical preview of KNN search, storage‑saving field encodings, faster geo‑point indexing, PyTorch model support for NLP, and numerous deprecations and improvements across aggregations, allocation, analysis, authentication, cluster coordination, and packaging.

APIBig DataElasticsearch
0 likes · 10 min read
What’s New in Elasticsearch 8.0? Key Features and Migration Tips
Su San Talks Tech
Su San Talks Tech
Feb 7, 2022 · Databases

Essential MySQL Interview Q&A: Locks, Indexes, Replication, and Optimization

A comprehensive collection of MySQL interview questions and answers covering storage engines, lock types, gap locks, deadlock avoidance, isolation levels, index types, covering indexes, the left‑most prefix rule, SQL tuning, master‑slave replication, latency handling, sharding challenges, and global ID generation.

Replicationdatabaseindexing
0 likes · 15 min read
Essential MySQL Interview Q&A: Locks, Indexes, Replication, and Optimization
Architecture Digest
Architecture Digest
Jan 15, 2022 · Databases

Designing and Implementing Elasticsearch for Large‑Scale Data Search and Storage

This article details the business background, technical advantages, architecture, indexing mechanisms, clustering, data synchronization strategies, API design, and performance monitoring of Elasticsearch, illustrating how it replaces costly SQL LIKE queries with a scalable, high‑performance search solution for massive user activity data.

ElasticsearchNoSQLdata synchronization
0 likes · 29 min read
Designing and Implementing Elasticsearch for Large‑Scale Data Search and Storage
Tencent Music Tech Team
Tencent Music Tech Team
Jan 4, 2022 · Big Data

Elasticsearch Fundamentals: Architecture, Indexing, Query DSL and Search Mechanics

Elasticsearch is a distributed, schemaless search engine built on Lucene that stores JSON documents in sharded indexes, uses immutable segments and merges, provides a flexible Query DSL with aggregations and relevance scoring, and executes distributed query‑then‑fetch searches with features like scrolling, optimistic locking, and zero‑downtime reindexing.

AnalyzersElasticsearchQuery DSL
0 likes · 26 min read
Elasticsearch Fundamentals: Architecture, Indexing, Query DSL and Search Mechanics
dbaplus Community
dbaplus Community
Jan 3, 2022 · Databases

What Happens When a Single Bad SQL Slows Down an Entire Data Warehouse? Real Cases & Fixes

This article presents four real‑world Oracle SQL cases—from a Cartesian join that halted nightly reports to a varchar primary key causing full scans, non‑standard date syntax breaking partition pruning, and missing statistics leading to unstable plans—detailing analysis, execution‑plan insights, and concrete remediation steps.

OracleSQL Optimizationexecution plan
0 likes · 19 min read
What Happens When a Single Bad SQL Slows Down an Entire Data Warehouse? Real Cases & Fixes
Top Architect
Top Architect
Dec 25, 2021 · Databases

Using MySQL JSON Data Type for Flexible Schema Design and Efficient Indexing

This article explains the MySQL JSON data type, demonstrates how to store structured and semi‑structured data with JSON objects and arrays, shows practical table designs for user login and profiling, and details virtual columns, function indexes, and multi‑valued indexes for fast querying.

JSONMulti-Valued IndexSQL
0 likes · 13 min read
Using MySQL JSON Data Type for Flexible Schema Design and Efficient Indexing
Laravel Tech Community
Laravel Tech Community
Dec 23, 2021 · Databases

Database Object Naming and Design Standards

This guide details comprehensive naming conventions and design best‑practices for MySQL database objects—including schemas, tables, indexes, views, stored procedures, functions, triggers, constraints, and users—along with recommended storage engine, character set, field types, index strategies, and SQL usage rules to ensure consistency, performance, and maintainability.

Naming ConventionSQLindexing
0 likes · 20 min read
Database Object Naming and Design Standards
21CTO
21CTO
Dec 14, 2021 · Databases

PostgreSQL vs MySQL: Which Database Wins in JSON, Indexing, and Concurrency?

This article examines workload analysis and query performance differences between PostgreSQL and MySQL, focusing on JSON handling, indexing strategies, concurrency control, benchmark results, and replication features, while offering configuration tips to improve each database’s speed and efficiency.

Database PerformanceJSONMVCC
0 likes · 10 min read
PostgreSQL vs MySQL: Which Database Wins in JSON, Indexing, and Concurrency?
Tencent Architect
Tencent Architect
Dec 14, 2021 · Databases

Master MongoDB: From Basics to Advanced Performance and Scaling

This comprehensive guide covers MongoDB's core concepts—including schema‑free design, high availability, sharding, storage engine internals, indexing, and performance tuning—while providing practical code examples, configuration steps, and best‑practice tips for developers and architects looking to master the database in production environments.

Database designMongoDBNoSQL
0 likes · 45 min read
Master MongoDB: From Basics to Advanced Performance and Scaling
Alimama Tech
Alimama Tech
Dec 8, 2021 · Artificial Intelligence

Dual Vector Foil (DVF): Decoupled Index and Model Retrieval for Large-Scale Recall

The Dual Vector Foil (DVF) system decouples index construction from model training by building a post‑training HNSW graph, enabling any complex model to score candidates, which yields a 5.7 % recall boost, cuts latency from ~40 ms to 6.5 ms, and raises QPS over tenfold while simplifying maintenance.

dual vector foilindexinglarge-scale retrieval
0 likes · 27 min read
Dual Vector Foil (DVF): Decoupled Index and Model Retrieval for Large-Scale Recall
macrozheng
macrozheng
Nov 23, 2021 · Databases

15 Essential SQL Optimization Tips to Boost Query Performance

This article presents fifteen practical SQL optimization techniques—including avoiding SELECT *, using UNION ALL, leveraging IN/EXISTS, batching inserts, applying LIMIT, controlling IN list size, incremental queries, efficient pagination, preferring JOINs, limiting join tables, managing indexes, choosing proper column types, improving GROUP BY, and using EXPLAIN—to help developers dramatically improve query speed and resource usage.

Query TuningSQLdatabase
0 likes · 20 min read
15 Essential SQL Optimization Tips to Boost Query Performance
Su San Talks Tech
Su San Talks Tech
Nov 10, 2021 · Databases

15 Proven SQL Optimization Techniques to Boost Query Performance

This article presents fifteen practical SQL optimization tips—including avoiding SELECT *, using UNION ALL, driving large tables with small ones, batch operations, LIMIT usage, index control, proper data types, and efficient GROUP BY—to help developers improve query speed, reduce resource consumption, and avoid common performance pitfalls.

Query TuningSQLdatabase
0 likes · 20 min read
15 Proven SQL Optimization Techniques to Boost Query Performance
Architect's Tech Stack
Architect's Tech Stack
Oct 20, 2021 · Databases

MySQL Performance Optimization: Data Volume, Concurrency, Query Time, Table Design, Index and SQL Tuning

This article presents a comprehensive guide to MySQL performance, covering maximum data volume and concurrency limits, recommended query response times, practical table‑design rules, index classification and optimization techniques, as well as detailed SQL tuning patterns such as batch processing, operator, OR, IN, LIKE, JOIN and LIMIT improvements.

Database OptimizationSQL Tuningdata modeling
0 likes · 15 min read
MySQL Performance Optimization: Data Volume, Concurrency, Query Time, Table Design, Index and SQL Tuning
Java High-Performance Architecture
Java High-Performance Architecture
Oct 3, 2021 · Databases

Why Tame Slow SQL? Proven MySQL Optimization Strategies

The article explains why slow SQL queries drain I/O and CPU resources, outlines prioritization rules, details MySQL execution steps, identifies performance‑affecting factors, and provides practical solutions and best‑practice guidelines for diagnosing and optimizing slow MySQL queries in production environments.

Database PerformanceSQL Tuningindexing
0 likes · 19 min read
Why Tame Slow SQL? Proven MySQL Optimization Strategies
Java Interview Crash Guide
Java Interview Crash Guide
Oct 1, 2021 · Databases

Why MySQL Queries Slow Down and How to Optimize with Indexes

The article explains why a simple GROUP BY query on a large user‑view table can become inefficient due to temporary tables, filesort, and memory allocation, demonstrates the execution steps with EXPLAIN, and shows how adding a composite index on user_id, viewed_user_age, and viewed_user_sex eliminates temporary tables and improves performance.

SQL Optimizationindexingmysql
0 likes · 16 min read
Why MySQL Queries Slow Down and How to Optimize with Indexes
IT Xianyu
IT Xianyu
Sep 15, 2021 · Databases

Database Types, Bottlenecks, Optimization Strategies and Scaling Techniques

This article explains the classification of relational and NoSQL databases, analyzes common performance bottlenecks such as query latency, large fields, and write overhead, and presents practical optimization methods including caching, proper indexing, transaction handling, read‑write separation, and sharding for large‑scale systems.

cachingdatabasesindexing
0 likes · 17 min read
Database Types, Bottlenecks, Optimization Strategies and Scaling Techniques
Wukong Talks Architecture
Wukong Talks Architecture
Sep 9, 2021 · Databases

Comprehensive MySQL Interview Questions and Answers on Indexes, Transactions, Replication, and More

This article compiles twenty detailed MySQL interview questions covering redo log vs. binlog, index types and best practices, covering indexes, joint index matching rules, back‑table queries, left‑most prefix principle, lock mechanisms, MVCC, execution plans, master‑slave replication, XA transactions, and the Snowflake ID algorithm, providing clear explanations and example SQL code.

MVCCReplicationTransactions
0 likes · 30 min read
Comprehensive MySQL Interview Questions and Answers on Indexes, Transactions, Replication, and More
Selected Java Interview Questions
Selected Java Interview Questions
Sep 7, 2021 · Big Data

Elasticsearch Basics: Core Concepts, Indexing, Write and Search Processes, Cluster Management and Performance Tips

This article provides a comprehensive overview of Elasticsearch, covering its fundamental architecture, key concepts such as indices, shards and replicas, the complete write and search workflows, consistency mechanisms, master node election, and practical performance‑tuning recommendations for large‑scale deployments.

Big DataCluster ManagementElasticsearch
0 likes · 15 min read
Elasticsearch Basics: Core Concepts, Indexing, Write and Search Processes, Cluster Management and Performance Tips
Sohu Tech Products
Sohu Tech Products
Aug 18, 2021 · Databases

Understanding Slow Queries, Index Optimization, and Search Solutions with MySQL, Elasticsearch, and HBase

This article explains why MySQL queries become slow, how proper indexing and index‑pushdown can improve performance, discusses common index‑failure causes, and then introduces Elasticsearch and HBase as complementary search and storage solutions for large‑scale data, including practical usage tips and architectural considerations.

ElasticsearchHBasedatabase
0 likes · 18 min read
Understanding Slow Queries, Index Optimization, and Search Solutions with MySQL, Elasticsearch, and HBase
ITFLY8 Architecture Home
ITFLY8 Architecture Home
Aug 14, 2021 · Databases

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

This article explains why MySQL queries become slow—covering index misuse, MDL locks, flush waits, row locks, and large‑table bottlenecks—then introduces ElasticSearch’s inverted‑index architecture and HBase’s column‑family storage, offering practical tips for performance tuning and hybrid solutions.

ElasticsearchHBasedatabase
0 likes · 18 min read
Why Your MySQL Queries Are Slow and How to Fix Them with Indexes, ElasticSearch, and HBase
Top Architect
Top Architect
Aug 14, 2021 · Big Data

Elasticsearch Overview: Architecture, Core Concepts, and Performance Optimization

This article provides a comprehensive introduction to Elasticsearch, covering its underlying Lucene-based inverted index, data types, shard routing, cluster roles, discovery mechanisms, refresh and translog handling, segment merging, and practical performance and JVM tuning tips for building scalable, near‑real‑time search systems.

Elasticsearchindexingperformance tuning
0 likes · 35 min read
Elasticsearch Overview: Architecture, Core Concepts, and Performance Optimization
Python Programming Learning Circle
Python Programming Learning Circle
Aug 12, 2021 · Databases

Understanding MySQL Slow Queries, Index Optimization, ElasticSearch Basics, and HBase Overview

This article explains why MySQL queries become slow, how proper indexing—including B+‑tree, left‑most prefix, index push‑down, and covering indexes—can improve performance, outlines common causes of index failure, and then introduces ElasticSearch search capabilities and HBase column‑family storage as complementary solutions for large‑scale data handling.

Database OptimizationElasticsearchHBase
0 likes · 16 min read
Understanding MySQL Slow Queries, Index Optimization, ElasticSearch Basics, and HBase Overview
Aikesheng Open Source Community
Aikesheng Open Source Community
Jul 21, 2021 · Databases

MySQL Index Design Specification and Best Practices

This article summarizes comprehensive MySQL index design guidelines, covering naming conventions, column selection criteria, index count control, handling of frequently updated columns, function and duplicate indexes, small‑table considerations, and index ordering to optimize query performance.

Database designSQLindexing
0 likes · 8 min read
MySQL Index Design Specification and Best Practices
Big Data Technology Architecture
Big Data Technology Architecture
Jul 20, 2021 · Big Data

PB‑Level Ad‑hoc Query Practice with Flink: Threat Hunting Platform Architecture and IO‑Reducing Optimizations

This article details 360's Threat Hunting platform built on Flink, covering its evolution, architecture, block‑index design, Hilbert‑curve data ordering, like‑pushdown, join optimizations, Alluxio caching, and future plans for BI and multi‑user concurrency, all aimed at efficient PB‑scale data querying.

AlluxioBlock IndexFlink
0 likes · 18 min read
PB‑Level Ad‑hoc Query Practice with Flink: Threat Hunting Platform Architecture and IO‑Reducing Optimizations
21CTO
21CTO
Jul 18, 2021 · Databases

Why Your MySQL Queries Are Slow and How ElasticSearch & HBase Can Help

This article examines common causes of slow MySQL queries, explains index mechanics and failures, then compares ElasticSearch’s fast tokenized search and HBase’s column‑oriented storage, offering practical guidance on when and how to use each technology.

Big DataDatabase PerformanceHBase
0 likes · 21 min read
Why Your MySQL Queries Are Slow and How ElasticSearch & HBase Can Help
Python Crawling & Data Mining
Python Crawling & Data Mining
Jul 18, 2021 · Databases

How Indexing Can Slash Oracle Query Time from Minutes to Seconds

This article examines a real‑world Oracle SQL query, identifies costly full‑table scans and low‑selectivity columns, and demonstrates how creating an index on a key column reduces execution time from 24 minutes to just one second, highlighting essential SQL performance‑tuning techniques.

AWRDatabase OptimizationOracle
0 likes · 7 min read
How Indexing Can Slash Oracle Query Time from Minutes to Seconds
vivo Internet Technology
vivo Internet Technology
Jul 14, 2021 · Databases

An Overview of Lucene: Architecture, Indexing Workflow, and Code Implementation

The article introduces Apache Lucene 7.3.1, explains its core architecture and index hierarchy, details the two‑phase indexing and search workflow with code examples for document addition, deletion, merging, and query execution, and highlights its suitability for small‑to‑medium projects versus distributed alternatives.

Code ExampleFull‑Text SearchJava
0 likes · 20 min read
An Overview of Lucene: Architecture, Indexing Workflow, and Code Implementation
MaGe Linux Operations
MaGe Linux Operations
Jul 4, 2021 · Databases

Why MySQL Queries Slow Down and How ES & HBase Can Help Optimize

This article explores common causes of MySQL slow queries such as index misuse and lock contention, explains indexing strategies like index pushdown and covering indexes, and then compares Elasticsearch and HBase as complementary solutions for large‑scale search and write‑intensive workloads, offering practical tips for performance optimization.

ElasticsearchHBaseindexing
0 likes · 19 min read
Why MySQL Queries Slow Down and How ES & HBase Can Help Optimize
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.

SQLdeep paginationindexing
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.

SQLindexinglarge datasets
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.

Database OptimizationHBaseindexing
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 PerformanceInnoDB
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.

SQLindexingmysql
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+TreeDatabase OptimizationInnoDB
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 QueriesElasticsearchInstallation
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.

LIMITLarge Dataindexing
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.

Unique IndexfailIndexKeyTooLongindexing
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+Treeindexingmysql
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.

ConsistencySystem Designarchitecture
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.

SQLindexingmysql
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.

SQLTransactionsarchitecture
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.

Product QualitySphinxbinary scanning
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.

Database designNOT 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.

Transactionsdatabaseindexing
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.

Database OptimizationDynamic QuerySQL
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.

auto-incrementindexingprimary-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.

Cloud DatabasesDatabase OptimizationLarge Tables
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.

auto_incrementindexingperformance
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.

Data MigrationFull Table ScanINSERT INTO SELECT
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.

MESPerformance OptimizationSQL
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.

Query TuningSQLindexing
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.

Query TuningSQLdatabase
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.

SQLdatabaseindexing
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.

databasedeadlockindexing
0 likes · 13 min read
MySQL Deadlock Investigation Record