MySQL Database optimization

Different versions of MySQL optimizer have a certain difference
+ View Database version
SELECT @ @version;

+ View the database being used
Select Database ();

+ See if MySQL turns on slow query log
Show variables like ' Slow_query_log ';

+ turn on MySQL slow query log
Set global slow_query_log=on;

+ Set the file location where the MySQL slow query log is stored
Set global slow_query_log_file= '/home/mysql/sql_log/mysql-slow.log ';

+ Set non-indexed records to slow query log
Set global log_queries_not_using_indexes = on;

+ Set the time limit for SQL query logging to slow query log files, in seconds, usually 0.01s
Set long_query_time=1

+ Slow query Log analysis tool
Pt-query-digest

Pt-query-digest/var/lib/mysql/bogon-slow.log | More

+ View SQL Execution plan
Explain select * from store;
Using Filesort: Optimization is required. MySQL requires additional steps to find out how to sort the rows that are returned. It sorts all rows based on the connection type and the row pointers for all rows that store the sort key values and matching criteria.
Using Temporary: optimization is required. MySQL needs to create a temporary table to store the results, which typically occurs when an order by rather than group by is performed on a different set of columns.

MySQL Database optimization