MySQL (Optimisation)

MySQL.md

MySQL (Optimisation)

⚡ Setup

Commands

SHOW VARIABLES LIKE "[name]";

SET GLOBAL [name] = [value];

ALTER TABLE [database].[table] ENGINE = InnoDB;

ALTER DATABASE [database] CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

mysqldump -u user -p database > dump.sql

⚡ Calculation

(1 CPU cores, 2 GB RAM)

SHOW PROCESSLIST;

⚡ Configuration

@ /etc/mysql/my.cnf

cp /etc/mysql/my.cnf /etc/mysql/my.cnf.bkp
wget https://gist.githubusercontent.com/Jakiboy/65be29993b2304b1f5f8c59eaafe0304/raw/my.cnf -O my.cnf
mv my.cnf /etc/mysql/my.cnf
chown mysql:mysql /etc/mysql/my.cnf

Slow query

touch /var/log/mysql-slow.log
chown mysql:mysql /var/log/mysql-slow.log
mysqldumpslow -a /var/log/mysql-slow.log

slow_query_log = 1
slow-query_log_file = /var/log/mysql-slow.log
long_query_time = 2

Check

wget http://mysqltuner.pl/ -O mysqltuner.pl
chmod a+x mysqltuner.pl

perl mysqltuner.pl --host 127.0.0.1

Restart

service mysql restart
systemctl daemon-reload

⚡ SQL (Advanced)

Count

SELECT [column], COUNT([column])
FROM `[table]`
GROUP BY [column]
HAVING COUNT([column]) > 1;

Column

ALTER TABLE `[table]`
ADD COLUMN `[column1]` VARCHAR(60) AFTER `column2`;

Copy

INSERT INTO `[table1]`
SELECT * FROM `[table2]`;

INSERT INTO `[table1]`(1,2,3)
SELECT 1,2,3 FROM `[table2]`;

Delete (Duplicated)

DELETE t1 FROM `[table]` t1
INNER JOIN `[table]` t2 
WHERE t1.[id] > t2.[id] AND t1.[name] LIKE t2.[name];

Replace value

UPDATE `[table]`
SET `[column]` = REPLACE(`[column]`, "VALUE1", "VALUE2");

WordPress User

[PREFIX]_users

INSERT INTO `DATABASE`.`[PREFIX]_users` 
(
        `ID`, 
        `user_login`, 
        `user_pass`, 
        `user_nicename`, 
        `user_email`, 
        `user_url`, 
        `user_registered`, 
        `user_activation_key`, 
        `user_status`, 
        `display_name`
) 
VALUES (
        '[id]', 
        '[login]', 
        MD5("[password]"), 
        '[name]', 
        '[email]', 
        '[url]', 
        '[date]', 
        '', 
        '0', 
        '[name]'
);

[PREFIX]_usermeta

INSERT INTO `DATABASE`.`[PREFIX]_usermeta` 
(`umeta_id`, `user_id`, `meta_key`, `meta_value`) 
VALUES (NULL, '[id]', '[PREFIX]_capabilities', 'a:1:[s:1]:"administrator";s:1:"1";}');

INSERT INTO `DATABASE`.`[PREFIX]_usermeta` 
(`umeta_id`, `user_id`, `meta_key`, `meta_value`) 
VALUES (NULL, '[id]', '[PREFIX]_user_level', '10');

---

Authors:

• Jihad Sinnaour - Jakiboy (Initial work)

⭐ Support:

Please give it a Star if you like the project.