• info@thincor.in
  • +91-7592865159

MySQL Database Optimization Tips

blog

Data base is also knows as collection of information or data. It’s the Storage space which is used to store large amount of data. We can store data in the database using tables. Which is further divided in to rows and columns in the table by an index number. So we can manage this table data by using basic CRUD operations. Which we can Create, Read, Update ,Delete as per our requirement. Inside a single database we can store multiple tables with particular rows and columns.

Here is an example of data stored in table named user.

    Columns
       
  

Index  → Id Username Email Phone
Rows → 1 Jack jack@gmail.com  123*******
  2 Jonny jonny@gmail.com 456*******
  3 Roy roy@gmail.com 456*******

 

Above is just an example for table in database, there are can be tons of data stored in such tables. 

When we are think about large enterprise level of application so we can store large amount of data’s inside a database. So we have to keep this large amount of data very securely. In this type of applications or software there a huge number of tables inside a database. This large data base will affects the performance of the application while we are writing basic CRUD operations.

For example; user or browser sends the request to server to get the information using a server side scripting language such as PHP and at time server will find the desired data or information in database and return the information to user or browser.

 In large application the response time such as the data taken from data base to user will take more time. So this speed problem will affect the entire working flow of the website and the site become down. In this situation we are using DATABASE OPTIMIZATION techniques for speed up the performance of any website or web application.

Requirement of Data Optimization

Presently there is huge amount of applications are developing in market and send to the client. So the client has several options to choose the best. There is the biggest thing is the attraction and performance of the website. For these this have an important role in the presentation of the website.

Now a day’s time is also very precious and the user will also evaluate the speed of the website. When a user login into application the working flow and the response time is also very important. So if it is very fast and user friendly it will be popular the industry. So these are the important technique to improve the performance of the website.

Tips to Improve the Performance of Website

1 Keep minimum table:

When we are designing different tables for storing the data’s. For improving the performance we have to remove unnecessary tables. So the database team has to design the table with more columns and has to include more fields in a single table.

2 Keep indexed:

There is also a great advantage for keeping every table is indexed with a primary key. It helps to speed up the database performance.

3 Type of Data:

Always has to decide in mind that to set the appropriate data type to your data which means INT or VARCHAR. Why because INT will take less memory space than VARCHAR.

4 Length of Data Type:

Always has to put relevant length for every column. It is very useful to save the memory space and it will increase the performance.

Wrong Method

 

phone 

VARCHAR 

255

 

5 Selecting Query:

Properly has to write each query’s and better to use join query to retrieve data from different tables.

SELECT * FROM users WHERE user = ‘Mohan’”;    Never use “SELECT *” it slow down the performance. Always use as  

6 Use Limit:

Limit specify the how many records has to be retrieved from the database. Limit has two arguments (start, end), start specify the starting address of the query and second one specify the limit which the means how many records are retrieved from the data base.

7 Avoid unnecessary Query:

When are running a query it will take some time to process it. So manually it will affect the performance. So better to avoid n=unnecessary query’s for controlling traffic.

8 Normalization and Demoralization:

Normalization is a concept in which we have to divide the data’s into multiple tables and we can managed or connected each tables together. When we are using normalization we can avoid duplication of data. There are different normalization types to connect each tables together. Demoralization is a concept in which multiple tables are merged together so that the data retrieve will be more fast.

Leave a Reply

Your email address will not be published. Required fields are marked *