This article explains how revoked document permissions may persist in AI retrieval indexes, using Microsoft Azure AI Search as a case study to show the disconnect between source permissions and indexed permissions, the three distinct timing points of revocation, and why AI assistants make permission lag more visible to users.
This comprehensive guide walks through a complete MySQL performance optimization cycle: classifying three types of gradual slowdown, setting up slow logs and digest metrics, using EXPLAIN ANALYZE and optimizer trace to pinpoint a full-table scan on a 62M-row orders table, adding a composite index (user_id, status, created_at) via safe online DDL with three safety barriers, and establishing monitoring to prevent regression.
This article explains why AI agents fail to recall user preferences across sessions despite saving them to files, detailing the critical role of an index file (MEMORY.md) for discovery, the two-step save-and-index process, failure modes when indexing fails, and verification techniques using unique content markers.
A developer shares their successful China Merchants Bank Java backend interview experience, detailing 20 technical questions covering HashMap, ConcurrentHashMap, synchronization, thread pools, Redis cache penetration, MySQL indexing, Spring Boot auto-configuration, Agent memory, RAG, multi-agent orchestration, and more, with answers and preparation tips.
A step-by-step MySQL query optimization case study showing how indexing, join rewriting, composite indexes, and covering indexes reduced execution time from 30,248 seconds to 0.001 seconds with execution plan analysis.
This MongoDB cheat sheet guides SQL developers through the mindset shift to NoSQL, covering concept mappings, CRUD operations, indexing, aggregation pipelines, and execution plan analysis with practical code examples for each operation and key pitfalls to avoid.
Baidu Baige optimizes DataLoader across sampling, indexing, decoding, and post-processing for embodied model training, reducing FastWAM single-sample video processing from 133ms to 63ms, stabilizing indexing at 0.61ms across 27,500 files, and boosting Qwen3-VL-30B throughput 1.68x via load-balanced sampling.
This article explains how MySQL uses B+ tree indexes to accelerate queries, detailing their structure, differences from binary search trees, organization around 16KB pages, and why smaller index values reduce tree height and disk I/O for better performance.
This comprehensive guide details nine essential MySQL DBA commands — SHOW DATABASES, SHOW TABLE STATUS, SHOW FULL PROCESSLIST, SHOW ENGINE INNODB STATUS, SHOW VARIABLES, SHOW STATUS, EXPLAIN, SHOW INDEX, and SHOW CREATE TABLE — with syntax examples, key output fields, and practical troubleshooting scenarios for performance tuning and database administration.
This comprehensive guide covers MySQL 9.5 performance optimization across table design, indexing strategies, query analysis with EXPLAIN, JOIN optimization, pagination techniques, system-level InnoDB tuning, and monitoring using Performance Schema and slow query logs to achieve dramatic query speed improvements.
The article surveys ten practical performance‑optimization tactics—from classic indexing, compression, and caching to prefetching, peak‑shaving, batch processing, and advanced methods such as resource squeezing, horizontal scaling, sharding, and lock‑free designs—explaining their trade‑offs, concrete examples, and when to apply each in real‑world systems.
This article covers MongoDB document modeling vs relational, embedding vs referencing strategies, Spring Data MongoDB Repository and MongoTemplate usage, aggregation pipelines for analytics, multi-document transactions with avoidance patterns, composite indexing principles, and connection pooling with cursor-based pagination for high-concurrency Spring Boot applications.
This article walks through a comprehensive case study of a provincial medical appointment platform, diagnosing slow‑query bottlenecks, proposing patient‑id + create_time composite indexes, designing read‑write separation with replication, selecting patient_id for horizontal sharding, and implementing cache‑aside strategies to ensure consistency while handling cache avalanche, penetration and thundering‑herd scenarios.
The article walks through a real‑world MySQL deadlock incident, shows how to extract the LATEST DETECTED DEADLOCK section from SHOW ENGINE INNODB STATUS, explains gap and next‑key locks, and presents a three‑step method plus practical fixes to eliminate the deadlock.
The article explains what MySQL slow queries are, why they must be detected, when to enable slow‑query logging, step‑by‑step commands to configure the log, how to simulate and analyze problematic SQL with EXPLAIN, and practical optimization techniques—including index creation and Spring Boot integration—to eliminate performance bottlenecks.
This article explains MySQL EXPLAIN output fields (id, select_type, type, key, key_len, rows, Extra), demonstrates optimizing a 200M-row query from 8 seconds to 15 milliseconds via composite index, and provides a four-point checklist (type, key, rows, Extra) for daily SQL troubleshooting.
The article provides a comprehensive PostgreSQL operational self‑checklist covering CPU, memory, generic plans, idle connections, autovacuum, checkpoint and bgwriter tuning, AI workload handling, index building, replication, migration, partition management, and observability, with concrete examples, benchmarks, and practical commands.
The article explains how InnoDB implements indexes as B+ trees, detailing their evolution from simple page directories, the differences between clustered and secondary indexes, the costs of indexing, and the specific query patterns and scenarios where such indexes are effective.
This article provides a comprehensive, production‑grade guide to Elasticsearch clusters, covering the fundamentals of inverted indexes and Lucene segments, near‑real‑time write mechanics, shard routing, indexing pipelines, query execution flow, scaling strategies, and practical tips to avoid common pitfalls in high‑traffic search systems.
The article identifies eight easy-to-overlook SQL habits—such as using SELECT *, applying functions on indexed columns, careless LIKE patterns, improper IN/OR logic, deep offset pagination, unconditional UPDATE/DELETE, excessive indexing, and ignoring execution plans—that can cause slow queries, lock tables, index failures, and even data loss, and provides concrete examples and safer alternatives.
Most slow SQL statements stem from poor query writing—such as index‑breaking functions, implicit type casts, leading wildcards, unnecessary column selection, improper joins, sorting, grouping, and pagination—rather than merely the size of the data, and the article shows how to diagnose and fix each issue.
This article reveals that most slow SQL queries aren't caused by large data volumes but by poor query writing, such as index‑killing functions, implicit type casts, leading wildcards, SELECT *, missing LIMIT, and inefficient JOINs, and offers a five‑step method to diagnose and fix them.
Elasticsearch adds a new Columnar Mode in the 9.5 tech preview and 9.6 GA, storing data once in a columnar layout with on‑demand indexing, which cuts storage costs, speeds analytical queries, keeps the existing document mode intact, and offers a migration path for append‑only log and security workloads.
This guide walks through the most important Elasticsearch commands, covering cluster health checks, index listing, creation with mappings, deletion, mapping inspection, document insertion (with and without IDs), various query types, updates, deletions, pagination, sorting, and bulk operations, each illustrated with concrete examples and usage notes.
This article provides a complete, step‑by‑step guide to MongoDB shell commands—including show dbs, use, db, collection operations, indexing, aggregation and statistics—complete with syntax, example outputs and practical usage notes for developers and administrators.
This article walks through a real‑world contract‑review RAG project, diagnosing low recall, redesigning the system with multiple indexes, a RouterQueryEngine, re‑ranking, knowledge‑graph integration, multimodal support, incremental updates, and a rigorous evaluation framework that boosted recall from 60 % to 92 %.
The article walks through a real‑world legal‑contract RAG project that stalled at 60% recall, diagnoses five root causes, and demonstrates how combining multiple LlamaIndex indexes, a Router, fusion retrieval, re‑ranking, knowledge‑graph and multimodal support raises recall to 92% while outlining evaluation metrics, latency trade‑offs, and practical deployment checklists.
This guide walks through the most common ElasticSearch commands, covering cluster health checks, index management, document CRUD operations, mapping inspection, full‑text and filtered searches, aggregations, and bulk inserts, while explaining each step with concrete examples and performance tips.
The article explains why developers should move beyond MySQL to PostgreSQL, illustrating the productivity gains of native window functions, MVCC concurrency, rich indexing, JSONB support, and extensive extensions through detailed examples, installation guides, performance comparisons, and real‑world use cases.
This article walks through the entire MySQL performance troubleshooting workflow from an operations perspective, covering architecture basics, slow‑query‑log configuration, analysis with mysqldumpslow and pt‑query‑digest, EXPLAIN interpretation, index design and optimization, configuration tuning, replication monitoring, real‑time diagnostic commands, risk mitigation, rollback procedures, and backup strategies.
The article explains why many developers stuck with MySQL should consider PostgreSQL, highlighting its superior SQL standards support, MVCC concurrency, rich indexing, JSONB performance, full‑text search, extensive extensions, and practical installation and Java integration steps, while also comparing pros, cons, and ideal use cases.
This article walks through a MySQL 5.6 case study where a sub‑query that originally took over eight hours is accelerated to a few milliseconds by analyzing the execution plan, adding single‑column and composite indexes, rewriting the query as EXISTS or JOIN, and applying best‑practice indexing techniques.
A real‑world MySQL incident where a batch UPDATE with an IN‑subquery caused a full‑table scan, connection pool exhaustion, and a system‑wide outage, and the step‑by‑step investigation, emergency mitigation, and comprehensive optimization that reduced query time from 45 seconds to 0.3 seconds.
This article walks through a real‑world MySQL slow‑query case, showing how to identify the bottleneck with EXPLAIN, design covering and composite indexes, rewrite the SQL, tune InnoDB parameters, and safely deploy the changes, ultimately shrinking execution time from seconds to a few milliseconds.
When a MySQL‑driven application shows sudden CPU spikes, this guide walks through a step‑by‑step process—using top/htop, SHOW PROCESSLIST, enabling slow‑query logs, analyzing EXPLAIN output, killing offending queries, adding appropriate indexes, rewriting joins, and establishing preventive monitoring—to quickly identify and fix the root cause.
The article walks through a real-world e-commerce incident, explains why a seemingly simple slow order-query SQL can cripple an entire high-traffic system, and presents a complete automated workflow—from detection and analysis to optimization, deployment, verification, and regression monitoring—to achieve sustainable full-stack SQL performance.
The article explains how to safely add indexes to a tens‑of‑millions‑row MySQL table, covering lock duration, disk‑space impact, write‑performance degradation, and six practical principles—selective columns, proper order, covering indexes, avoiding redundancy, short‑lock tools, and ongoing monitoring—plus pros, cons, and suitable use cases.
When a production MySQL query joins ten tables and exceeds 30 seconds, a systematic approach—starting with EXPLAIN analysis, profiling, and configuration checks—combined with layered optimizations such as indexing, join order adjustment, query splitting, temporary tables, materialized views, OLAP engines, and vertical partitioning—can dramatically improve performance.
The article explains why brute‑force vector search is painfully slow, introduces Flat, IVF, and HNSW index structures, compares their speed, memory and accuracy, shows common pitfalls, provides production‑grade Python code, and presents benchmark results that demonstrate HNSW’s superior speed‑accuracy trade‑off.
This article walks through the complete workflow of turning split text into high‑dimensional vectors, choosing the right embedding model, selecting an appropriate similarity metric, comparing index structures such as Flat, IVF, HNSW and PQ, and finally picking a vector database and integrating it with LangChain.js for production‑grade RAG pipelines.
This article explains how embeddings turn text into high‑dimensional vectors, compares commercial and open‑source embedding models, details cosine, Euclidean and inner‑product similarity metrics, reviews common index structures such as Flat, IVF, HNSW and PQ, and shows how to choose and use a vector database with LangChain.js while avoiding typical pitfalls.
This article explains what a vector database is, how it stores and retrieves high‑dimensional vector data, outlines its key characteristics and indexing mechanisms, compares it with traditional databases, and reviews common open‑source vector database solutions such as Milvus, Faiss, Weaviate, PgVector, Chroma, LanceDB, Elasticsearch and Qdrant.
When a table exceeds a billion rows, creating a primary key clustered index can turn a routine operation into a disaster, but by using OFFLINE mode, MAXDOP, SORT_IN_TEMPDB, proper tempdb sizing, and a detailed runbook, you can safely rebuild the table with minimal impact.
This guide explains MySQL DDL mechanisms, compares COPY, INPLACE, and INSTANT algorithms across versions, details Online DDL execution phases and risks, and provides practical production‑grade indexing practices and interview Q&A for handling large tables without blocking reads or writes.
This tutorial note walks through Python variables, demonstrates how lists can store multiple heterogeneous items, explains list declaration, indexing, modification, and shows two ways to iterate over a list with concrete code examples and their output.
The article explains why locally fast MySQL queries can become painfully slow in production, identifies three common pitfalls such as ineffective indexes, oversized Go connection pools, and hidden type conversions, and provides concrete optimization techniques spanning index design, query planning, Go‑MySQL integration, and systematic self‑checks.
MySQL relies on B+Tree indexes for faster I/O, easy range queries, and stable performance, and InnoDB stores the primary key as a clustered B+Tree where ordered keys prevent page splits that would otherwise degrade speed.
This tutorial explains Python's string indexing and slicing, covering zero‑based indices, the start:end:step syntax, negative indices, step rules, and multiple concrete examples that demonstrate how to extract and manipulate substrings.
This article walks through building a production‑grade system for storing and querying massive GPS trajectory data with MongoDB, covering data modeling choices, index strategies, sharding, query patterns, performance tuning, and best‑practice checklists to handle billions of points efficiently.
When a Spring Boot application reaches millions of rows, it often suffers from OutOfMemoryErrors, slow queries, and high CPU, but by applying five proven strategies—pagination, streaming, batch processing, indexing, and asynchronous execution—you can halve memory usage and achieve up to ten‑fold speed gains.
The article explains how deep pagination on massive MySQL tables causes full‑table scans, massive I/O, and memory pressure, then presents six concrete optimization techniques—including delayed join, cursor pagination, covering indexes, ID‑range pagination, caching, and partitioning—backed by a real‑world e‑commerce case study and detailed execution‑plan analysis.
This article explains how Lance's Python API creates scalar and vector indexes, walks through the internal Rust implementation of the create_index workflow, and details the transaction, commit, and error‑handling mechanisms that ensure atomic and consistent index creation.
This guide walks through five systematic checks—indexing delay vs refresh, thread‑pool rejections, segment merges and I/O, bulk size and refresh settings, and mapping/_source configuration—to pinpoint and resolve Elasticsearch write‑performance bottlenecks.
This guide explains what PostgreSQL execution plans are, how they are generated, and how to interpret them using EXPLAIN and EXPLAIN ANALYZE, then demonstrates three real‑world case studies that show step‑by‑step optimizations with indexes, configuration tweaks, and query rewrites to dramatically improve query speed.
This article analyzes Milvus—its open‑source origins, three deployment modes, four‑layer architecture, eight‑plus indexing algorithms, real‑world case studies, and a detailed comparison with competitors—highlighting its strengths, weaknesses, common pitfalls, and when it’s the right choice for large‑scale AI workloads.
This article demonstrates how to use MySQL's JSON data type and spatial extensions to store semi‑structured and geographic data, create indexes, write optimized queries, and combine both features to build flexible GIS solutions, with complete code examples and performance tips.
This article presents eleven practical MySQL advanced techniques—including execution‑plan analysis, sophisticated indexing, window functions, CTEs, JSON handling, partitioning, join optimizations, user variables, online DDL, and generated columns—each explained with real‑world examples, code snippets, and performance caveats to help developers move from basic usage to mastery.
This article explains core database design concepts—from relational vs. NoSQL choices and normalization rules to indexing strategies, ACID transactions, SQL optimization, and sharding—using supermarket‑style analogies and concrete exam‑style examples to help readers build fast, safe, and scalable data stores.
This article explains how backend engineers should rethink table design, indexing, partitioning, and key strategies when migrating attendance data from MySQL's OLTP model to Apache Doris 2.1.7's OLAP architecture, providing concrete DDL examples and practical tips.
This guide explains why solid MySQL design matters, walks through the three normal forms with concrete SQL examples, covers primary keys, foreign keys, indexes, various table relationships, an online‑store case study, design recommendations, and performance‑tuning techniques.
This guide walks you through diagnosing MySQL bottlenecks, enabling slow‑query logging, using pt‑query‑digest, optimizing indexes, tuning parameters, handling pagination, sharding, and troubleshooting deadlocks, providing concrete commands, scripts, and real‑world examples to boost query speed from seconds to fractions of a second on massive datasets.
When Elasticsearch Bulk API reports successful writes and Count shows documents, but Search and Get return empty _source, a six‑hour investigation reveals the root cause is a disabled _source mapping and provides a step‑by‑step debugging checklist and fix.
The article reveals seven common PostgreSQL architectural oversights—such as neglecting vacuum, misusing UUID primary keys, treating the database as a queue, missing indexes, over‑relying on ORMs, ignoring write‑side scaling, and failing to partition large tables—that silently degrade performance and reliability, and provides concrete fixes and best‑practice configurations.
This article explains how to use Redis 8.4.0 for vector recall and keyword filtering, covering index selection (FLAT vs HNSW), schema creation with redisvl, full‑text BM25 search, pure KNN vector queries, hybrid text‑plus‑vector retrieval, query cleaning, score fusion, and optional in‑Redis Lua re‑ranking or TAG‑based filtering extensions.
This article walks through a real e‑commerce MySQL performance crisis, showing how to pinpoint bottlenecks, analyze slow‑query logs, use EXPLAIN, add composite indexes, rewrite SQL, apply partitioning, read/write splitting and caching, and achieve sub‑second response times with 99% CPU reduction.
Using SELECT * in MySQL queries increases disk I/O, inflates network traffic, and prevents the optimizer from using covering indexes, leading to slower performance and unnecessary resource consumption.
This guide walks you through real‑world MySQL slow‑query problems, showing how to enable logging, analyze execution plans, design single‑ and composite indexes, use partitioning, rewrite queries, and set up monitoring scripts, achieving performance jumps from 800 ms to just 8 ms.
This article explains how improper use of NULL in MySQL columns can lead to serious production incidents, outlines official recommendations against NULL, demonstrates its impact on queries, indexes, and storage, and provides practical guidelines for when to use or avoid NULL values.
This article walks through a real‑world case where a batch‑score query API that originally took up to 20 seconds was systematically optimized through index tuning, multithreaded execution, and request‑size limiting, ultimately achieving sub‑second response times.
This article explains Apache Hudi’s core concepts, including its timeline architecture, file layout, indexing mechanisms, and the two primary table types—Copy on Write and Merge on Read—along with their trade‑offs and the various query modes such as snapshot, time‑travel, and incremental queries.
Using random UUID primary keys forces PostgreSQL to write to unpredictable index pages, causing heavy CPU usage, large index size, and dramatically higher insert latency, while switching to a sequential bigint key restores performance and reduces write amplification.
This article examines why traditional MySQL LIMIT offset pagination degrades performance on massive tables and presents five database‑level solutions plus engineering best practices—including delayed joins, covering indexes, cursor pagination, window functions, and partitioning—to achieve fast, scalable paging.
This guide explains why Meilisearch is a lightweight alternative to Elasticsearch, walks through Docker‑based installation, demonstrates index creation, settings configuration, document CRUD operations, and various search queries, all with concrete curl commands and code examples.
This guide explores the fundamentals of database indexing, answering ten common questions about index design, performance pitfalls, composite indexes, covering indexes, NULL handling, and maintenance, while providing practical SQL examples, B+‑tree concepts, and best‑practice recommendations for MySQL and PostgreSQL.
This guide walks you through a complete, step‑by‑step process for identifying why MySQL CPU usage jumps to 100%, from initial symptom verification and data‑flow analysis to locating slow queries, killing them, optimizing SQL, adding indexes, and setting up monitoring and alerts to prevent recurrence.
When faced with 50 million user rows, using LIKE '%1234' triggers a full table scan, but adding a suffix column, reversing the phone number, leveraging function indexes, or integrating Elasticsearch can reduce query time from minutes to a few milliseconds, each with its own trade‑offs.
This comprehensive guide walks you through MySQL InnoDB's core architecture, storage structures, indexing mechanisms, transaction and concurrency control, crash recovery, backup options, and practical performance‑tuning techniques, providing clear explanations and real‑world SQL examples.
This article explains how defining MySQL table columns as NOT NULL—rather than allowing NULL—can improve query speed, reduce storage consumption, simplify indexing and aggregation, make application code cleaner, and enhance data consistency, while also noting scenarios where NULL is appropriate.
The article explains why using the KEYS command in Redis is a blocking operation that can cripple production systems, demonstrates the safe, incremental SCAN approach, and proposes an index‑based architecture or replica scans for high‑frequency or offline key‑lookup scenarios.
StarRocks 4.0 introduces FlatJSON, a columnar storage and execution engine that converts high‑frequency JSON fields into native columns, dramatically reducing I/O and CPU costs and enabling JSON queries to run with performance close to that of traditional columnar data.
This article explains what OLTP (Online Transaction Processing) is, outlines its key characteristics, and details how MySQL—through ACID‑compliant transactions, the InnoDB storage engine, various indexing strategies, fast locking mechanisms, query optimization, and high‑availability features—effectively supports high‑concurrency, low‑latency transactional workloads.
After disabling Redis in production and carefully testing on PostgreSQL 18, the author reduced p95 latency, simplified the system, and lowered operational overhead by replacing the cache with a covering index, a generated column, and a materialized view, while providing detailed query examples, configuration tweaks, and performance measurements.
This article examines why PostgreSQL is often a better choice than MySQL for high‑performance workloads, covering architectural differences, indexing capabilities, query optimization, data types, concurrency control, benchmark results, and migration considerations, and provides code examples to illustrate each point.
This article explains how MySQL indexes work, why wrapping indexed columns in functions can implicitly mask them, and how adding logical or domain‑knowledge‑based redundant conditions can unlock index usage to dramatically improve query efficiency.
This article compares PostgreSQL and MySQL across architecture, indexing, query optimization, data types, concurrency control, and real‑world benchmarks, showing why PostgreSQL generally outperforms MySQL in high‑throughput, complex‑query, and data‑intensive applications.
This article compares PostgreSQL and MySQL across architecture, indexing, complex query optimization, data‑type support, transaction handling, and real‑world benchmark results, showing why PostgreSQL is generally the better choice for high‑concurrency, data‑intensive applications.
This article walks through the evolution from binary search trees to balanced trees, B‑trees and finally B+ trees, explaining how MySQL's InnoDB and MyISAM storage engines implement these structures to reduce disk I/O and boost query performance.
This article walks through a MySQL 5.6 scenario where a sub‑query to find students scoring 100 runs for hours, demonstrates how adding single‑column indexes cuts execution time dramatically, explores join‑based rewrites, composite indexes, left‑most prefix rules, covering indexes and sorting optimizations, and provides practical performance‑tuning tips.
This article walks through a MySQL 5.6 scenario with three tables, demonstrates why a sub‑query that scans millions of rows runs for hours, and shows step‑by‑step how adding single‑column, composite, and covering indexes, as well as rewriting the query as a join, reduces execution time from over eight hours to a few milliseconds.
This article explains why the industry advises keeping MySQL single‑table row counts below ten million by examining InnoDB’s 16KB page structure, B+‑tree indexing mechanics, fan‑out calculations, and how page size and row size together determine the practical limits and performance cliffs of large tables.
This article explains how MySQL stores data in 16KB pages, uses B+‑tree indexes to locate rows, derives the commonly cited 20‑million‑row limit from page fan‑out and row size calculations, compares B‑tree and B+‑tree structures, and offers practical optimization tips.
This comprehensive guide walks you through diagnosing MySQL slow queries, from identifying root causes and configuring slow‑query logs to applying advanced indexing, query‑rewriting, and monitoring techniques—complete with real‑world case studies that demonstrate how to cut query times from seconds to milliseconds.
This article explains why a MySQL GROUP BY query can degrade from seconds to minutes as data grows, shows how to locate the bottlenecks with EXPLAIN and profiling tools, and provides practical indexing, query rewriting, temporary‑table tuning, batch aggregation, and distributed‑database strategies to restore performance.
In this detailed case study, the author walks through a real‑world MySQL slow‑query incident, analyzes execution plans, logs, and profiling data, and applies a four‑step optimization strategy—including index creation, query rewriting, covering indexes, and materialized views—to reduce execution time from 20 seconds to 200 milliseconds, achieving over 100× speedup.
This article explains the fundamentals of database indexes, how they work, common pitfalls such as slow queries and index misuse, and provides practical solutions, best‑practice design principles, and differences between MySQL and PostgreSQL, all illustrated with clear SQL examples and diagrams.
When a massive e‑commerce order table suffers from MySQL’s deep pagination slowdown, this article dissects the root causes and presents six battle‑tested solutions—including cursor pagination, deferred joins, covering indexes, partitioning, precomputed pages, and Elasticsearch integration—to dramatically improve query performance.
This article walks through a real-world slow‑SQL case, explains the inner workings of Join and Order‑by algorithms, demonstrates using EXPLAIN and optimizer_trace to trace execution, and shows how adding indexes and choosing the right join method can dramatically improve MySQL query performance.