{"id":8,"date":"2007-11-14T11:13:22","date_gmt":"2007-11-14T09:13:22","guid":{"rendered":"http:\/\/vladimir-shapiro.com\/en\/blog\/2007\/11\/14\/mysql-query-optimization\/"},"modified":"2016-08-18T11:33:57","modified_gmt":"2016-08-18T09:33:57","slug":"mysql-query-optimization","status":"publish","type":"post","link":"https:\/\/vladimir-shapiro.com\/en\/blog\/2007\/11\/14\/mysql-query-optimization\/","title":{"rendered":"MySQL Query Optimization"},"content":{"rendered":"<p>From the first month of my employment at <a href=\"http:\/\/www.sap.com\/\">SAP<\/a> I was really impressed how the guys in support become crazy on SQL optimization. Deeply impressed on variety of tools for doing this on <a href=\"http:\/\/www.oracle.com\/database\/index.html\">Oracle<\/a> and <a href=\"http:\/\/www-306.ibm.com\/software\/data\/db2\/\">DB2<\/a> I decided to carry out the same analysis on my own projects. The first question I&#8217;ve asked myself: &#8220;Do we have any native <a href=\"http:\/\/dev.mysql.com\/\">MySQL<\/a> tools for sql optimization?&#8221;.<\/p>\n<p>Yes, we do.<\/p>\n<p><!--more--><\/p>\n<p>MySQL can record expensive SQL queries in the <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/slow-query-log.html\">slow query log<\/a>. Turning this feature on helps you to track all SQL queries which took longer than you expected.  You can define your expectations in seconds using parameter  <em>long_query_time<\/em>.<\/p>\n<p>After the logging is turned on you can analyze the log contents using <em>mysqldumpslow<\/em> command. E.g. following call<\/p>\n<blockquote><p> <strong>mysqldumpslow<\/strong> \/path\/to\/your\/mysql-slow-queries.log <strong>-t 10<\/strong><\/p><\/blockquote>\n<p>shows you top 10 performance killers. For each statement in the output you can see the number of identical calls, execution time in seconds, rows affected and the statement itself.<\/p>\n<p>I have already tested slow query logging in <a href=\"http:\/\/en.diorama.ru\/\">diorama<\/a> and found several performance killers in&#8230; <a href=\"http:\/\/www.phpbb.com\/\">phpBB<\/a>.  But that&#8217;s\u00c2\u00a0 another story ;-)<\/p>\n","protected":false},"excerpt":{"rendered":"<p>From the first month of my employment at SAP I was really impressed how the guys in support become crazy on SQL optimization. Deeply impressed on variety of tools for doing this on Oracle and DB2 I decided to carry out the same analysis on my own projects. The first question I&#8217;ve asked myself: &#8220;Do [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[6],"tags":[21],"_links":{"self":[{"href":"https:\/\/vladimir-shapiro.com\/en\/blog\/wp-json\/wp\/v2\/posts\/8"}],"collection":[{"href":"https:\/\/vladimir-shapiro.com\/en\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vladimir-shapiro.com\/en\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vladimir-shapiro.com\/en\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/vladimir-shapiro.com\/en\/blog\/wp-json\/wp\/v2\/comments?post=8"}],"version-history":[{"count":1,"href":"https:\/\/vladimir-shapiro.com\/en\/blog\/wp-json\/wp\/v2\/posts\/8\/revisions"}],"predecessor-version":[{"id":122,"href":"https:\/\/vladimir-shapiro.com\/en\/blog\/wp-json\/wp\/v2\/posts\/8\/revisions\/122"}],"wp:attachment":[{"href":"https:\/\/vladimir-shapiro.com\/en\/blog\/wp-json\/wp\/v2\/media?parent=8"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vladimir-shapiro.com\/en\/blog\/wp-json\/wp\/v2\/categories?post=8"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vladimir-shapiro.com\/en\/blog\/wp-json\/wp\/v2\/tags?post=8"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}