Sometimes we may face about performance issues while opening a website. Not only databases but we need to optimize our programming code also.
MySQL Best Practices means few tips to optimize the MySQL query:
- 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.
Read Also: Optimize a MySQL database