MySQL: MySQL Best Practices to Follow As a Developer

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

MySQL Best Practices to Follow As a Developer

MySQL is the second most popular open-source relational database management system in the world. It is used worldwide because of its consistently fast performance, high reliability, and ease of use. This article presents some of the MySQL best practices.

 

SL No	Description	Do Not	Do		Comments
1	Always use proper datatype	Using varchar (20) instead of DATETIME datatype for storing date time	Use the DATETIME datatype for storing data DATETIME		Also, avoid large char (255) text fields when a varchar or smaller char is enough. If you use the right data type, more records will fit in memory or index key block. This leads to fewer reads and faster performance.
2	Do not use functions over indexed columns…	SELECT columns FROM table WHERE customer_code like ‘AK%’	SELECT columns FROM table WHERE left (customer_code,2)=’AK’		because then the index loses its purpose
3	Use SQL SELECT * only if needed	Select * From MyTableName	Select Column1,Column2 From MyTableName		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.
4	8. Use ORDER BY clause only if needed	Select * From MyTableName Order By ColumnName ASC	Select * From MyTableName.		If you want to show the result in the front-end application, let it ORDER the result set. If you do this in SQL, the response time may be slowed down in the multi-user environment.
5	Use EXISTS clause wherever needed	If EXISTS(SELECT * from MyTableName WHERE col=’some value’)	If (SELECT count(*) from Table WHERE col=’some value’)>0		For checking the existence of data, use EXISTS clause which is faster in response time. For example, use:
6	Use LIMIT 1 when getting a unique row	SELECT Column1,Column2 FROM MyTableName WHERE Column1 IN(SELECT Column1 FROM SecondTableName)   SELECT * FROM user WHERE state = ‘Alabama'	SELECT Column1,Column2 FROM MyTableName WHERE Column1 IN(SELECT Column1 FROM SecondTableName LIMIT 1)   SELECT 1 FROM user WHERE state = ‘Alabama’ LIMIT 1		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. Here is another of the MySQL best practices: 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	Index and use the same column types for joins	Another vital tip of MySQL best practices – if your application has many JOIN queries, make sure that the columns you join by are indexed on both tables. This affects the internal optimization of the Join operation by MySQL. Also, the joined columns need to be the same type. For example, if you join a DECIMAL column to an INT column from another table, MySQL won’t be able to use any of the indexes. Even the character encodings need to be the same type for string type columns.
8	Avoid using functions in predicates	SELECT * FROM TABLE1 WHERE UPPER(COL1)='ABC'	SELECT * FROM TABLE1 WHERE COL1=Lower('ABC')		Because of the UPPER () function, the database doesn’t utilize the index on COL1. If there isn’t any way to avoid that function in SQL, you will have to create a new function-based index or have to generate custom columns in the database to improve performance.
9	Avoid using a wildcard (%) at the beginning of a predicate	SELECT * FROM TABLE1 WHERE COL1 LIKE '%ABC'	SELECT * FROM TABLE1 WHERE COL1='%ABC'
10	Avoid unnecessary columns in SELECT clause	Instead of using ‘SELECT *’, always specify columns in the SELECT clause to improve MySQL performance. Because unnecessary columns cause additional load on the database, slowing down its performance as well whole systematic process.
11	Use inner join, instead of outer join if possible	Use outer join only when it is necessary. Using it needlessly not only limits database performance but also limits MySQL query optimization options, resulting in slower execution of SQL statements.
12	Use DISTINCT and UNION only if it is necessary	Using UNION and DISTINCT operators without any major purpose causes unwanted sorting and slowing down of SQL execution. Instead of UNION, using UNION ALL brings more efficiency in the process and improves MySQL performance more precisely.
13	The ORDER BY clause is mandatory in SQL if you expect to get a sorted result	The ORDER BY keyword sorts the result-set in predefined statement columns. Though the statement brings advantage for the database admins for getting the sorted data, it also produces a bit performance impact in the SQL execution. Because the query first needs to sort the data for producing the final result-set, causing a bit complex operation in the SQL execution.
14	Optimizing MySQL Subqueries	The most important advice I can give you about subqueries is that you must prefer a join where possible, at least in current versions of MySQL. Subqueries are the subject of intense work by the optimizer team, and upcoming versions of MySQL may have more subquery optimizations. Do keep a check on which of the optimizations will end up in released code, and how much difference they’ll make. My point here is that “prefer a join” is not future-proof advice. The server is getting smarter all the time, and the cases where you have to tell it how to do something instead of what results to return are becoming fewer.
15	Use the ALL CLASS Upper Case Note :  Use UPPCASE Instead Of lowercase, sentence case, Capital Each Word	1.      From, including joins 2.      Where 3.      group by 4.      Having 5.      Window functions 6.      select 7.      distinct 8.      union 9.      order by 10.  limit and offset		1.      FROM, Including JOINs 2.      WHERE 3.      GROUP BY 4.      HAVING 5.      WINDOW functions 6.      SELECT 7.      DISTINCT 8.      UNION 9.      ORDER BY 10.  LIMIT and OFFSET

 

 

 

 

 

 

 

 

EXPLAIN your SELECT queries

If you use the EXPLAIN keyword, you can get insight on what MySQL is doing to execute your query. This can help you detect problems with your query or table structures (e.g. bottlenecks).An EXPLAIN query results in showing you which indexes are being utilized, how the table is being scanned, sorted, etc. All you have to do is add the keyword EXPLAIN in front of a SELECT query (preferably a complex one with joins). Also, if you use phpmyadmin for this, your results will be shown in a nice table.

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. The query cache 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. The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client. 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.

Use the smallest data types possible

Let me tell you a story. When I was attending college, the philosophy was that “memory is scarce”. Those were the days of 256 MB hard drives. Nowadays, no one seems to care one iota about memory or hard drive space. The new philosophy is that “memory is cheap”. It might be true in dollar terms, but reading large data types still takes longer than reading smaller ones. Large data types require more disk sectors to be read into memory.

The moral is, ignore the temptation to immediately jump to the largest data type when you design your tables. Think about using an int instead of a bigint.

Turn on delay_key_write

Delay_key_write is turned OFF by default. There is a reason for that. If you experienced a crash in the middle of the project, your database could become corrupt. So, why would you want to turn it on? The reason is simple. Because the delay_key_write ensures that the database will not flush the MyISAM key file after every single write. Therefore, if you are doing another write in the near future, you will be saved quite a lot of time. Here is another cool tip we chose from MySQL best practices: Turning on delay_key_write is different for every version. To see how to turn it on in a specific version, consult the official MySQL site manual.