21 Essential MySQL Performance Tips Every Developer Should Know

Today, database operations are increasingly becoming the performance bottleneck for entire applications, especially web applications. Database performance is not only a DBA concern; programmers must also pay attention when designing table structures and writing SQL, particularly for MySQL, the most common web database.

1. Optimize Queries for the Query Cache

Most MySQL servers have the query cache enabled, which is one of the most effective ways to improve performance because the engine stores results of identical queries and serves them from memory on subsequent executions.

Programmers often overlook that certain functions prevent caching. For example, queries containing CURDATE(), NOW(), or RAND() bypass the cache because their results are nondeterministic. Replace such functions with a variable to enable caching.

2. EXPLAIN Your SELECT Queries

Using the EXPLAIN keyword shows how MySQL processes a query, helping you identify performance bottlenecks in the query or table design.

The EXPLAIN output reveals how indexes and primary keys are used, how tables are scanned, and how rows are sorted. Run EXPLAIN on a complex SELECT (preferably one with joins) and examine the rows column to spot inefficient scans.

3. Use LIMIT 1 When Only One Row Is Needed

If you know a query will return at most one row, adding LIMIT 1 lets MySQL stop searching after the first match, reducing unnecessary work.

4. Index Columns Used in Searches

Indexes are not limited to primary keys. Any column frequently used in WHERE clauses should be indexed.

The example shows a LIKE 'a%' search that is four times faster with an index. Note that full‑text searches or patterns starting with a wildcard (e.g., LIKE '%apple%') cannot use a normal index; consider MySQL full‑text indexes or custom indexing solutions.

5. Use Matching Data Types for JOIN Columns and Index Them

When performing JOINs, ensure the joined columns are indexed and have identical data types and character sets; mismatched types (e.g., DECIMAL vs. INT) prevent index usage.

6. Never Use ORDER BY RAND()

Randomly ordering rows with ORDER BY RAND() forces MySQL to evaluate the RAND() function for every row and then sort the entire result set, causing exponential performance degradation.

Alternative methods (e.g., selecting a random primary key range) should be used instead.

7. Avoid SELECT *

Fetching unnecessary columns increases data transfer and slows queries, especially when the database server is separate from the web server. Retrieve only the columns you need.

8. Always Define a Primary Key ID for Each Table

Give every table an INT UNSIGNED AUTO_INCREMENT primary key. Avoid using VARCHAR columns (e.g., email) as primary keys because they consume more space and degrade performance.

Primary keys are also essential for clustering, partitioning, and many engine‑specific optimizations.

9. Use ENUM Instead of VARCHAR for Fixed Sets

ENUM stores values as tiny integers internally while presenting them as strings, making it fast and space‑efficient for columns with a limited set of possible values (e.g., gender, status, country).

MySQL’s PROCEDURE ANALYSE() can suggest converting suitable VARCHAR columns to ENUM.

10. Get Recommendations from PROCEDURE ANALYSE()

PROCEDURE ANALYSE()

examines actual data in a table and offers suggestions such as changing an INT primary key to MEDIUMINT or converting a low‑cardinality VARCHAR to ENUM.

In phpMyAdmin, the “Propose table structure” feature displays these recommendations.

11. Prefer NOT NULL When Possible

Unless there is a compelling reason, define columns as NOT NULL. NULL values require extra storage and complicate comparisons. Be aware of the distinction between empty strings and NULL, especially in Oracle where they are treated the same.

12. Use Prepared Statements

Prepared statements (similar to stored procedures) allow the server to parse a query once and execute it many times with different parameters, improving performance and protecting against SQL injection.

They also reduce network traffic because MySQL can transmit the statement in binary form. Note that older MySQL versions may not cache prepared statements, but support was added after 5.1.

In PHP, use mysqli or PDO to work with prepared statements.

13. Use Unbuffered Queries When Appropriate

Normally, MySQL buffers the entire result set before returning control to the script. mysql_unbuffered_query() streams rows as they are retrieved, saving memory for large result sets.

Unbuffered queries cannot use mysql_num_rows() or mysql_data_seek() and require you to read all rows or free the result before issuing another query.

14. Store IP Addresses as UNSIGNED INT

Instead of a VARCHAR(15), store IPv4 addresses as UNSIGNED INT, which uses only 4 bytes and enables range queries (e.g., IP BETWEEN ip1 AND ip2).

Use INET_ATON() to convert a string to an integer and INET_NTOA() to convert back; PHP equivalents are ip2long() and long2ip().

15. Fixed‑Length Tables Are Faster

Tables composed only of fixed‑length columns (no VARCHAR, TEXT, BLOB) are considered static and allow MySQL to calculate row offsets quickly, improving read speed and cache efficiency.

The trade‑off is potential wasted space for unused fixed‑length fields.

16. Vertical Partitioning

Vertical partitioning splits a wide table into two tables based on column groups, reducing the number of columns accessed in common queries and improving performance.

Example: move an optional address column to a separate table so that most queries on the Users table read only the frequently used columns.

Another example: isolate a frequently updated last_login column to avoid invalidating the query cache for the rest of the user data.

17. Break Large DELETE or INSERT Statements

Large DELETE or INSERT operations can lock tables and stall a web site. Split them into smaller batches using LIMIT to avoid long locks.

18. Smaller Columns Are Faster

Compact data types reduce disk I/O. Use the smallest appropriate integer type (TINYINT, SMALLINT, MEDIUMINT) and prefer DATE over DATETIME when time-of‑day is unnecessary.

Be mindful of future growth; altering a large table can be time‑consuming.

19. Choose the Right Storage Engine

MySQL offers MyISAM and InnoDB. MyISAM excels at read‑heavy workloads but locks the whole table on writes. InnoDB provides row‑level locking, transactions, and better write concurrency, making it preferable for mixed read/write workloads.

20. Use an Object‑Relational Mapper (ORM)

An ORM can improve performance through lazy loading (fetching data only when needed) and by batching statements into transactions. However, excessive lazy loading may generate many small queries and degrade performance.

Popular PHP ORM: Doctrine.

21. Be Cautious with Persistent Connections

Persistent connections ( mysql_pconnect()) keep a MySQL link open across requests, reducing connection overhead but consuming a limited pool of connections, memory, and file handles. In high‑concurrency environments, they can cause resource exhaustion.

Consider the overall architecture before enabling persistent connections.

Source: http://www.uml.org.cn/sjjm/201803272.asp