{"id":31,"date":"2007-08-03T14:01:21","date_gmt":"2007-08-03T18:01:21","guid":{"rendered":"http:\/\/blogs.n1zyy.com\/andrew\/2007\/08\/03\/mysql-query-profiling\/"},"modified":"2007-08-03T14:01:21","modified_gmt":"2007-08-03T18:01:21","slug":"mysql-query-profiling","status":"publish","type":"post","link":"https:\/\/blogs.n1zyy.com\/andrew\/2007\/08\/03\/mysql-query-profiling\/","title":{"rendered":"MySQL Query Profiling"},"content":{"rendered":"<p>Apparently MySQL quietly (logical fallacy: I assume it was quiet because I didn&#8217;t hear anything about it) slipped a <a href=\"http:\/\/dev.mysql.com\/tech-resources\/articles\/using-new-query-profiler.html\">query profiler<\/a> patch in MySQL 5.0 (as early as 5.0.37) that will give you time statistics about each step of a query&#8217;s lifetime. From the aforementioned:<\/p>\n<blockquote>\n<pre>mysql&gt; show profile for query 1;\n+--------------------+------------+\n| Status             | Duration   |\n+--------------------+------------+\n| (initialization)   | 0.00006300 |\n| Opening tables     | 0.00001400 |\n| System lock        | 0.00000600 |\n| Table lock         | 0.00001000 |\n| init               | 0.00002200 |\n| optimizing         | 0.00001100 |\n| statistics         | 0.00009300 |\n| preparing          | 0.00001700 |\n| executing          | 0.00000700 |\n| Sending data       | 0.00016800 |\n| end                | 0.00000700 |\n| query end          | 0.00000500 |\n| freeing items      | 0.00001200 |\n| closing tables     | 0.00000800 |\n| logging slow query | 0.00000400 |\n+--------------------+------------+\n15 rows in set (0.00 sec)<\/pre>\n<\/blockquote>\n<p>This looks immensely useful! And while the <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/show-profiles.html\">MySQL documentation<\/a> is mute on the matter, according to comments on the <a href=\"http:\/\/jcole.us\/blog\/archives\/2007\/03\/10\/now-available-profiling-in-mysql\/\">blog of the original author<\/a>, the times reported aren&#8217;t specific to a thread. In other words, you&#8217;ll only get reliable numbers when there&#8217;s only one thread running.<\/p>\n<p>Note: this seems to have been left out of the version of MySQL available in Gentoo portage. We&#8217;re not sure why, but we have some good minds (i.e., not mine) trying to figure out where it went. Consequently, I haven&#8217;t actually used it yet.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Apparently MySQL quietly (logical fallacy: I assume it was quiet because I didn&#8217;t hear anything about it) slipped a query profiler patch in MySQL 5.0 (as early as 5.0.37) that will give you time statistics about each step of a query&#8217;s lifetime. From the aforementioned: mysql&gt; show profile for query 1; +&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211;+&#8212;&#8212;&#8212;&#8212;+ | Status | [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17],"tags":[],"class_list":["post-31","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/blogs.n1zyy.com\/andrew\/wp-json\/wp\/v2\/posts\/31","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.n1zyy.com\/andrew\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blogs.n1zyy.com\/andrew\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.n1zyy.com\/andrew\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.n1zyy.com\/andrew\/wp-json\/wp\/v2\/comments?post=31"}],"version-history":[{"count":0,"href":"https:\/\/blogs.n1zyy.com\/andrew\/wp-json\/wp\/v2\/posts\/31\/revisions"}],"wp:attachment":[{"href":"https:\/\/blogs.n1zyy.com\/andrew\/wp-json\/wp\/v2\/media?parent=31"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blogs.n1zyy.com\/andrew\/wp-json\/wp\/v2\/categories?post=31"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blogs.n1zyy.com\/andrew\/wp-json\/wp\/v2\/tags?post=31"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}