MySQL Best Practices
22/06/2019 . 3 minutes, 9 seconds to read . Posted by Admin#mysql #best-practices
MySQL is open-source relational database management system it is the second most popular tool in the world. It has become so popular because of its consistently fast performance, high reliability and ease of use. Below are some useful best practices for Mysql users.
1. Primary Key
The primary key is the unique value in the table through which most of the time we query data, it can be an auto-increment value if there is no obvious primary key.
2. User Proper DataTypes
Choose proper data types depend on your data. The use of improper data type may load to consume more memory and result in errors.
3. Joins
Join is the relationship between the two tables. Join is used to pull data from multiple tables based on identical ID values from those tables. To execute the query in minimum time, define foreign keys on the join columns, and declare those columns with the same data type in each table. Adding foreign keys ensures that referenced columns are indexed, which can improve performance.
4. Use the CHAR datatype to store only fixed-length data
For example: If the length of the data is less than 1000, using char (1000) instead of varchar (1000) will consume more space.
5. Use SQL SELECT * only if needed
Following on the list of the MySQL best practices, do not just blindly use SELECT * in the code. If the table has many columns, all will be returned. This will slow down the response time, especially if you send the result to a front-end application.
Instead, explicitly type out the column names which are actually needed.
Note: remember that all SELECT statements require a WHERE clause.
6. Use LIMIT 1 when getting a unique row
Sometimes you know in advance that you are looking for just one row when querying your tables. For example, you might be fetching a unique record, or you might just be checking the existence of any number of records that satisfy your WHERE clause.
In such cases, you will want to use the MySQL limit function to increase performance. simply add LIMIT 1 to your query. This way the database engine will not have to go through the whole table or index. It will stop scanning when it finds just 1 record of what you are looking for.
7. Take advantage of query caching
Query caching is one of the most effective methods of improving performance. Most MySQL servers have it enabled by default.
It stores the text of a SELECT statement together with the corresponding result set. If the server later receives an identical statement, it will retrieve the results from the query cache rather than parsing and executing the statement again. A result set generated by one user can be sent in response to the same query issued by another user.
However, great as it is, query caching has its limitations. Take the following statement:
The problem here is that queries contain certain non-deterministic functions, like NOW() and RAND(). MySQL cannot calculate such functions in advance, so they end up not being cached.
Fortunately, there is an easy solution to that: you can store the function results in a variable.
8. Prepared Statements
Prepared Statements filters the variables by default you bind to them, which is great for protecting your application against SQL injection attacks.
In case of when the same query is being used multiple times in your application, you can assign different values to the same prepared statement, yet MySQL will only have to parse it once.