tag:blogger.com,1999:blog-7171477457129187873.post8905058823337656817..comments2022-03-24T11:42:30.620+01:00Comments on Jørgen's point of view: Index merge annoyances fixed in MySQL 5.6Jørgen Lølandhttp://www.blogger.com/profile/07423633132356058535noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-7171477457129187873.post-54055262151929443032012-10-04T13:00:31.878+02:002012-10-04T13:00:31.878+02:00Igor,
I agree that this should be improved as wel...Igor,<br /><br />I agree that this should be improved as well so I just made a fix. I'll brush the patch up as soon as I get the time for it :-)<br />Jørgen Lølandhttps://www.blogger.com/profile/07423633132356058535noreply@blogger.comtag:blogger.com,1999:blog-7171477457129187873.post-87349812640235247522012-10-04T06:36:47.074+02:002012-10-04T06:36:47.074+02:00Jørgen.
Let's take the query from
http://bugs...Jørgen.<br /><br />Let's take the query from<br />http://bugs.mysql.com/bug.php?id=17673<br /><br />We have for mysql-5.6.7<br /><br />mysql> explain select * from tmerge2 where ((c2 = 1 and c3 = 1) or (c4 =2 and c5 = 1)) and cp = 1\G<br />*************************** 1. row ***************************<br /> id: 1<br /> select_type: SIMPLE<br /> table: tmerge2<br /> type: index_merge<br />possible_keys: k1,k2<br /> key: k1,k2<br /> key_len: 12,12<br /> ref: NULL<br /> rows: 2<br /> Extra: Using sort_union(k1,k2); Using where<br /><br />and <br /><br />mysql> explain select * from tmerge2 where (c2 = 1 and c3 = 1 and cp=1) or (c4=2 and c5=1 and cp=1)\G<br />*************************** 1. row ***************************<br /> id: 1<br /> select_type: SIMPLE<br /> table: tmerge2<br /> type: index_merge<br />possible_keys: k1,k2<br /> key: k1,k2<br /> key_len: 14,14<br /> ref: NULL<br /> rows: 2<br /> Extra: Using sort_union(k1,k2); Using where<br /><br />See that in the second case the index merge uses 3 components for each merged index, while in the first case<br />only two major components<br />(key_len: 14,14 and key_len: 12,12 respectively)<br />igorhttps://www.blogger.com/profile/06961843646062810762noreply@blogger.comtag:blogger.com,1999:blog-7171477457129187873.post-77446041034947412452012-10-03T09:23:49.384+02:002012-10-03T09:23:49.384+02:00Igor,
Thanks for the example. As I wrote above I ...Igor,<br /><br />Thanks for the example. As I wrote above I am aware of and able to write examples of such complex conditions myself. What I wanted to know was if there are users with real problems that would need even more enhancements in this area. If not, I think you agree with me that a developer's time is best spent on enhancements that users care about.<br /><br />I figured the MySQL community would be eager to provide this feedback, but it is better to do so through bug reports.Jørgen Lølandhttps://www.blogger.com/profile/07423633132356058535noreply@blogger.comtag:blogger.com,1999:blog-7171477457129187873.post-77338578395882001952012-10-02T19:45:25.214+02:002012-10-02T19:45:25.214+02:00Here's an example:
for the table t0 from mysql...Here's an example:<br />for the table t0 from mysql-test/include/index_merge1.inc:<br /><br />mysql> explain select * from t0 where (key1 < 2 or key2 < 4) and (key3 > 10) or (key1 < 1 or key2 < 3) and (key3 < 20);<br />+----+-------------+-------+------+---------------+------+---------+------+------+-------------+<br />| id | select_type | table | type | possible_keys | key | key_len | ref<br />| rows | Extra |<br />+----+-------------+-------+------+---------------+------+---------+------+------+-------------+<br />| 1 | SIMPLE | t0 | ALL | i1,i2,i3 | NULL | NULL | NULL | 1024 | Using<br />where |<br />+----+-------------+-------+------+---------------+------+---------+------+------+-------------+<br />1 row in set (0.00 sec)<br />(This is the output from mysql-5.6.7-rc-linux2.6-x86_64.tar.gz. BTW<br />could you update 5.6 on launchpad, please?)<br /><br />Index merge is not chosen here though it's much cheaper than the table scan.<br /><br />Also almost any test case with multi-component indexes<br />from lp:~maria-captains/maria/5.3/mysql-test/t/range_vs_index_merge.test<br />fails to choose an index merge.<br /><br />I'm afraid the problem cannot be resolved with such simple tweaks in opt_range.cc as you used. They could be enough to fix all the reported<br />bugs you mentioned though.<br /><br />Igor Babaev<br />igor@askmonty.orgigorhttps://www.blogger.com/profile/06961843646062810762noreply@blogger.com