tag:blogger.com,1999:blog-9144505959002328789.post7501598068318574457..comments2024-03-28T11:39:50.622+01:00Comments on Karlsson on databases and stuff: OR conditions considered bad... Or? And a workaround.Karlssonhttp://www.blogger.com/profile/04874338187076980133noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-9144505959002328789.post-18827589573006481972011-07-26T13:36:01.153+02:002011-07-26T13:36:01.153+02:00You might need to add a column to one of your inde...You might need to add a column to one of your indexes to get index_merge working.Daniël van Eedenhttps://www.blogger.com/profile/14757324605223498151noreply@blogger.comtag:blogger.com,1999:blog-9144505959002328789.post-39399330031229381122011-07-20T11:34:44.368+02:002011-07-20T11:34:44.368+02:00OK, I admit, I hadn't read the docs carefully ...OK, I admit, I hadn't read the docs carefully enough, there is an index merge method since 5.0. Shame on me. But I can't remember when I saw that one used last. I'll do some experiments today and post another blogpost on that, and then I'll read the docs.<br />In theory, the "one index per statement" limitation should be gone in 5.0. But as I said, I have not seen the index_merge method used often.Karlssonhttps://www.blogger.com/profile/04874338187076980133noreply@blogger.comtag:blogger.com,1999:blog-9144505959002328789.post-65249819968842298412011-07-20T01:15:40.492+02:002011-07-20T01:15:40.492+02:00Thanx Mark, I knew there was some work in this are...Thanx Mark, I knew there was some work in this area, but I just assumed it wasn't implemented yet. But apparently the optimizer still has to figure out a way to use this method in any case than the most simple one. I'll make some more experiments and see what happens!<br /><br />Cheers<br />/KarlssonKarlssonhttps://www.blogger.com/profile/04874338187076980133noreply@blogger.comtag:blogger.com,1999:blog-9144505959002328789.post-51864914712278815922011-07-19T22:30:33.406+02:002011-07-19T22:30:33.406+02:00I guess I should've posted the example query t...I guess I should've posted the example query too:<br /><br />SELECT comment_date_gmt <br />FROM wp_comments <br />WHERE comment_author_IP = '1.1.1.1' OR <br /> comment_author_email = 'example@example.com' <br />ORDER BY comment_date DESC;<br /><br />Both columns are indexed in the table, which is shown as both being selected as a key in the EXPLAINAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-9144505959002328789.post-36650013440707750142011-07-19T22:28:20.551+02:002011-07-19T22:28:20.551+02:00"The Index Merge method is used to retrieve r..."The Index Merge method is used to retrieve rows with several range scans and to merge their results into one. The merge can produce unions, intersections, or unions-of-intersections of its underlying scans."<br /><a href="http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html</a><br /><br />I see this most commonly in WordPress (I took a few rows out for brevity):<br /><br />select_type: SIMPLE<br />table: wp_comments <br />type: index_merge<br />key: comment_author_IP,comment_author_email <br />Extra: Using union(comment_author_IP,comment_author_email); Using where; Using filesort<br /><br />It's not always the most reliable, but it does end up using two indexes and merging the result sets from both indexes.Anonymousnoreply@blogger.com