Releem SQL Query Optimization

Metrics collection
We don't collect your data, we need the following information: schema information, queries, and plan of queries execution.

To collect information from your MySQL server for query optimization, please follow these instructions.

1. Create a directory to store the exported data, set permissions and export schema

mkdir -p /var/lib/mysql-files/
chown -R mysql. /var/lib/mysql-files/
mysqldump --no-data -A > /var/lib/mysql-files/dump_all.sql
2. Execute the following queries with the root MySQL user to save indexes, performance schema and column information into CSV files in the created directory

SELECT * 
FROM information_schema.STATISTICS 
WHERE TABLE_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema', 'sys')
INTO OUTFILE '/var/lib/mysql-files/indexes.csv' 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

SELECT * 
FROM performance_schema.events_statements_summary_by_digest 
ORDER BY SUM_TIMER_WAIT DESC 
INTO OUTFILE '/var/lib/mysql-files/digest.csv' 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

SELECT * 
FROM performance_schema.file_summary_by_instance 
INTO OUTFILE '/var/lib/mysql-files/IO_Summary_Tables_DAY.csv' 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

SELECT * 
FROM information_schema.columns
INTO OUTFILE '/var/lib/mysql-files/columns.csv' 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

SELECT *  
FROM information_schema.tables
INTO OUTFILE '/var/lib/mysql-files/tables.csv' 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
3. To provide us with the slow query log, find its location by executing the following SQL query

SHOW VARIABLES WHERE Variable_name = 'slow_query_log_file';
4. Please send us /var/lib/mysql-files/ folder and slow query log file using the following form.
Collected data