1 views collected around this technical thread.
This guide walks you through identifying MySQL bottlenecks, using EXPLAIN, optimizing indexes, rewriting queries, tuning configuration, applying architectural patterns, and learning from real‑world cases to dramatically speed up your database.
The article explains how to retrieve coupon templates linked to a specific product ID in MySQL, compares a simple LIKE‑based fuzzy search with a more accurate multi‑value JSON index approach, and provides step‑by‑step SQL code and performance considerations.
This article explains how to use MySQL's EXPLAIN command to analyze slow queries, interprets each field in the execution plan, provides practical examples and optimization techniques, and offers advanced diagnostics for improving index usage and overall SQL performance.
A real‑world MySQL data‑migration case study shows how using INSERT INTO SELECT without proper indexing caused a full table scan, OOM errors, and data loss, and explains how to avoid the pitfall by adding appropriate indexes and understanding transaction locking.
This guide introduces Meilisearch, a lightweight, fast search engine with RESTful API, covering its features, Docker installation, index and settings management, document operations, and advanced search queries, while also showcasing a real‑world SpringBoot‑Vue e‑commerce project that integrates Meilisearch for instant, accurate results.
This article provides a thorough tutorial on SQL fundamentals, including statement order, core clause rules, templates for SELECT/INSERT/UPDATE/DELETE, practical multi‑table query cases, join and subquery techniques, common pitfalls, index optimization strategies, experimental performance data, and a structured learning path for mastering database development.
To improve MySQL performance, first identify slow queries via logs, then examine their EXPLAIN plans and profiling data, understand optimizer traces, and apply fixes such as proper left‑most indexing, avoiding implicit conversions, large offsets, unsuitable IN/ORDER BY patterns, range‑blocking, NOT‑IN predicates, and consider alternative storage or pagination strategies for complex or massive datasets.
The case study details how an activity‑middleware platform tackled slow SQL problems by cleaning historic data, introducing sharding and Elasticsearch off‑loading, optimizing queries with proper indexes and simplified joins, and automating nightly deletions, ultimately cutting daily slow queries from thousands to double‑digit levels and boosting system stability.
This article investigates MySQL's recommendation to avoid UUIDs and non‑sequential keys by benchmarking insert performance of auto‑increment, UUID, and random (snowflake) primary keys, analyzing their index structures, drawbacks, and concluding that sequential auto‑increment keys provide the best overall efficiency.
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 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 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 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 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.