Mastering MySQL Performance: Efficient SQL, Table Design, and Indexing Strategies

As the Mobile Cloud platform grows, frequent system updates can expose performance issues largely caused by non‑standard development practices. To improve stability, the article examines MySQL’s logical structure—including connectors, services, parsers, optimizer, caches, and buffers—and outlines the query processing flow from cache checking to execution.

Common Factors Affecting MySQL Performance

Key influences include server hardware (CPU, memory, storage I/O), storage engine choice (MyISAM vs. InnoDB), and configuration parameters such as max_connections, key_buffer_size, sort_buffer_size, join_buffer_size, table_open_cache, tmp_table_size, thread_cache_size, and InnoDB‑specific settings ( innodb_buffer_pool_size, innodb_log_buffer_size). Sample commands illustrate how to view and adjust these variables.

Table Design Recommendations

Choose InnoDB for transactional workloads; avoid MyISAM for OLTP.

Adopt a consistent naming convention: lowercase letters, underscores, max 32 characters, avoid reserved words.

Use appropriate data types (e.g., TINYINT, SMALLINT, VARCHAR sized to actual needs) and limit column count to ~20.

Prefer VARCHAR over CHAR, store IPs as integers, and avoid NULL columns when possible.

Consider partitioning (RANGE, LIST, HASH, KEY, composite) for large tables, especially time‑based data.

Separate large or rarely accessed columns (TEXT/BLOB) into auxiliary tables to reduce I/O.

Store data and index files on separate disks for hot tables.

Index Creation Guidelines

Assess index necessity based on table size and query frequency; small tables may not need indexes.

Select high‑selectivity columns (<1% result set) with few NULLs and uniform distribution.

Avoid over‑indexing; each index adds storage and write overhead.

Use unique indexes where possible; they provide the best performance.

Prefer short prefix indexes for long string columns.

Leverage left‑most prefix columns in multi‑column indexes and avoid redundant indexes.

Match index type to query patterns (e.g., =, BETWEEN, range scans) and avoid constructs that prevent index use (OR without indexes, leading % in LIKE, functions on indexed columns, implicit type casts, etc.).

Efficient SQL Writing Practices

Batch inserts using multi‑value INSERT statements instead of separate statements.

Prioritize query or update workloads based on application needs; use LOW_PRIORITY, HIGH_PRIORITY, or DELAYED modifiers accordingly.

Avoid SELECT *; specify needed columns to enable index covering scans.

Prefer INSERT … SELECT alternatives (e.g., SELECT … INTO OUTFILE + LOAD DATA INFILE) to avoid locking.

Commit transactions appropriately to release undo/redo resources and reduce lock contention.

Minimize table‑level lock conflicts by ensuring queries use indexes and by using row‑level locking where possible.

Use SQL_BUFFER_RESULT for large result sets to free locks early.

Apply optimizer hints ( USE INDEX, IGNORE INDEX, FORCE INDEX) sparingly and prefer proper statistics collection.

Optimize GROUP BY and ORDER BY by adding matching indexes or using ORDER BY NULL when sorting is unnecessary.

Rewrite subqueries as joins when they can be executed more efficiently.

Prefer UNION ALL over UNION unless duplicate elimination is required.

Implement pagination using covering indexes (select primary keys first, then join) to keep per‑page cost constant.

Avoid non‑deterministic functions (e.g., NOW(), RAND()) in statements that replicate to slaves.

Using EXPLAIN for Performance Analysis

Running EXPLAIN before a query reveals the execution plan: table, access type, possible and actual keys, key length, rows examined, and extra information (e.g., Using temporary, Using filesort). The article shows examples where adding appropriate indexes reduced execution time from seconds to milliseconds.

Automation with a Slow‑Query Management Platform

An internal platform built on the open‑source yearningSQL tool collects slow queries, applies predefined optimization rules, and presents suggestions. Queries that cannot be auto‑approved are flagged for manual review.

Conclusion

By understanding MySQL’s architecture, tuning server and MySQL parameters, designing tables and indexes thoughtfully, and writing optimized SQL, developers can keep systems stable and performant during continuous updates. Automated slow‑query analysis further streamlines ongoing optimization efforts.