Steps to optimize Mysql database through PhpMyAdmin

A database can hold immense amount of data, the bigger is larger the data .The information can also be in the form text, menus, posts etc. Better sites always have bigger database. Along with the important details, these databases are piled with scams items, unwanted contents and temporary files. It can result in poor performance of your website; it can reduce your website’s speed. The MySQL requests to a pileup database stuck in a queue. A proper optimization once in while is absolutely necessary to have an efficient database.

Overview of optimisation:

There are various factors which run the database under the control of tables, queries and configurations. The result of software can be determined from the end of hardware process such as approval from the I/O and CPU operators. Once if you are ready to learn about the software ethics then you would be expert in rules and regulations from the aspect of higher stages. If at all, an expert has a measurement to identify the CPU and I/O internally.

Database structure to optimise at different levels:

Identify the structure of tables and columns at each stage. Some application will have updates at regular instances; they would have more tables and fewer columns. If they want to analyse the data then the tables will be few and columns will be more.

The queries should be properly malfunctioning and have clear understood ability.

The engine which stores the data should be analysed with its features and positive abilities. To perform the scalability you should use InnoDB and MyISAM. It’s also better if you use appropriate storage engine for each table.

The InnoDB and MyISAM should have proper row format which stabilizes the less space in disk and read & write efficiency.

The memory areas should be configured properly to avoid overload paging and memory physically.

Configure the MyISAM key, InnoDB and MySQL will be processed at main instances.

Above are the basic methods to optimize your database. There are websites based on CMS like wordpress, zoomla, open cart etc. Here we use wordpress based website, and the name of the database tests_wp255.

Making changes can remove all your existing data, so it is a custom to backup your data just to be on your safer side.

You can back it up from cpanel FILES tab >> Backup menu. Just click on the database to "Download a MySQL Database Backup" menu and start the download .

Next is to click on the database assigned for downloading.

Now Navigate databases tab in cpanel >> phpMyAdmin menu. Select the icon to get into PhpMyAdmin.You should click on the database which is located on the left side.

On clicking it we can find that the tables are listed there. Recent tables are displayed first.

Selecting of the needed table from the listed tables, then click on the option optimize table from the drop down dialog box is the next process. We can select multiple tables by check marking using check all. Selecting multiple tables at once and optimizing it can be done. Once the selected tables are optimized the other tables are updated. There are other ways to optimize your database.

You can remove the unnecessary data. It can be done using the SQL command line. The query is,

Code:

DELETE FROM $table where <clause>;

Where $table defines the name of the table that should be modified and <clause> defines the changes that can be performed. for removing all the post with a particular keyword , the following code is used,

Code:

DELETE FROM 'table'
WHERE 'column' like '%keyword%'.

To remove the comments from a certain user.

The command to use is:

Code:

DELETE FROM 'wp9x_comments'
WHERE 'comment_author' = 'test_user'

To remove post status:

Code:

Or DELETE FROM 'wp9x_posts'
WHERE 'post_status' = 'closed.

The same process is for all other tables too.

Steps to optimize Mysql database through PhpMyAdmin

More resources from bhawanisingh

Share this resource