Optimize your MySQL Database Performance

MySQL is a free database management system widely used in PHP. When creating tables in MySQL there are many types of Storage Engine to choose from. MyIsam & Innodb & Memory are three popular types. Choosing a store tool is extremely important before you design the database, helping to improve query performance. If the store engine changes later, it will take a long time if the data on the table is large. Let’s talk about it.

I. MySQL storage engines

1. MyISAM

This Storage Engine allows full-text search indexes. Therefore, this Storage Engine gives speed read and search quickly.

The downside of MyISAM is that it uses table-level locking mechanism, so when adding, editing or deleting a certain record in the same table, that table will be locked, not allowed until the previous operation done.

2. InnoDB

Currently InnoDB is set by default when creating DB, it supports Foreign key foreign key and since version 5.6, InnoDB also supports Full-text search indexes.

The best insert / update / delete speed because it works according to Row Level Locking mechanism, so when adding, editing or deleting a table, only the records are manipulated, and other activities on this table are not affect.

3. Memory

Memory engine is the most suitable engine for fast data access, as everything is stored in RAM. And so when restarting MySQL or Server, all data will be deleted.

So we should choose the store engine to suit the purpose of our project:

– MyISAM: For an application with high reading frequency such as news sites, blogs, you should use MyISAM

– InnoDB: For applications with high insert and update frequency such as: Forums, social networks … you should use InnoDB.

– Memory: For tables containing data that has not been stored for too long, and for user sessions, you should use Memory

II. Optimize queries to MySQL Database

1. MySQL insert multiple rows

You cannot run the INSERT statement in a loop, which actually affects the speed of the connection to the database in case your loop has many values.

Let’s use an INSERT statement in this case, for example:

INSERT INTO USERS (full_name, age, gender) VALUES (‘John’, 20, 1), (‘Sara’, 30, 0), (‘Mary’, 25, 0);

2. MySQL multiple update in one statement

Similar to the multiple INSERT statement, the UPDATE should be optimized when you want to perform the same task.

Please use ON DUPLICATE KEY UPDATE to apply, for example:

INSERT INTO USERS (id, full_name, age, gender) VALUES (1, ‘John’, 21, 0), (2, ‘Sara’, 31, 0), (3, ‘Mary’, 26, 1) ON DUPLICATE KEY UPDATE full_name=values(full_name), age=values(age), gender=values(gender);

3. Do not use LIMIT OFFSET with tables with large data

For example:

SELECT * FROM TABLE LIMIT 10 OFFSET 80000 (starting at 80001)
OR
SELECT * FROM TABLE LIMIT 80001, 10

Please replace it with:

SELECT * FROM TABLE WHERE id > 80000 LIMIT 10

4. Indexing in MySQL

– How to type index compound vs index:

+ If you frequently query for 2 fields, use composite index (a, b) rather than index (a), index (b). index (a, b) is faster
+ If the query is only 1 field, then just index a without index b

– Each individual field does not need UNIQUE, but when combined as the home address, you can type INDEX as follows:

CREATE UNIQUE INDEX index_street_number_city ON addresses (street, house_number, city);

* Note: You should only index to support the frequently read queries, the frequently used fields. The more indexes, the more it affects inserting, deleting and updating.

5. Use the correct field type for the data

There are many different types of fields available through MySQL, but it’s best to use those that are familiar with strings and numbers.

For example, if you only store the numbers 1, 2, and 3 in a field, better use TINYINT than INTERGER. The storage space on the server will be more saved.

III. Some useful MySQL query commands

1. The most basic thing in a SELECT query

SELECT query is not to use SELECT * if it’s not absolutely necessary.

2. SQL CONCAT Function:

CONCAT function is used to concatenate two or more strings to a single string.

SELECT CustomerID, CONCAT(FirstName, LastName) AS FullName, CONCAT(PostalCode, Town, District, City) AS Address FROM Customers;

Result:

3. MySQL JOIN Select last record:

SELECT a.id, a.user_id, a.user_name
FROM Customers as a
LEFT JOIN ViewHistories as b ON b.user_id = a.user_id
WHERE b.id = (SELECT MAX(id) FROM ViewHistories WHERE user_id = a.user_id)
ORDER BY a.name

If the user_name includes Japanese(カタカナ), it is possible to ORDER BY condition: ORDER BY user_name collate utf8_unicode_ci

4. Insert record if not exists in table [duplicate]

$db = DB::getInstance();
$user_id = 1001;
$name = ‘John’;
$age = ’26’;
$birthday = ‘1999-01-01’;

$sql = “INSERT INTO users (user_id, name, age, birthday)
SELECT * FROM (SELECT ?, ?, ?, ?) AS tmp
WHERE NOT EXISTS (
SELECT user_id FROM users WHERE user_id = ?
)”;

$run = $db->prepare($sql);
if ($run->execute([$user_id, $name, $age, $birthday, $user_id])) {
// …
// ex: $lastInsertId = $db->lastInsertId();
}
if (!empty($lastInsertId)) {
return ‘This data already exists !’;
}

5. How to delete from multiple tables in one MySQL command?

Use a JOIN in the DELETE statement.

Example: Delete record two table (logs, log_detail) with $id = 1 in logs table

$sql = “DELETE a, b
FROM logs as a
JOIN log_details as b ON b.log_id = a.id
WHERE a.id = $id “;

Thank you for reading the article.