Get extended EXPLAIN profiling data in MySQL

As always with a slow query, finding the execution plan with EXPLAIN is the 1st step to understand where time is spent.

For extended profiling information, SHOW PROFILE can be used: 

mysql> set @@profiling = 1;
mysql> SELECT ....;
mysql> SHOW PROFILE;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| starting           | 0.000783 |
| Opening tables     | 0.000192 |
| System lock        | 0.000005 |
| Table lock         | 0.000010 |
| init               | 0.000369 |
| optimizing         | 0.000091 |
| statistics         | 3.459529 |
| preparing          | 0.000213 |
| executing          | 0.000005 |
| Sending data       | 0.004597 |
| end                | 0.000008 |
| query end          | 0.000005 |
| freeing items      | 0.000066 |
| logging slow query | 0.000004 |
| cleaning up        | 0.000006 |
+--------------------+----------+

Source: https://www.percona.com/blog/2014/03/06/many-table-joins-mysql-5-6/