The article provides a comprehensive guide to relational database design using MySQL, covering ER modeling, normalization (BCNF and 3NF), storage engine selection, character set and data type choices, as well as index design principles illustrated with a student course selection example.
This article explains the fundamentals of relational database design, covering E‑R modeling with a student‑course example, normalization through BCNF and 3NF, selection of MySQL storage engines, character set and data type choices, and best practices for index design.
This comprehensive guide walks you through MySQL's core architecture, explains why SQL optimization matters, details index types and creation methods, demystifies EXPLAIN output, and provides practical examples and best‑practice tips to dramatically improve query performance.
This article explains relational database design using MySQL, covering ER modeling, BCNF and 3NF normalization, storage engine and charset choices, data type selection, and index design, illustrated with examples and practical guidelines for building a student course selection system.
This tutorial walks through installing Elasticsearch 8.2, configuring the PHP client via Composer, creating a MySQL articles table, indexing its rows into Elasticsearch, and performing basic CRUD operations such as retrieving, deleting, and searching documents using PHP code.
This article explains MySQL's internal architecture and provides twelve practical SQL optimization strategies—including avoiding subqueries, using IN instead of OR, efficient pagination, minimizing ORDER BY, batch inserts, selective column retrieval, proper JOIN handling, and index best practices—to dramatically improve database query performance.
This comprehensive guide explains MySQL's native JSON data type, its advantages over text storage, detailed CRUD operations, indexing strategies, migration from TEXT columns, the powerful Partial Updates feature, performance benchmarks, and a wide range of JSON functions with practical examples.
This article examines why a MySQL query that uses an IN subquery on a massive users table becomes extremely slow, analyzes the execution plan revealing materialized temporary tables and semi‑join optimization, and demonstrates how disabling the optimizer or rewriting the query restores index usage and dramatically improves performance.
This article explains how to identify and resolve performance bottlenecks in MySQL by locating slow queries, analyzing execution plans with EXPLAIN, focusing on type, rows and extra information, using profiling and trace tools, and applying practical optimization techniques illustrated through multiple real‑world cases.
This comprehensive guide explains Elasticsearch’s role in searching structured and unstructured data, covers Lucene’s inverted index, details cluster components, shard and replica mechanics, mapping types, installation steps, indexing workflow, storage strategies, and practical performance optimizations for real‑world deployments.
This article introduces the lightweight pure‑Python search library Whoosh, outlines its key features, demonstrates how to define a schema, build an index from a CSV of Chinese poems, and perform full‑text queries with example code, making it ideal for small search projects.
This article provides a comprehensive introduction to Elasticsearch, covering data types, Lucene fundamentals, cluster architecture, node roles, shard and replica mechanisms, mapping, installation, health monitoring, indexing principles, storage strategies, refresh and translog handling, segment merging, performance tuning, and JVM optimization for large‑scale search applications.
This article provides a detailed overview of Elasticsearch, covering its data model, Lucene foundation, cluster architecture, shard and replica mechanisms, index mapping, installation steps, health monitoring, write and storage processes, segment management, and performance tuning techniques for large‑scale search applications.
The team diagnosed intermittent Elasticsearch write‑timeout failures in their real‑time Flink‑to‑Elasticsearch pipeline as lock contention from frequent duplicate updates to the same document IDs, and eliminated the issue by aggregating binlog events in a 5‑second sliding window to deduplicate writes, adjusting refresh intervals, using async translog durability, and disabling non‑essential fields.
This article introduces the lightweight Python search library Whoosh, explains its features, demonstrates how to define schemas, create indexes from a CSV dataset, and perform full‑text queries with example code, including searching.
This article compiles 66 MySQL interview questions covering fundamentals such as joins, data types, indexes, storage engines, transaction properties, locking mechanisms, replication, sharding, performance tuning, and high‑availability concepts for interview preparation.
This article details Ctrip's large‑scale, real‑time log monitoring solution, covering the overall monitoring architecture, the Clog log system, the CAT tracing platform, and the TSDB metric store, and explains design choices such as write‑heavy indexing, segment‑based storage, and migration to ClickHouse for high‑cardinality data.
This article provides a detailed guide on optimizing MySQL performance, covering SQL statement tuning, index usage, table design normalization, system configuration, hardware choices, and the internal architecture of MySQL to help developers write faster and more efficient queries.
This comprehensive guide explores MongoDB’s core features—including schema flexibility, high availability, sharding, storage engine internals, indexing, and performance tuning—while providing practical examples, configuration tips, and best‑practice recommendations for developers and architects seeking to efficiently deploy and operate MongoDB in production environments.
This article explains the motivation, design principles, implementation details, and performance improvements of the new multi‑modal indexing subsystem introduced in Apache Hudi 0.11.0 for Lakehouse architectures, covering scalable metadata, ACID updates, fast lookups, file listing, data skipping, upsert performance, and future work.
This article explains Bilibili's lake‑warehouse integrated architecture, describing how Iceberg, Z‑Order sorting, and advanced indexing techniques such as BloomFilter and BitMap are used to accelerate queries and improve data organization in large‑scale analytics workloads.
This article explains how to tune Apache Solr by configuring caches, SolrCloud, commit strategies, dynamic fields, indexed versus stored fields, copyField, filter queries, and faceting to achieve maximum search performance for production workloads.
This article introduces essential Python techniques—including list indexing and slicing, string alignment methods, list and dictionary comprehensions, handling variable-length arguments, and using NumPy for linear‑algebra operations—providing clear code examples and explanations for each concept.
This article explains how to identify slow SQL statements, analyze execution plans with EXPLAIN, use profiling and tracing tools, and apply specific optimization techniques such as index tuning, query rewriting, and alternative storage solutions across a range of real‑world cases.
Discover essential Python techniques—from powerful list indexing and slicing, string alignment methods, and list/dict comprehensions to handling variable arguments and performing efficient linear algebra with NumPy—illustrated with clear code examples that boost readability and performance for developers.
This article demonstrates how a functional, persistent key‑value store can be created with just two shell commands—one to append data and another to retrieve the latest entry—while explaining the underlying grep and sed techniques, performance trade‑offs, and basic indexing concepts.
This article explains the design and implementation of StarRocks 2.x Primary Key tables, covering real‑time update mechanisms, write and commit workflows, in‑memory primary indexing, compaction, read‑path optimizations, performance benchmarks, and upcoming features such as partial and conditional updates.
This article provides a comprehensive introduction to Elasticsearch, covering data types, Lucene fundamentals, cluster architecture, node roles, shard and replica mechanisms, mapping, basic usage, health monitoring, indexing workflow, storage strategies, and practical performance tuning techniques.
This tutorial explains what Faiss is, how to install it, construct various indexes such as IndexFlatL2, IndexIVFFlat, and IndexIVFPQ, and demonstrates code examples for building and querying vector similarity search pipelines while discussing speed‑accuracy trade‑offs.
This article explains the structures of B+ trees and skip lists, compares their insertion and search behaviors, and shows why MySQL prefers B+ trees for disk‑based indexing while Redis adopts skip lists for in‑memory operations.
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.
This article provides a comprehensive guide to relational database design with MySQL, covering ER modeling, BCNF and 3NF normalization, storage engine selection, character set and data type choices, and index design principles, illustrated through a student‑course selection example.
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.
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.
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.
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.
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.
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.
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.
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.
This article details practical Elasticsearch tuning steps—adjusting index buffers, thread pools, refresh intervals, and translog settings—to raise average write speed from 3,000 to 8,000 documents per second and maintain stability under load.
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.
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.
Introducing Hudi’s Bucket Index, a hash‑based indexing module that replaces Bloom Filter and HBase indexes, dramatically reducing file reads and writes, improving upsert performance, enabling efficient bucket pruning and joins, and offering practical guidance on design, implementation, and future extensions.
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.
This article investigates MySQL's recommendation to use auto_increment primary keys instead of UUID or random snowflake IDs by comparing table designs, running SpringBoot‑based insert benchmarks, analyzing index structures, and discussing the performance trade‑offs and practical drawbacks of each approach.
This article presents a detailed benchmark comparing MySQL 8.0.19 and MariaDB 10.4.12 on a Windows 10 i7 machine, covering single‑row and batch inserts, various SELECT queries with and without indexes, and analyzes the trade‑off between speed and memory usage.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
This article explains why MySQL queries become slow, how index design and lock contention affect performance, and when to complement MySQL with Elasticsearch for full‑text search or HBase for write‑intensive workloads, providing practical optimization strategies and architectural guidance.
This article walks through a practical approach to safely split a 50 million‑row MySQL history table, add missing indexes, and migrate data in batches using backup, table‑copy, and incremental INSERT operations while avoiding long locks and performance degradation.
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.
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.
This article explains how Kuaishou tackled late data scheduling, costly synchronization, and inefficient back‑fills by adopting Apache Hudi, detailing the pain points, reasons for choosing Hudi, and step‑by‑step implementation to achieve fast, fresh, and scalable data processing.
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.
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.
This article explains the core concepts of B‑trees and B+‑trees, compares them with red‑black trees, details their node definitions and insertion algorithms, and shows why B+‑trees are preferred for database and file indexing due to lower I/O cost and stable search paths.
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.
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.
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.
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.
This article shares practical lessons from three iterations of a data platform, focusing on Elasticsearch and Lucene optimizations that enable cross‑month queries, year‑long data export, and sub‑second query responses for tables handling billions of rows per day.
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.
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.
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.
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.
This article provides a comprehensive collection of MySQL optimization techniques—including storage engine characteristics, data type selection, index design, and query rewriting—targeted at teams without a dedicated DBA, using MySQL 5.5 and InnoDB as the reference platform.
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.
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.
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.
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.
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.
The article explains how to improve indexing of long string columns in MySQL by using generated columns with CRC32/MD5 hashes, functional indexes introduced in MySQL 8.0.13, and prefix indexes, providing code examples and performance considerations.
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.
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.
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.
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.
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.
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.
A latency‑sensitive MongoDB cluster experienced severe jitter and connection exhaustion when multiple background indexes were added sequentially, revealing how index builds overload replica nodes, trigger alerts, and can be mitigated with proper index‑addition strategies and the noIndexBuildRetry option.
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.
This article explores the common causes of MySQL slow queries, explains index usage and pitfalls, introduces ElasticSearch architecture and its fast search capabilities, and outlines HBase's column‑family storage model and suitable use cases for large‑scale data systems.
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.
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.
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.
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.
This article explains what NoSQL means, contrasts it with SQL and relational databases, walks through schema design, primary keys, indexing, and then demonstrates how to implement a lightweight key‑value NoSQL store in pure Python with full command parsing and handling.
This article explains why large data sets must be stored on disk, compares disk and memory access characteristics, introduces the B+ tree as an efficient disk‑based index, and details its structure, search process, and dynamic adjustments for insertions and deletions.
