MySQL Performance Optimization Tips for Web Applications

Database operations are increasingly becoming performance bottlenecks for web applications, and developers need to be aware of MySQL optimization techniques.

1. Optimize Queries for Query Cache

Most MySQL servers have the query cache enabled, which can dramatically improve performance for repeated identical queries. However, certain queries (e.g., those using functions like CURDATE() , NOW() , RAND() ) bypass the cache. Replace such functions with variables to enable caching.

// Query cache disabled $r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()"); // Query cache enabled $today = date("Y-m-d"); $r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

2. Use EXPLAIN on SELECT Queries

Running EXPLAIN before a SELECT shows how MySQL processes the query, revealing index usage and row scans. Adding missing indexes (e.g., on group_id ) can reduce scanned rows from thousands to a handful.

3. Use LIMIT 1 When Only One Row Is Needed

Adding LIMIT 1 stops the engine after finding the first matching row, saving work when only one result is required.

// Inefficient $r = mysql_query("SELECT * FROM user WHERE country = 'China'"); // Efficient $r = mysql_query("SELECT 1 FROM user WHERE country = 'China' LIMIT 1");

4. Index Search Fields

Create indexes on columns that are frequently used in WHERE clauses. Indexed searches can be several times faster than full table scans.

5. Ensure Join Columns Have Matching Types and Indexes

Both sides of a JOIN should be indexed and share the same data type and character set; otherwise, MySQL cannot use the indexes efficiently.

6. Never Use ORDER BY RAND()

Sorting by RAND() forces MySQL to generate a random value for every row and then sort, which is extremely costly. Use alternative methods such as counting rows and selecting a random offset.

7. Avoid SELECT *

Retrieve only the columns you need; selecting all columns increases data transfer and processing time.

8. Always Define an AUTO_INCREMENT INT Primary Key

Each table should have a single integer primary key with AUTO_INCREMENT . Avoid using VARCHAR as a primary key because it degrades performance.

9. Use ENUM Instead of VARCHAR for Fixed Sets

ENUM stores values as tiny integers internally, offering faster lookups and smaller storage for columns with a limited set of possible values.

10. Get Recommendations from PROCEDURE ANALYSE()

Run PROCEDURE ANALYSE() on a table to receive suggestions such as converting VARCHAR to ENUM or adjusting integer sizes based on actual data distribution.

11. Prefer NOT NULL When Possible

Columns defined as NOT NULL avoid extra storage for null‑bitmap bits and simplify comparisons.

12. Use Prepared Statements

Prepared statements improve performance for repeated queries and protect against SQL injection. Example in PHP (mysqli):

// Create prepared statement if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) { // Bind parameter $stmt->bind_param("s", $state); // Execute $stmt->execute(); // Bind result $stmt->bind_result($username); // Fetch $stmt->fetch(); printf("%s is from %s\n", $username, $state); $stmt->close(); }

13. Use Unbuffered Queries for Large Result Sets

mysql_unbuffered_query() streams results row‑by‑row, reducing memory usage but requiring manual result handling.

14. Store IP Addresses as UNSIGNED INT

Store IPs using INET_ATON() and INET_NTOA() (or PHP's ip2long() / long2ip() ) to save space and enable range queries.

$r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";

15. Fixed‑Length Tables Are Faster

Avoid variable‑length columns (VARCHAR, TEXT, BLOB) when possible; fixed‑length rows are quicker to locate and cache.

16. Vertical Partitioning

Split a wide table into two tables: one with frequently accessed columns and another with rarely used columns (e.g., address, last_login) to improve performance.

17. Break Large DELETE/INSERT Statements into Batches

Use LIMIT in a loop to delete or insert rows in small chunks, pausing between batches to avoid locking the table for too long.

while (1) { mysql_query("DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000"); if (mysql_affected_rows() == 0) { break; } usleep(50000); }

18. Smaller Columns Are Faster

Use the smallest appropriate data type (e.g., TINYINT, SMALLINT, DATE) to reduce storage and I/O.

19. Choose the Right Storage Engine

MyISAM is fast for read‑heavy workloads, while InnoDB provides row‑level locking and transaction support for write‑intensive applications.

20. Use an Object‑Relational Mapper (ORM)

ORMs like Doctrine can improve performance through lazy loading and transaction batching, but misuse can cause many small queries.

21. Be Cautious with Persistent Connections

Persistent connections ( mysql_pconnect() ) reduce connection overhead but can exhaust available connections under high concurrency.

Disclaimer: The content is sourced from public channels and is provided for reference only. Copyright belongs to the original authors.