My Workday

Profiling Queries with SHOW STATUS
mysql> flush status;
mysql> select SQL_NO_CACHE count(*) from table;

– Check query plan now:

mysql> show status like 'Select%';

– Check engine operations:

mysql> show status like 'Handler%';

– Check if there was any ordering:

mysql> show status like 'Sort%';

– Check how many temporary tables have been created:

mysql> show status like 'Created%';


Profiling Queries with SHOW PROFILE

mysql> set profile=1;
select count(*) from table;
mysql> show profiles\G
*************************** 1. row ***************************
Query_ID: 1
Duration: 9.28089300
Query: select count(*) from table
1 row in set (0.00 sec)
mysql> show profile;
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| starting | 0.165418 |
| Opening tables | 0.000024 |
| System lock | 0.000004 |
| Table lock | 0.000008 |
| init | 0.000014 |
| optimizing | 0.000006 |
| statistics | 0.000013 |
| preparing | 0.000012 |
| executing | 0.000006 |
| Sending data | 9.115348 |
| end | 0.000015 |
| end | 0.000004 |
| query end | 0.000003 |
| freeing items | 0.000008 |
| closing tables | 0.000004 |
| logging slow query | 0.000002 |
| cleaning up | 0.000004 |
+--------------------+----------+

mysql> show profile cpu;
+——————–+———-+———-+————+
| Status | Duration | CPU_user | CPU_system |
+——————–+———-+———-+————+
| starting | 0.000063 | 0.000000 | 0.000000 |
| query end | 0.000004 | 0.000000 | 0.000000 |
| freeing items | 0.000005 | 0.000000 | 0.000000 |
| logging slow query | 0.000003 | 0.000000 | 0.000000 |
| cleaning up | 0.000003 | 0.000000 | 0.000000 |
+——————–+———-+———-+————+