MySQL: How to Find Slow Queries & Optimize Them

Let’s see if you have “slow_query_log” enabled — issue the following command:

show global variables like ‘%slow%’;

If “slow_query_log” is set to 0, issue this command:

set global slow_query_log = 1;

If you don’t see “slow_query_log” or it’s empty, issue this command (changing the directory to your preference):

set global slow_query_log = “/var/lib/mysql/slow-query.log”;

In order to evaluate all data, let’s set our “long_query_time” to 0:

select @@global.long_query_time;

set global long_query_time = 0;

And finally, we’re allow “pt-query-digest” to provide us a report from which we can make informed decisions:

pt-query-digest /var/lib/mysql/slow-query.log > output.txt

See which engine is set for each database-table (example): show table status from <database-name-here>;

Change database engine to “InnoDB” (example): alter table wp_termmeta ENGINE=InnoDB;

Source: http://www.speedemy.com/