How to optimize a MySQL database

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 "character". 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. If you have 1 million 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.

There is another query that you can use to increase the loading speed of your database:

OPTIMIZE TABLE sample

 

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.

  • 46 کاربر این را مفید یافتند
آیا این پاسخ به شما کمک کرد؟

مقالات مربوطه

What type of databases do you support? PGSQL and MySQL?

All our servers support MySQL and PostgreSQL databases. Also with any of our hosting packages...

How to repair a MySQL database?

In this article we will show you two ways to repair a MySQL database.  1) Through...

How to optimize a MySQL database using phpMyAdmin?

It is always a good idea to keep your databases' tables optimized. Fortunately, making this...

How to change the database engine of a MySQL database table?

In this article we will show you how to change the database engine of a MySQL table. Let's...

I get an error 'ACCESS DENIED CREATE DATABASE db_name' when I try to import a database

In order to solve this issue follow these steps: Open the MySQL dump with a text editor on...