This article investigates why MySQL discourages UUID or non‑sequential Snowflake IDs as primary keys, compares auto_increment, UUID, and random‑key tables through SpringBoot‑JdbcTemplate benchmarks, analyzes their index structures, and explains the performance trade‑offs and security considerations.
This article investigates MySQL's recommendation against using UUIDs or non‑sequential keys as primary keys by creating three tables, benchmarking insert and query speeds with Spring Boot/JdbcTemplate, analyzing index structures, and discussing the trade‑offs of auto‑increment, UUID, and random long keys.
Benchmarking three MySQL tables—auto_increment, UUID, and random long keys—shows that sequential auto_increment inserts are fastest, random keys are slower, and UUIDs dramatically lag due to random I/O and fragmentation, so use auto_increment for most workloads and reserve UUIDs only for required global uniqueness.
This article provides a step‑by‑step guide to essential MySQL operations, covering connection methods, database creation and deletion, user management, password changes, data manipulation, query techniques, indexing strategies, performance tips, and backup procedures, all illustrated with complete command examples.
This article explains how to improve MySQL query performance by optimizing ORDER BY, GROUP BY, and LIMIT operations, covering index usage, file‑sort algorithms, covering indexes, prefix indexes, and practical code examples for large‑scale data sets.
After upgrading a client from MySQL 5.7 to 8.0, query performance slowed dramatically due to changes in ORDER BY handling, and the article analyzes the root cause, presents verification tests, and recommends adding indexes to restore millisecond‑level response times.
This article explores advanced SQL query optimization techniques for large datasets, covering sorting methods, GROUP BY improvements, pagination strategies, covering indexes, and prefix index considerations, with practical examples, EXPLAIN analyses, and recommendations to reduce filesort usage and enhance performance.
This article presents ten practical Elasticsearch performance‑tuning techniques—including query DSL, deep pagination, mapping design, high‑cardinality aggregations, nested queries, script optimization, index templates, force‑merge, bulk writes, and profiling—each illustrated with concrete scenarios, code snippets, and step‑by‑step analysis to boost cluster speed and stability.
This article explains MySQL covering indexes, how table lookups (back‑table operations) work, and the leftmost prefix rule for composite indexes, providing practical examples and tips for optimizing query performance in interview scenarios.
This article explains the concept of database indexes, compares various index structures such as hash tables, ordered arrays, binary trees, B‑trees and B+‑trees, details InnoDB primary, secondary, covering and composite indexes, discusses prefix indexes and order‑by optimization, and provides practical advice and SQL examples for improving MySQL query performance.
This article explains how multi‑table joins in MySQL can become performance bottlenecks and walks through the four join algorithms—Simple Nested‑Loop, Block Nested‑Loop, Hash, and Index Nested‑Loop—showing their execution plans, code examples, and step‑by‑step optimizations using indexes and join buffers to dramatically improve query speed.
This article provides a step‑by‑step guide on setting up Xunsearch with PHP to achieve real‑time full‑text search and automatic index updates, covering environment preparation, server installation, search script implementation, and index‑updating routines.
This article explores MySQL's join processing methods—Simple Nested‑Loop, Block Nested‑Loop, Hash, and Index Nested‑Loop joins—demonstrates how to create test tables, analyze execution plans, and apply practical optimizations such as indexing and query rewriting to dramatically improve multi‑table query performance.
This article explains how MySQL processes multi‑table joins, compares Simple Nested‑Loop, Block Nested‑Loop, Hash, and Index Nested‑Loop join algorithms, and demonstrates practical optimization techniques using indexes, join buffers, and query rewrites to dramatically improve performance on large datasets.
This article explains why indexes are essential for query performance, lists the most common scenarios for creating single‑column, unique, composite, and prefix indexes, provides concrete SQL examples, and shows how to verify index usage with EXPLAIN.
This article explains why MySQL adopts B+ trees for its indexes, examining the impact of disk I/O latency, comparing B‑tree variants, and showing how B+ trees reduce I/O operations while supporting efficient range queries and balanced performance for large datasets.
This article outlines fifteen typical MySQL pitfalls—including missing WHERE clauses, lack of indexes, improper NULL handling, wrong data types, deep pagination, missing EXPLAIN analysis, charset misconfiguration, SQL injection risks, transaction misuse, collation issues, overusing SELECT *, index loss, frequent schema changes, missing backups, and unarchived historical data—and provides concrete examples and best‑practice solutions to improve performance, reliability, and security.
The article outlines twelve major performance and architectural issues in Magento 2—including a heavy core codebase, inefficient EAV database design, slow admin UI, over‑reliance on caching, poor front‑end performance, resource‑intensive cloud requirements, indexing bottlenecks, limited scalability, third‑party extension risks, testing difficulties, slow deployment, and a steep developer learning curve.
The article explains how to troubleshoot extreme CPU usage in MySQL and Java processes, detailing step‑by‑step diagnostics with top, show processlist, jstack, and practical fixes such as adding missing indexes, disabling slow logs, using caches, and correcting busy‑loop code to bring CPU consumption back to normal levels.
This article walks through practical MySQL performance tuning methods—including pagination, join, subquery, ORDER BY, GROUP BY, and COUNT optimizations—illustrated with real‑world data volumes, step‑by‑step SQL examples, EXPLAIN analyses, index creation, and measurable query‑time improvements.
This article introduces RediSearch—a Redis module for full‑text search—detailing its features, performance comparison with Elasticsearch, index‑building and query benchmarks, installation methods (source and Docker), and comprehensive command‑line examples for creating, querying, updating, and managing indexes.
This article presents nine practical strategies—including adding default filters, limiting page size, reducing joins, optimizing indexes, using straight_join, archiving data, leveraging count(*), querying ClickHouse, and implementing read‑write splitting—to dramatically improve the speed and scalability of pagination APIs in MySQL‑based back‑ends.
A developer’s costly mistake using MySQL’s INSERT INTO SELECT for data migration led to massive OOM errors, a full‑table scan, lost payment records, and ultimately a $10 000 loss and termination, highlighting the need for proper indexing and careful testing.
Bilibili transformed its search indexing pipeline by replacing a manual, low‑throughput process with a distributed KV store (Taishan) and Spark‑based construction, achieving unified data ingestion, reduced resource consumption, faster full‑ and incremental builds, and a shift from daily to hourly indexing cycles.
This article introduces vector databases as the foundation for efficient high‑dimensional data retrieval in generative AI, covering their background, Milvus’s cloud‑native architecture, key indexing techniques, performance‑trade‑offs, AI‑driven optimizations, and a Q&A session.
This article explains why MySQL queries become slow—covering index misuse, MDL locks, flush waits, large‑table bottlenecks, and read/write splitting—then shows how ElasticSearch’s inverted index and HBase’s column‑family design can complement MySQL for faster search and scalable storage.
This article provides a comprehensive technical guide on MySQL internals, covering the underlying data structures of indexes (B‑tree, B+‑tree, hash), how execution plans are generated and interpreted, the four transaction isolation levels, MVCC implementation, and the InnoDB buffer‑pool architecture.
This article compiles comprehensive MySQL best‑practice guidelines covering charset selection, storage engine choice, naming conventions, field design, index optimization, SQL development tips, and operational procedures to improve performance, maintainability, and reliability of database systems.
This article outlines a series of backend interface performance optimization strategies—including batch processing, asynchronous execution, caching, preprocessing, pooling, parallelization, indexing, transaction management, program structure refactoring, deep pagination, SQL tuning, and lock granularity—to help developers reduce latency and improve system efficiency.
This comprehensive guide explains MySQL indexing fundamentals, B+Tree advantages, index classifications, optimal creation strategies, and how to use EXPLAIN to diagnose and fine‑tune queries for maximum performance while avoiding common pitfalls.
This article details Bilibili's transformation of its search offline indexing architecture—from a manual, low‑throughput MySQL‑centric process to a distributed, KV‑based, protobuf‑driven pipeline that leverages Taishan storage and Spark, cutting build cycles from days to hours while solving performance, consistency, and maintenance challenges.
By refactoring the station‑profile index to eliminate over‑indexed and mis‑typed fields—cutting complex types from 282 to 74 and keywords from 67 to 59—the team lowered CPU peaks from 60 % to 50 %, reduced average CPU to 20 %, cut query latency to 150 ms, accelerated Flink sync to 10 minutes, and decommissioned two nodes, achieving substantial performance gains and cost savings.
Tencent’s real‑time lakehouse combines Spark, Flink, StarRocks and Presto compute layers with Iceberg‑based management and HDFS/COS storage, and its Intelligent Optimize Service—comprising Compaction, Expiration, Cleaning, Clustering, Index and Auto‑Engine modules—automatically reduces merge time, improves query performance, enables secondary indexing, and dynamically routes hot partitions, while future plans target cold/hot separation, materialized view acceleration, and AI‑driven optimizations.
This article walks through Elasticsearch’s licensing history, version selection, installation, cluster health monitoring, shard routing, storage mechanisms, refresh and translog processes, segment merging, and practical performance optimizations such as disk choices, index settings, and JVM tuning.
This article presents eight practical optimization recommendations for the indexing stage of enterprise‑level Retrieval‑Augmented Generation (RAG) applications, covering chunk creation, abbreviation handling, multimodal document processing, semantic enrichment, metadata usage, alternative index types, and embedding model selection.
This guide covers practical MySQL performance improvements, including choosing proper data types, avoiding SELECT *, optimizing joins and UNION usage, batch inserts, query cache settings, configuration parameters like innodb_buffer_pool_size, and comprehensive index strategies to accelerate queries and reduce resource consumption.
This article provides a thorough guide to MySQL performance optimization, covering general query improvements, proper data types, index usage, configuration parameter tuning, avoiding common pitfalls, and detailed EXPLAIN analysis, with concrete SQL examples and practical recommendations for developers and DBAs.
This article provides a comprehensive set of ten beginner-friendly Python string exercises covering indexing, slicing, step slicing, searching, replacing, concatenation, formatting, membership testing, length calculation, case conversion, and advanced topics like regular expressions, splitting, padding, stripping, digit checking, and practical text‑analysis examples.
This article explains Apache Hudi’s write‑side indexing, detailing the indexing API, various index types—including simple, Bloom, bucket, HBase, and record‑level indexes—and their mechanisms, helping readers understand how Hudi validates record existence and optimizes updates and deletions.
This article explains what constitutes a slow SQL query, how to detect and analyze it using MySQL tools, and provides practical optimization steps—including indexing, business logic review, caching, scheduling, and partitioning—to dramatically reduce execution time.
This article provides a comprehensive overview of Apache Doris, explaining its frontend and backend nodes, storage structures such as tablets, rowsets, and segments, replication mechanisms, partitioning versus bucketing, indexing types, compaction processes, and high‑availability strategies through a detailed Q&A format.
Learn eight practical MySQL optimization strategies—including smarter LIMIT usage, avoiding implicit type conversion, rewriting subqueries with JOINs, handling mixed sorting, eliminating costly EXISTS clauses, pushing conditions, pre‑filtering data, and leveraging intermediate result sets—to dramatically reduce query execution time from seconds to milliseconds.
The article outlines practical backend interface performance optimizations—including proper indexing, SQL tuning, parallel remote calls, batch queries, asynchronous processing, scoped transactions, fine-grained locking, pagination batching, multi-level caching, sharding, and monitoring tools—to dramatically reduce latency and improve throughput.
To avoid common MySQL pitfalls, the article compiles practical SQL writing guidelines—selecting specific columns instead of *, using LIMIT 1, avoiding OR in WHERE, optimizing pagination, handling LIKE patterns, minimizing redundant indexes, preferring INNER JOIN, and leveraging covering indexes and EXPLAIN for efficient query execution.
This comprehensive guide walks backend developers through practical techniques—such as proper indexing, SQL refactoring, parallel remote calls, asynchronous processing, caching layers, sharding, and monitoring—to diagnose and dramatically improve API response times and overall system throughput.
This article reviews the latest vector database research showcased at SIGMOD 2024, covering system designs such as Starling, Vexless, RaBitQ, and ACORN, and discusses current academic hotspots including query processing, index structures, optimization techniques, and hardware acceleration for large‑scale similarity search.
This article explains ClickHouse’s columnar, MPP‑based design, block compression, LSM pre‑sorting, sparse and skip‑list indexing, and vectorized execution, while also discussing its high‑frequency write challenges, concurrency limits, and production‑grade issues such as Zookeeper load and resource management.
This article explains how vector databases store and query high‑dimensional embeddings, compares them with standalone vector indexes, outlines common embedding types and indexing algorithms, and discusses performance, monitoring, security, and API considerations for building robust AI‑driven systems.
This article explains how to leverage Redis Stack modules such as RedisJSON, RediSearch, and RedisTimeSeries to store, index, and query high‑dimensional vectors for image‑search services, providing Go code examples for index creation, bulk insertion, memory inspection, and K‑Nearest‑Neighbour vector searches with optional filtering.
This article demonstrates how to set up Xunsearch, a C++-based full-text search engine, and integrate it with PHP to achieve real-time search capabilities and automatically update the index, covering environment preparation, installation, sample code for searching, and index maintenance.
This article explains how to install Xunsearch, create indexes, and implement search functionality using PHP, providing step‑by‑step instructions and sample code to enhance search speed and accuracy for developers building web applications.
This article walks through generating tens of millions of rows in MySQL, then demonstrates a series of optimizations—including simple and covering indexes, data reduction, small‑table‑driven joins, and forced indexes—showing how each technique impacts query performance and EXPLAIN output.
This article explains how to accelerate slow MySQL join queries in high‑traffic e‑commerce fulfillment systems by using wide tables, MySQL JSON columns, virtual columns, and indexed expressions, comparing their trade‑offs and providing practical SQL examples.
This article analyzes MySQL's recommendation to avoid UUID or non‑sequential random primary keys by benchmarking insert performance across three tables—auto_increment, UUID, and random key—explaining the underlying index structures, observed speed differences, and the trade‑offs of each approach.
The article walks through real interview scenarios where MySQL or Java processes consume 900% CPU, detailing step‑by‑step diagnosis with top, processlist, jstack, and concrete remediation such as adding indexes, using caches, fixing busy loops, and tuning memory.
This article explores how to retrieve sales statistics for multiple business units and their associated products in MySQL, compares loop‑based, OR‑concatenated, mixed‑filter, and SQL‑92 row‑comparison techniques, and explains why the row‑comparison method was ultimately chosen for its performance and compliance with development constraints.
This article recounts a costly MySQL data‑migration failure caused by an unchecked INSERT INTO SELECT operation, analyzes why full‑table scans and transaction isolation led to payment‑record loss, and offers practical safeguards such as proper indexing and careful use of the statement.
This article introduces the lightweight Python search library Whoosh, outlines its features, explains how to define schemas, create indexes, and perform queries with example code, and compares it to larger search engines, making it suitable for small search projects.
This comprehensive guide introduces Elasticsearch’s core concepts, installation, REST API operations, and practical examples of indexing, updating, deleting, bulk processing, searching, filtering, and aggregations, helping readers build scalable, near‑real‑time search and analytics solutions for diverse use cases.
This article introduces Elasticsearch, an open‑source distributed search and analytics engine, explaining its architecture, core concepts such as clusters, nodes, shards, replicas, indices, inverted indexes, documents and fields, and how these components enable fast, scalable searching and data analysis.
This article presents a comprehensive guide to MySQL table design, covering naming conventions, appropriate data types, primary key strategies, field length choices, logical deletion, common fields, indexing rules, normalization trade‑offs, storage engine selection, time type recommendations, and SQL optimization tips.
This article explains Python's list data structure, covering forward and negative indexing as well as slice operations, and provides multiple code examples demonstrating element access, sub‑list extraction, list copying, reversal, and stepwise selection.
This guide outlines comprehensive database testing practices, covering relational and non‑relational types, CRUD validation, index and view checks, empty‑database scenarios, data integrity, design principles, stress testing, and detailed SQL optimization techniques with concrete examples and code snippets.
This guide presents comprehensive MySQL performance‑tuning methods, covering SQL pagination tricks, proper index creation and usage, join and UNION optimizations, slow‑query logging, schema design choices, and hardware recommendations to dramatically improve query speed and resource efficiency.
A large‑scale MySQL table migration using INSERT INTO SELECT caused hidden full‑table scans and lock contention, leading to data loss and a costly OOM incident, but adding the proper index and understanding transaction isolation prevented the failure.
This guide explains ByteHouse’s high‑performance vector search capabilities, covering the background of vector retrieval for LLMs, the limitations of its existing skip‑index architecture, the new vector‑index design with HNSW and IVF, query‑time optimizations, performance benchmarks against Milvus, and future development plans.
A real‑world MySQL case study shows how using INSERT INTO SELECT for large‑scale data migration without proper indexing caused full‑table scans, lock contention, and data loss, highlighting the need for careful query planning and performance testing.
This tutorial explains how to set up Xunsearch with PHP to provide efficient real‑time full‑text search and automatically update the search index, covering environment preparation, server installation, search implementation, and index synchronization steps.
Deep pagination in MySQL becomes a performance bottleneck when tables reach millions of rows, causing excessive row scans and costly index lookups; this article explains its root causes and presents six practical optimization techniques—including keyset pagination, delayed joins, covering indexes, data partitioning, and external search engines—to dramatically improve query speed.
A detailed post recounts a production outage caused by improper index handling in a massive MySQL table, walks through MySQL lock diagnostics, compares index behavior on MySQL and TiDB, and distills practical recommendations for safe DML, DDL, and index management.
This guide explains the step‑by‑step SQL execution sequence, offers practical tips for optimizing multi‑table joins, and provides comprehensive indexing recommendations to improve query performance and reduce resource consumption.
This article presents thirty practical SQL optimization techniques, ranging from avoiding SELECT * and using LIMIT 1 to proper indexing, join strategies, and query rewriting, each illustrated with code examples and clear explanations to help developers write faster, more efficient database queries.
When a user reported errors, I discovered a MySQL table flooded with millions of bad rows from a new group, causing slow queries and crashes; I detail the step‑by‑step investigation, failed attempts with DELETE and lock‑free changes, and the final successful rebuild using temporary tables, DROP, TRUNCATE, and index reconstruction.
This article explains common MySQL performance pitfalls such as large‑offset pagination, implicit type conversion, inefficient UPDATE/DELETE joins, mixed ordering, EXISTS subqueries, predicate push‑down, early data reduction, and intermediate result set push‑down, and provides concrete rewrites that turn multi‑second queries into millisecond‑level executions.
This article presents 18 practical guidelines for backend developers on naming conventions, field types, lengths, primary keys, indexes, storage engines, null constraints, foreign keys, character sets, collations, large fields, redundant fields, and comments to help create efficient, maintainable MySQL tables.
This guide explains how to identify and fix extreme CPU usage—up to 900%—in MySQL and Java processes, covering diagnostic commands, SQL indexing, caching strategies, thread analysis, and code adjustments to stabilize production systems.
This article enumerates the most frequent situations that cause MySQL indexes to become ineffective—such as violating the left‑most rule, using functions or expressions on indexed columns, type conversions, improper LIKE patterns, OR/IN/NOT IN usage, and inequality operators—and demonstrates how to reproduce and verify each case with sample data and SQL scripts.
This article provides a comprehensive overview of Elasticsearch, covering its distributed architecture, key features such as JSON RESTful APIs and multi‑tenant support, core functionalities like full‑text search and aggregations, comparisons with Solr, advanced mapping techniques, various query DSLs, suggestion mechanisms, and practical zero‑downtime reindexing strategies.
This article summarizes essential MySQL concepts—including basic terminology, data types, SQL commands, indexes, views, storage engines, backup methods, and performance tuning techniques—to help developers understand and effectively apply this powerful open‑source relational database in real‑world projects.
Learn how to seamlessly integrate Spring Data Redis Repositories in a Spring Boot 2.7 environment, covering object mapping, CRUD operations, indexing with @Indexed, query‑by‑example, batch saves, and setting expiration using @TimeToLive, complete with code examples and visual data representations.
This article explains MySQL's InnoDB storage engine, covering where data files are kept, the different row formats (compact, redundant, dynamic, compressed), the internal 16 KB page layout, record header fields, overflow handling, and how B‑Tree indexes (clustered and secondary) are built and accessed.
The article demonstrates, through a concrete MySQL example, how adding a seemingly small secondary index can unexpectedly inflate a table’s on‑disk size by up to 80%, explains the underlying InnoDB storage mechanics, and offers practical schema‑design recommendations to mitigate such growth.
This article outlines ten essential MySQL table design best practices, covering requirement analysis, data type selection, normalization, indexing, keys, character sets, storage engines, partitioning, backup, and performance monitoring to improve scalability, maintainability, and efficiency.
This article examines the challenges of querying massive follower lists stored in MySQL, compares limit‑based pagination with tag‑record and range‑limit methods, and offers practical indexing and query‑optimization recommendations for handling tables containing hundreds of millions of rows.
When a system’s data grows, MySQL queries can become slow and cause latency; this article presents practical optimization strategies—including query tuning, pagination, join improvements, subquery replacement, order‑by and group‑by enhancements, and count() efficiency—illustrated with sample tables, code snippets, and performance screenshots.
This article explains the concept, principles, performance benefits, and trade‑offs of MySQL indexes, illustrating with a 200‑million‑row example that shows how indexing reduces query time from days to under a second compared to full table scans.
The guide outlines production‑ready Elasticsearch best practices, covering query tuning such as using shard request cache, filter context, size‑0 aggregations and composite aggregations; write strategies like auto‑generated IDs, bulk API sizing and refresh handling; optimal shard counts, explicit mappings with disabled unnecessary features, and general advice to use explicit index names and stored scripts.
CStore is a Rust‑implemented native graph storage engine designed for large‑scale graph analysis, supporting petabyte‑level data, offering array‑plus‑linked‑list storage, multi‑level indexing, efficient compaction, and providing detailed build instructions and future roadmap for open‑source development.
This article explains how Elasticsearch tokenizes text, why small spelling errors or plural forms can miss matches, and how synonyms improve search recall, comparing the trade‑offs of applying synonyms during indexing versus at query time, with practical code examples and tips for managing large synonym lists.
This article explains how software performance can be improved by balancing time and space trade‑offs through six fundamental techniques—indexing, compression, caching, prefetching, peak‑valley smoothing, and batch processing—and then explores four advanced, parallelism‑focused methods for large‑scale systems.
This article demonstrates how to dramatically improve MySQL query performance by analyzing execution plans, adding appropriate single‑column and multi‑column indexes, rewriting subqueries as joins, and applying covering indexes, showing step‑by‑step measurements and visual explanations.
A real‑world MySQL case study shows how using INSERT INTO SELECT for large data migrations can trigger full‑table scans, lock contention, and massive data loss, emphasizing the need for proper indexing and careful testing before deployment.
A real‑world case study shows how using INSERT INTO SELECT without proper indexing caused full‑table scans, lock contention, and data loss, ultimately leading to a costly incident and a colleague's dismissal.
This comprehensive guide explores MySQL’s architecture, explains how queries are parsed and executed, and provides detailed techniques for optimizing SQL performance through effective indexing, understanding EXPLAIN output, and avoiding common pitfalls that cause index misuse and slow query execution.
This article details the business workflow, system architecture, logical index structures, creation, modification, and search processes of the Car Smart Investment advertising index, and evaluates its performance, demonstrating how billions of ad records are efficiently retrieved within milliseconds.
This article explains the purpose, recommended settings, and tuning tips for MyISAM‑related MySQL variables such as key_buffer_size, read_buffer_size, read_rnd_buffer_size, bulk_insert_buffer_size, myisam_sort_buffer_size, myisam_max_sort_file_size, myisam_repair_threads, and myisam_recover, helping DBAs optimize indexing and query performance.
This article explores common reasons MySQL queries become sluggish—beyond missing indexes—including connection limits, insufficient InnoDB buffer pool size, and query cache issues, and provides practical steps such as profiling, using FORCE INDEX, adjusting max_connections, enlarging the buffer pool, and tuning connection pools to improve performance.
This article explains the concepts of local and global indexes in OceanBase, demonstrates how to create tables and indexes, compares six practical scenarios—including no index, local index, partitioned queries, unique and global indexes—and provides performance metrics to guide database optimization.
This guide explains how to optimize Elasticsearch 7.8 performance by adjusting the refresh interval, replica count, JVM heap settings, bulk API usage, storage devices, mapping design, and query patterns, providing practical commands and visual examples for each step.
This article walks through a practical Pandas solution for handling CSV files without headers, demonstrating how to drop duplicate rows and then promote the first data row to serve as the DataFrame index, complete with code snippets and helpful tips for Python automation.
