This guide presents practical recommendations for MySQL and Oracle development, covering table design, appropriate data types, indexing best practices, and efficient SQL patterns to improve performance, maintainability, and security.
Although a users table has an index on the create_time column, a query spanning a broad date range triggers a full table scan, but narrowing the range enables the optimizer to use the index, illustrating how MySQL’s cost‑based optimizer decides between index lookup and table scan.
Distinct in MySQL works like GROUP BY but returns only one row per group; it can use index scans like GROUP BY, falls back to a temporary table when indexes aren't sufficient, and unlike GROUP BY it avoids sorting, making index optimization crucial for large result sets.
This article explains why GROUP BY requires sorting like ORDER BY, how a suitable index can satisfy both operations, and outlines the precise conditions—including single‑table queries, left‑most index prefixes, constant values, and limited aggregates—that allow MySQL to execute GROUP BY using an index scan.
This article explains how to identify table and index fragmentation in MySQL using the Data_free column, and provides two effective repair methods—altering the table engine and running OPTIMIZE TABLE—while warning about resource consumption and recommending a monthly maintenance schedule.
