SQL

Top 10 best practices in MySQL

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

Best practices in MySQL

1. Always use proper datatype

Use datatypes based on the nature of data. If you use irrelevant datatypes 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 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 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 first two character of 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 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 front-end application, let it ORDER the result set. Doing this in SQL may slow down the response time in the multi-user environment.

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 INNODB storage engine.
Choosing 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 * from Table WHERE col=’some value’)
which is faster in response time.

5 Comments
  1. Wonderful goods from you, man. I’ve understand your stuff previous to and you’re
    just too wonderful. I really like what you’ve acquired here,
    certainly like what you’re saying and the way in which you say it.
    You make it enjoyable and you still take care of to keep it smart.
    I cant wait to read much more from you. This is actually a
    wonderful site.

  2. Can you tell us more about this? I’d like to find out more details.

  3. gamefly free trial 4 months ago
    Reply

    I blog frequently and I seriously appreciate your information. The article has truly
    peaked my interest. I will take a note of your site and keep checking for new information about once a
    week. I opted in for your RSS feed too.

  4. natalielise 2 months ago
    Reply

    Hi there, I read your new stuff like every week. Your humoristic style is awesome, keep up the good work!
    pof natalielise

  5. minecraft games 4 days ago
    Reply

    I simply couldn’t go away your web site before suggesting that I
    really loved the standard information a person provide for your guests?

    Is gonna be back continuously to inspect new
    posts

Leave a Comment

Your email address will not be published.

You may also like

Pin It on Pinterest