MySQL Optimize Tips

Optimize database structure

Optimize data size

→ Reducing the amount of data written to and read from disk

→ Less main memory while their contents are being actively processed during query execution

→ Results in smaller indexes that can be processed faster

Table Columns

• Use the most efficient (smallest) data types possible
• Declare columns to be NOT NULL if possible → better use of indexes and eliminating overhead for testing whether each value is NULL.

Indexs

• The primary index of a table should be as short as possible
• Create only the indexes that you need to improve query performance. Indexes are good for retrieval, but slow down insert and update operations.
• searching on a combination of columns → create a composite index
• the first column in the index should be the one with the most duplicate → to obtain better compression of the index.

Joins

• Declare columns with identical information in different tables with identical data types → same data type
• Keep column names simple,

Normalization

• Normally, try to keep all data nonredundant
• If speed is more important than disk space and the maintenance costs of keeping multiple copies of data

Optimize data type

• For unique IDs or other values that can be represented as either strings or numbers, → numeric values can be stored in fewer bytes than the corresponding strings, faster and takes less memory to transfer and compare them.
• When comparing values from different columns, declare those columns with the same character set and collation wherever possible → avoid string conversions
• For column values less than 8KB in size, use binary VARCHAR instead of BLOB. The GROUP BY and ORDER BY clauses can generate temporary tables, and these temporary tables can use the MEMORY storage engine if the original table does not contain any BLOB columns.
• If a table contains string columns and not access frequently → split to another table and join → When MySQL retrieves any value from a row, it reads a data block containing all the columns of that row (and possibly other adjacent rows). Keeping each row small, with only the most frequently used columns, allows more rows to fit in each data block.
• When you use a randomly generated value as a primary key in an InnoDB table, prefix it with an ascending value such as the current date and time if possible.
• For a table with several columns, to reduce memory requirements for queries that do not use the BLOB column, consider splitting the BLOB column into a separate table and referencing it with a join query when needed
• • Since the performance requirements to retrieve and display a BLOB value might be very different from other data types, you could put the BLOB-specific table on a different storage device or even a separate database instance. For example, to retrieve a BLOB might require a large sequential disk read that is better suited to a traditional hard drive than to an SSD device.
• Rather than testing for equality against a very long text string, you can store a hash of the column value in a separate column, index that column, and test the hashed value in queries. (Use the MD5() or CRC32() function to produce the hash value.)

Optimizing SQL Statements

We’ve found it useful to analyze a poorly performing query in two steps:

• Find out whether your application is retrieving more data than you need. That usually means it’s accessing too many rows, but it might also be accessing too many columns.
• Find out whether the MySQL server is analyzing more rows than it needs.

Optimizing SELECT Statements

• Avoid Using Functions In Predicates
• Avoid Using a Wildcard (%) At the Beginning of a Predicate
• Use DISTINCT and UNION Only If Necessary