{"id":2214,"date":"2014-09-20T17:26:52","date_gmt":"2014-09-20T16:26:52","guid":{"rendered":"http:\/\/kudzia.eu\/b\/?p=2214"},"modified":"2014-10-10T07:33:00","modified_gmt":"2014-10-10T06:33:00","slug":"union-instead-of-or-in-mysql","status":"publish","type":"post","link":"https:\/\/kudzia.eu\/b\/2014\/09\/union-instead-of-or-in-mysql\/","title":{"rendered":"UNION instead of OR in MySQL"},"content":{"rendered":"<p>i have MySQL that is run on arrays of increasing size:<\/p>\n<pre class=\"brush: plain; title: ; notranslate\" title=\"\">\r\nSELECT ... FROM tableA LEFT JOIN tableB ON tableA.id=tableB.aId WHERE tableA.lastModified&gt;? OR tableB.lastModified&gt;?\r\n<\/pre>\n<p>i remembered that the query optimizer will not be able to use existing indices for lastModified columns but.. the query execution time was acceptable, so i did not bother. recently much more data was added and query above, repeated quite often, caused a lot of cpu usage on the server. so i&#8217;ve rewritten the query into union of two queries:<\/p>\n<pre class=\"brush: plain; title: ; notranslate\" title=\"\">\r\nSELECT ... FROM tableA LEFT JOIN tableB ON tableA.id=tableB.aId WHERE tableA.lastModified&gt;\r\nUNION\r\nSELECT ... FROM tableA LEFT JOIN tableB ON tableA.id=tableB.aId WHERE tableB.lastModified&gt;?\r\n<\/pre>\n<p>in this case existing indices are properly used.. finally! the result:<\/p>\n","protected":false},"excerpt":{"rendered":"<p>i have MySQL that is run on arrays of increasing size: SELECT &#8230; FROM tableA LEFT JOIN tableB ON tableA.id=tableB.aId WHERE tableA.lastModified&gt;? OR tableB.lastModified&gt;? i remembered that the query optimizer will not be able to use existing indices for lastModified columns but.. the query execution time was acceptable, so i did not bother. recently much [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17],"tags":[],"class_list":["post-2214","post","type-post","status-publish","format-standard","hentry","category-tech"],"_links":{"self":[{"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/posts\/2214","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/comments?post=2214"}],"version-history":[{"count":2,"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/posts\/2214\/revisions"}],"predecessor-version":[{"id":2339,"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/posts\/2214\/revisions\/2339"}],"wp:attachment":[{"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/media?parent=2214"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/categories?post=2214"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/tags?post=2214"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}