{"id":65,"date":"2007-07-23T01:18:24","date_gmt":"2007-07-23T05:18:24","guid":{"rendered":"http:\/\/www.virtualroadside.com\/blog\/index.php\/2007\/07\/23\/optimizing-a-really-nasty-sql-query\/"},"modified":"2007-07-23T01:18:24","modified_gmt":"2007-07-23T05:18:24","slug":"optimizing-a-really-nasty-sql-query","status":"publish","type":"post","link":"http:\/\/www.virtualroadside.com\/blog\/index.php\/2007\/07\/23\/optimizing-a-really-nasty-sql-query\/","title":{"rendered":"Optimizing a really nasty SQL query"},"content":{"rendered":"<p>Ok, so while working on Obsessive Website Statistics (<a href=\"http:\/\/obsessive.sourceforge.net\/\">OWS<\/a>), I&#8217;ve hit a situation. See, OWS tries to be semi-intelligent and combine the parameters of all the installed plugins into really giant\/nasty SQL queries that make you shudder, but tend to work.<\/p>\n<p>So right now, I&#8217;m trying to select the following (at the same time):<\/p>\n<ul>\n<li>All pages, grouped<\/li>\n<li>COUNT() of all hosts<\/li>\n<li>COUNT() of all pages that end with .html, .htm, .php, \/<\/li>\n<li>COUNT() of all pages<\/li>\n<li>SUM() of filesizes<\/li>\n<\/ul>\n<p>So of course, getting the 1st, 2nd, 4th, and 5th items is pretty easy. However, the third item is getting annoying. I tried using a subquery, but considering the table is 100,000 rows this is a particularly slow query:<\/p>\n<pre>\r\nSELECT \r\n\tvirtualroadside_com.request_str,\r\n\tCOUNT(DISTINCT virtualroadside_com.host),\r\n\tc.b,\r\n\tCOUNT(virtualroadside_com.filename),\r\n\tSUM(virtualroadside_com.bytes) \r\nFROM \r\n\t(\t\r\n\t\tSELECT \r\n\t\t\tvirtualroadside_com.request_str AS a,\r\n\t\t\tCOUNT(virtualroadside_com.id) AS b \r\n\t\tFROM \r\n\t\t\tvirtualroadside_com \r\n\t\tWHERE \r\n\t\t\t(virtualroadside_com.filename LIKE '%.html' \r\n\t\t\tOR virtualroadside_com.filename LIKE '%\/' \r\n\t\t\tOR virtualroadside_com.filename LIKE '%.htm' \r\n\t\t\tOR virtualroadside_com.filename LIKE '%.php') \r\n\t\tGROUP BY virtualroadside_com.request_str \r\n\t\tORDER BY virtualroadside_com.request_str DESC \r\n\t\tLIMIT 0,100\r\n\t) c,\r\n\tvirtualroadside_com \r\nWHERE \r\n\tvirtualroadside_com.request_str = c.a\r\nGROUP BY virtualroadside_com.request_str \r\nORDER BY virtualroadside_com.request_str DESC \r\nLIMIT 0,100;\r\n<\/pre>\n<p>So, the big question here is: Is there better ways to accomplish this sort of thing without using subqueries? This particular query takes around 40 seconds on 105,000 rows to execute on my computer (Dual PIII, 500Mhz). I&#8217;m positive theres a way to do with with a JOIN of some kind, but I can&#8217;t get any of those to work correctly either. Let me know if you have any good ideas! I&#8217;ll publish a better way to do this hopefully in the next few days once I figure it out. \ud83d\ude42<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Ok, so while working on Obsessive Website Statistics (OWS), I&#8217;ve hit a situation. See, OWS tries to be semi-intelligent and combine the parameters of all the installed plugins into really giant\/nasty SQL queries that make you shudder, but tend to work. So right now, I&#8217;m trying to select the following (at the same time): All [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"_links":{"self":[{"href":"http:\/\/www.virtualroadside.com\/blog\/index.php\/wp-json\/wp\/v2\/posts\/65"}],"collection":[{"href":"http:\/\/www.virtualroadside.com\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.virtualroadside.com\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.virtualroadside.com\/blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.virtualroadside.com\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=65"}],"version-history":[{"count":0,"href":"http:\/\/www.virtualroadside.com\/blog\/index.php\/wp-json\/wp\/v2\/posts\/65\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.virtualroadside.com\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=65"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.virtualroadside.com\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=65"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.virtualroadside.com\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=65"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}