Categories: MySQL

MySQL Best Practices

Sometimes we may face about performance issues while opening a website. Not only databases but we need to optimize our programming code also.

Avoid SELECT * and use particular fields from a table what you require.
Use ENUM rather than VARCHAR because ENUM type is very fast and compact.
Every table always have an id field i.e. PRIMARY KEY, AUTO_INCREMENT and should be UNSIGNED as no negative value will be inserted.
Do Not ORDER BY RAND() as it takes much time.
While you need one unique row use limit 1.
EXPLAIN to check your SELECT queries for performance.
Most effective methods of improving performance is optimize your queries for query cache.
You can use PROCEDURE ANALYSE() to analyze the columns structures and the data in the table.
Use INDEX while searching fields.
For JOIN use same column type for both columns. While joining a DECIMAL column to an INT column from another table, MySQL is unable to use at least one of the indexes.
Store IP Addresses as UNSIGNED INT.
Use mysql_unbuffered_query() for saving amount of memory and you can start working on result set after getting the first row. You do not have wait until the completion of query.
Smaller columns are always faster.
Right storage engine is always good for better performance.
Use Prepared statements for performance and security reasons.
For performance gain use Object Relational Mapper.
Big query always time consuming. So split the big query into limit wise.
For huge data use multiple tables rather than multiple column.
Fixed-length tables are faster.
If you have any reason to use NULL value then use it, otherwise avoid it bacause NULL columns require additional space.