SQL and index optimization turn on slow query log to see if slow query log is open
Mysql> Show variables like ' Slow_query_log ';
+----------------+-------+
| variable_name | Value |
+----------------+-------+
| Slow_query_log | OFF |
+----------------+-------+
1 row in Set (0.00 sec)
mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.05 sec)
Slow query log file storage location
Mysql> Show variables like '%slow_query_log_file% ';
+---------------------+----------------------------------------------+
| variable_name | Value |
+---------------------+----------------------------------------------+
| Slow_query_log_file | C:\xampp\mysql\data\80CEAE742547827-slow.log |
+---------------------+----------------------------------------------+
2 rows in Set (0.00 sec)
Whether to log SQL that does not use an index
Mysql> Show variables like '%log_queries_not_using% ';
+-------------------------------+-------+
| variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF |
+-------------------------------+-------+
1 row in Set (0.00 sec)
mysql> set global log_queries_not_using_indexes=on;
Query OK, 0 rows Affected (0.00 sec)
SQL that executes longer than n seconds is logged
Mysql> Show variables like '%long_query% ';
+-----------------+-----------+
| variable_name | Value |
+-----------------+-----------+
| Long_query_time | 10.000000 |
+-----------------+-----------+
1 row in Set (0.00 sec)
mysql> set global long_query_time=1;
Query OK, 0 rows Affected (0.00 sec)
GROUP BY Statement optimization
Before optimization
Select Count (*) from Sakila.film_actor INNER JOIN sakila.actor USING (actor_id) GROUP by film_actor.actor_id
After optimization using the join subquery, the actor table does not use file sorting and staging tables
After optimization
Select Actor.first_name, Actor.last_name, c.cnt from Sakila.actor INNER JOIN ( Selectcount(*as from theGROUPby as C using (actor_id)
Although 200 rows of records are still scanned after optimization, the actor does not use file sorting and temporary tables
Data structure Optimization system configuration Optimization server hardware optimization
MySQL performance optimization