MySQL Best Practices

  

1. Always use proper datatype:- Use datatypes based on the nature of data. If you use irrelevant data types it may consume more space or may lead to errors.
   Example: Using varchar (20) to store date-time values instead of DATETIME datatype will lead to errors during date-time-related calculations and there is also a possible case of storing invalid data.

2. Use CHAR (1) over VARCHAR(1):- If you string a single character, use CHAR(1) instead of VARCHAR(1) because VARCHAR(1) will take an extra byte to store information

3. Use CHAR datatype to store only fixed length data:- Example: Using char(1000) instead of varchar(1000) will consume more space if the length of data is less than 1000

4. Avoid using regional date formats:- When you use DATETIME or DATE datatype always use YYYY-MM-DD date format or ISO date format that suits your SQL Engine. Other regional formats like DD-MM-YYY, MM-DD-YYYY will not be stored properly.

5. Index key columns:- Make sure to index the columns which are used in JOIN clauses so that the query returns the result fast.
   If you use an UPDATE statement that involves more than one table make sure that all the columns which are used to join the tables are indexed

6. Do not use functions over indexed columns:- Using functions over indexed columns defeats the purpose of the index. Suppose you want to get data where the first two characters of the customer code is AK, do not write
   SELECT columns FROM table WHERE left (customer_code,2)='AK'
   but rewrite it using
   SELECT columns FROM table WHERE customer_code like AK%'
   which will make use of an index which results in faster response time.

7. Use SELECT * only if needed:- Do not just blindly use SELECT * in the code. If there are many columns in the table, all will get returned which will slow down the response time particularly if you send the result to a front-end application.
   Explicitly type out the column names which are actually needed.

8. Use ORDER BY Clause only if needed:- If you want to show the result in the front-end application, let it ORDER the result set. Doing this in SQL may slow down the response time.

9. Choose proper Database Engine-:-If you develop an application that reads data more often than writing (ex: search engine), choose MyISAM storage engine.
   If you develop an application that writes data more often than reading (ex: real-time bank transactions), choose the InnoDB storage engine.
   Choosing the wrong storage engine will affect the performance

10. Use EXISTS clause wherever needed:- If you want to check the existence of data, do not use
    If (SELECT count(*) from Table WHERE col=’some value’)>0
    instead, use EXISTS clause
    If EXISTS(SELECT columns from Table WHERE col=’some value’)
    which is faster in response time.

11. EXPLAIN your SELECT queries:- If you use the EXPLAIN keyword, you can get insight into 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.

12. 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. 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.
    
    // what NOT to do:
    $r = mysql_query(“SELECT * FROM user WHERE state = ‘Alabama'”);
    if (mysql_num_rows($r) > 0) {
    // …
    }

    // muchbetter:
    $r = mysql_query(“SELECT 1 FROM user WHERE state = ‘Alabama’ LIMIT 1”);
    if (mysql_num_rows($r) > 0) {
    // …
    }

13. Use the smallest data types possible:-
    Example:- Use int instead of a bigint.
    Avoid large char (255) text fields when a varchar or smaller char is enough.

Database schema and some tips while designing:-

A good example of MySql Table Schema:-

CREATE TABLE IF NOT EXISTS accountBranding (  id int(11) NOT NULL AUTO_INCREMENT,  accountId int(11) NOT NULL COMMENT 'foreign key from account table',  dashboardLogo varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,  showFreeUserUpgradeInfo tinyint(1) NOT NULL DEFAULT '1' COMMENT '0=hide, 1=show',  created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  createdBy int(10) unsigned DEFAULT '0' COMMENT 'user id if anyone is adding him',  modified timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  modifiedBy int(10) unsigned DEFAULT '0' COMMENT 'user id if anyone is modifying him',  userType tinyint(1) unsigned DEFAULT '0' COMMENT '1 for account admin 2 for rf admin',   PRIMARY KEY (id),   UNIQUE KEY accountId (accountId) ) ENGINE=InnoDB AUTO_INCREMENT=286 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT="This table stores information related to account branding"

Things to keep in mind while designing the schema:-

1. Table and column names should be simple and meaningful.

2. Use correct data type. (tinyInt vs smallInt vs int vs bigInt, char vs varchar vs text)

3. Make sure to add a default value for non-empty entities.

4. Always add comments for all entities.

5. Always add an index on the correct entity. (Avoid adding unnecessary indexes)

6. Prefer tinyInt over enum for numbers. (Example for storing 1,2,3.. etc)

7. Always add comments for the table.

8. Better to use Uppercase for the Keywords.