Profiling MySQL/MariaDB queries

Afbeelding verwijderd.If you ever face an optimization issue in MySQL or MariaDB and want to know how to measure/benchmark the differences in execution between two queries, you should definitely know about the set profiling=1; command. Here is how it works:
mysql> set profiling=1; Query OK, 0 rows affected (0.00 sec) mysql> SELECT count(login_user_id)  FROM stats.track_e_online WHERE DATE_ADD(login_date, INTERVAL 60 MINUTE) >= '2012-06-11 11:56:20'; +----------------------+ | count(login_user_id) | +----------------------+ |                   65 | +----------------------+ 1 row in set (0.00 sec) mysql> show profile; +--------------------------------+----------+ | Status                         | Duration | +--------------------------------+----------+ | starting                       | 0.000022 | | checking query cache for query | 0.000055 | | checking permissions           | 0.000010 | | Opening tables                 | 0.000012 | | System lock                    | 0.000005 | | Table lock                     | 0.000023 | | init                           | 0.000027 | | optimizing                     | 0.000010 | | statistics                     | 0.000008 | | preparing                      | 0.000009 | | executing                      | 0.000004 | | Sending data                   | 0.001994 | | end                            | 0.000006 | | query end                      | 0.000004 | | freeing items                  | 0.000014 | | storing result in query cache  | 0.000227 | | logging slow query             | 0.000004 | | logging slow query             | 0.000028 | | cleaning up                    | 0.000003 | +--------------------------------+----------+ 19 rows in set (0.01 sec) mysql> SELECT count(login_user_id)  FROM stats.track_e_online WHERE '2012-06-11 11:56:20'  <= DATE_ADD(login_date, INTERVAL 60 MINUTE); +----------------------+ | count(login_user_id) | +----------------------+ |                   65 | +----------------------+ 1 row in set (0.00 sec) mysql> show profile; +--------------------------------+----------+ | Status                         | Duration | +--------------------------------+----------+ | starting                       | 0.000032 | | checking query cache for query | 0.000083 | | checking permissions           | 0.000014 | | Opening tables                 | 0.000011 | | System lock                    | 0.000005 | | Table lock                     | 0.000031 | | init                           | 0.000026 | | optimizing                     | 0.000009 | | statistics                     | 0.000008 | | preparing                      | 0.000009 | | executing                      | 0.000005 | | Sending data                   | 0.001989 | | end                            | 0.000007 | | query end                      | 0.000003 | | freeing items                  | 0.000015 | | storing result in query cache  | 0.000340 | | logging slow query             | 0.000005 | | logging slow query             | 0.000030 | | cleaning up                    | 0.000003 | +--------------------------------+----------+ 19 rows in set (0.00 sec) mysql> set profiling=0; Query OK, 0 rows affected (0.00 sec)
Well, granted, there isn't much of a difference between those two examples. I believe that's because these queries have been stored in the query cache already, but you get the idea... Note that profiling is a session variable, which means it only acts in your session of the MySQL/MariaDB client. You can find more information about profiling in the MySQL documentation pages. Of course, this also works in the great MariaDB server.