Optimizing MySQL or MariaDB Databases

Optimizing MySQL or MariaDB Databases

One of the most important prerequisites for achieving optimal MySQL database performance is indexing. Indexing is an internal MySQL feature that allows faster gathering of data.

Let’s take an example table called “sample” with only two rows – “number” and “employee”. If you run a simple query such as:

SELECT * FROM sample WHERE number = 4;

MySQL will check all records and will return only the one that has its number value set to 4.

But if you have several thousand entries for example, this will be a slow query. In this case we have a unique field – “number”. Therefore, we can create an index for it. Indexing will create an internal register that is saved in by the MySQL service. It can be done with the following query:

ALTER TABLE sample ADD INDEX (number);

Once this index is set, next time you want to get the information for employee number 4, the service will go directly to it using the index and will return the information much faster.

This is just a very basic example. For bigger databases, the difference in the loading time can be significant. Indexing your database can drastically decrease the loading time of your web applications.

You can use the following command to optimize/repair a database through SSH:

$ mysqlcheck -orp -u user_name database_name

where:
-o – stands for optimize
-r – stands for repair
-p – when this option is used with the command you will be prompted to enter the password of the database username user_name
-u – the username assigned to the database; in this case you can use your cPanel username with its corresponding password

There are other methods which can be used for MySQL optimization such as using of persistent connections, query caching, etc. However, they may require some fine tuning from the Apache and MySQL configuration files.

After the optimization, the MySQL service will take much less time to search in your database which will result in better performance of your scripts. This can have considerable performance benefits in WordPress Hosting and other applications utilizing relational databases.


at 

Updated: at 

Been read 5166 times

Similar Articles

Command Line Tools to Manage Linux Servers


The ssh command allows you to log in to a remote machine as well as execute commands on a remote machine. When performed as the root user, this method of connecting to the server provides the greatest level of access and control over the server. The...

What People wrote...