{"id":1141,"date":"2011-07-11T13:26:45","date_gmt":"2011-07-11T20:26:45","guid":{"rendered":"http:\/\/162.243.68.163\/blog\/?p=1141"},"modified":"2011-07-14T09:30:05","modified_gmt":"2011-07-14T16:30:05","slug":"wordpress-quotes-collection-patch-update","status":"publish","type":"post","link":"https:\/\/tekhedd.com\/?p=1141","title":{"rendered":"WordPress Quotes Collection patch update"},"content":{"rendered":"<p>This is an update to the WordPress Quotes Collection plugin, which uses &#8220;ORDER BY RAND().&#8221; This is a Bad Idea, but <a href=\"http:\/\/jan.kneschke.de\/projects\/mysql\/order-by-rand\/\">there is a better way<\/a> that isn&#8217;t too ugly. This is a partial fix. A complete fix involves a helper table and a database trigger, which I am just too lazy to maintain as a branch of an uncommented PHP script.<\/p>\n<p><em>The problem:<\/em> If you have a very large number of quotes, and use the &#8220;random&#8221; feature, your quotes collection plugin is eating randomness very rapidly for each page load. This is bad.<\/p>\n<p><em>The solution:<\/em> This patch replaces the quotescollection_get_quote()\u00c2\u00a0 function with a random function that retrieves exactly one random number per fetch. It has only been tested in my configuration, with Quotes Collection 1.5.1:<\/p>\n<pre>function quotescollection_get_quote($condition = '', $random = 1, $current = 0)\r\n{\r\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0global $wpdb;\r\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0$sql = \"SELECT quote_id, quote, author, source\r\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0FROM \" . $wpdb-&gt;prefix . \"quotescollection\";\r\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0if(!$random) {\r\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0if ($condition)\r\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0$sql .= $condition;\r\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0if($current)\r\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0$sql .= \" AND quote_id &lt; {$current}\";\r\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0$sql .= \" ORDER BY quote_id DESC\";\r\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0}\r\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0else {\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \/\/ Select random id. Conserves randomness, but will be less random if you\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \/\/ delete quotes.\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \/\/ See: http:\/\/jan.kneschke.de\/projects\/mysql\/order-by-rand\/\r\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0$sql .= \" JOIN (SELECT CEIL(RAND() * (SELECT MAX(quote_id)\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 FROM \" . $wpdb-&gt;prefix . \"quotescollection )) AS id)\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 AS myrand\";\r\n\r\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0if ($condition) {\r\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0$sql .= $condition;\r\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0$sql .= \" AND \";\r\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0}\r\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0else {\r\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0$sql .= \" WHERE \";\r\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0}\r\n\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 $sql .= \"quote_id &gt;= myrand.id\";\r\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\/\/ This random method requires LIMIT 1, which is appended below already\r\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0}\r\n\r\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0$sql .= \" LIMIT 1\";\r\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0$random_quote = $wpdb-&gt;get_row($sql, ARRAY_A);\r\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0if ( empty($random_quote) ) {\r\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0if(!$random &amp;&amp; $current)\r\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0return quotescollection_get_quote($condition, 0, 0);\r\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0else\r\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0return 0;\r\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0}\r\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0else\r\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0return $random_quote;\r\n}<\/pre>\n<p>My previous random fix used count(*) which I have now discovered can be just as slow, for entirely different reasons. So I won&#8217;t be doing that again. :)<\/p>\n<p>Peace, love, and high quality randomness to you all.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This is an update to the WordPress Quotes Collection plugin, which uses &#8220;ORDER BY RAND().&#8221; This is a Bad Idea, but there is a better way that isn&#8217;t too ugly. This is a partial fix. A complete fix involves a helper table and a database trigger, which I am just too lazy to maintain as [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[239,240],"class_list":["post-1141","post","type-post","status-publish","format-standard","hentry","category-other","tag-quotes-collection","tag-sql"],"_links":{"self":[{"href":"https:\/\/tekhedd.com\/index.php?rest_route=\/wp\/v2\/posts\/1141","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tekhedd.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tekhedd.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tekhedd.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/tekhedd.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1141"}],"version-history":[{"count":5,"href":"https:\/\/tekhedd.com\/index.php?rest_route=\/wp\/v2\/posts\/1141\/revisions"}],"predecessor-version":[{"id":1143,"href":"https:\/\/tekhedd.com\/index.php?rest_route=\/wp\/v2\/posts\/1141\/revisions\/1143"}],"wp:attachment":[{"href":"https:\/\/tekhedd.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1141"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tekhedd.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1141"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tekhedd.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1141"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}