Why Database Optimization is Necessary ?

Everyone is familiar with a Database concept but not everybody knows what “Database Optimization” does. Also, most of the Database administrations and analysts have the misconception regarding Optimization. I have seen my own pals, who know how to run the optimization but have no any idea why they are doing it.

I used to think Database Optimization is all about managing Speed or Load time of the Web Pages. But one day I came to find out that it is more than that. I’ll try to explain the best I can though I am not a Database expert. Please, help me out with my idea if I’m wrong. 🙂  So why database optimization is necessary for every website ?

Before we start, we should first OBSERVE a complete picture of DB Optimization on What it does, How it Affects our site, and What are its Benefits ?

The Oxford dictionary meaning of DATABASE is A structured set of data held in a computer, especially one that is accessible in various ways:.

Oxford dictionary defines OPTIMIZATION as “The action of making the best or most effective use of a situation or resource”.

In general and in computer terms, the definition is similar.

How Database Works ? And what optimization does ? 

As we know, all the database information is stored in files or we say tables in web term. When a query to delete a post or any other piece of data is executed, it removes the selected data from the table. When this happens, it, therefore, leaves the empty space in the file which takes up a few bytes unnoticed. After a time, this space adds up to huge space and can impact heavily on MySQL or Website performance. That is why we run optimization to clean up those empty spaces, not technically reducing database size.

Sometimes your DB tables can contain a lot of allocated but unused space which is usually created after many plugins installations and deletions. This will, in turn, increases the overhead on your DB and effectively puts it in a non-optimal state.

In conclusion, when the database optimization technique is performed, it does not really reduce your database size but it deletes all the spaces and reclaim the DB size. Therefore, many big companies hire a separate Database analysts especially for Database for the better performance of the site.

Advantages of Database Optimization

Now we have a brief picture of how optimization works in a database. Still many developers, designers, and database analyst, think that the optimization is only for Web Page Load time Speed and faster response time. But the Universal truth is “the sole purpose of DB optimization is to retrieve data/information quickly”.  The optimization, however, has other benefits as well. Some of the Pros of Optimizing DB as far as I know from general research are:

• Faster Processing query
• High performance of the System
• Efficient use of Database engine
• Consumption of less memory
• Reduces data redundancy and updating errors
• Improves Data Security
• Saves database memory sizes

In contrast, there are, however, few disadvantages of Optimization too. Database optimization is not as easy as pie. It sometimes destroys your system like a slow virus. It is time-consuming, difficult, and complex too. If not performed efficiently and effectively, without a doubt, the business of your product will fall into pieces in no time. Furthermore, initial training is required for all the programmers as well as users. It is costly but it’s worth the value. 🙂

How to Optimize Database ?

If you are using WordPress, it’s pretty easy to optimize the database table of the site. There are many Plugins available from which you can optimize your database with few simple clicks. But I prefer the Automatic Database Optimizing tool instead. It’s like an herbal cure for the disease which has no side effects. So here are the steps:

1. Go to your root directory either from Cpanel or FTP and find the  wp-config.php file and add the following code at the end of the code. Do backup your wp-config.php file before editing.

    define( 'WP_ALLOW_REPAIR', true );

2. Go to {$your_site}/wp-admin/maint/repair.php on your browser. For example, I will enter prabinparajuli.com.np/wp-admin/maint/repair.php on my browser and it will redirect to a new optimization page as screenshot shown below:You can either hit the Repair Database or Repair and Optimize Database buttons.

   Please note: make sure you have your database backup before making any changes.

3. Finally, if you run the optimization, your database will be optimized. You will see the status of your database via different messages shown on a new page after the optimization is complete. Do check your site if everything is NORMAL as before and there is no any error. The site should not be broken, that is our first priority. Then you are ready to go.

   Please Don’t Forget To DELETE define( 'WP_ALLOW_REPAIR', true ); from your wp-config.php file for the future security. Trust me, I have been a victim of a hack due to my silly simple ignorance.

CONCLUSION

It is always necessary and a must to backup your database before processing further for optimization. This technique should be followed from time to time for the good website performance. So, therefore, Database Optimization is necessary for every dynamic website. Feel free to comment your ideas how you optimization your database website. What technique you use or which plugins you recommend. ADIOS !!