tag:blogger.com,1999:blog-8607946055890352285.post3490863621797411691..comments2012-03-16T16:44:14.356+01:00Comments on Olav Sandstå: MySQL 5.6: Index Condition PushdownOlav Sandståhttp://www.blogger.com/profile/17087637481339681503noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-8607946055890352285.post-14856494911009283852012-03-16T16:44:14.356+01:002012-03-16T16:44:14.356+01:00Ovais: Yes, that is correct. If the first keypart ...Ovais: Yes, that is correct. If the first keypart had been a non-range condition the range optimizer would read only rows qualifying both keyparts in the first place. In this regard, ICP is the second best thing. <br /><br />A more tightly setup range is better whenever possible because that removes the need to read index entries while ICP only removes the need to read rows from the table.Jørgen Lølandhttps://www.blogger.com/profile/07423633132356058535noreply@blogger.comtag:blogger.com,1999:blog-8607946055890352285.post-71325687078418546052012-02-01T18:29:38.748+01:002012-02-01T18:29:38.748+01:00Olav:
In the example that you have given there is...Olav: <br />In the example that you have given there is a range condition on the postalcode column which happens to be the first column of the index, and because this is a range condition, therefore MySQL < 5.6 is not able to use the next part of the index (age column) to filter rows. So what you are implying is that this limitation is removed by ICP. Am I correct in thinking that?<br /><br />Nextly, for a non-range condition on the first column of the index, say postcalcode=5000 AND age BETWEEN 21 and 22, ICP is not going to make any difference, because both the columns of the index (postalcode, age) would already be used by the storage engine to filter the rows before passing them to the Server. Am I correct?<br /><br />Ovais.Ovais Tariqhttps://www.blogger.com/profile/04778564768448950777noreply@blogger.comtag:blogger.com,1999:blog-8607946055890352285.post-27297999469614075022011-04-18T04:57:50.401+02:002011-04-18T04:57:50.401+02:00I welcome anything that eliminates disk reads. Nic...I welcome anything that eliminates disk reads. Nice job.Mark Callaghanhttps://www.blogger.com/profile/09590445221922043181noreply@blogger.comtag:blogger.com,1999:blog-8607946055890352285.post-56669431202602753822011-04-15T16:04:12.991+02:002011-04-15T16:04:12.991+02:00Nars: Thanks for the comment. You are right about ...Nars: Thanks for the comment. You are right about that in order to push down parts of the condition to the storage engine it must be possible to evaluate the condition using the content of an index entry. The reason for this is that we get the greatest performance benefit when the storage engine can avoid reading the corresponding record from the table.<br /><br />If the server pushed down conditions that referred fields not stored in the index entry then the storage engine would have to read the record first before evaluating the condition. In this case the savings by having the storage engine evaluate the condition compared to having the server evaluate the condition would be much less.Olav Sandståhttps://www.blogger.com/profile/17087637481339681503noreply@blogger.comtag:blogger.com,1999:blog-8607946055890352285.post-4436626066172452772011-04-13T11:24:04.598+02:002011-04-13T11:24:04.598+02:00This is really good improvement.
But it looks like...This is really good improvement.<br />But it looks like only queries having indexed columns in WHERE clause will get benefited using this improvement.<br /><br />NarsNarshttps://www.blogger.com/profile/10726589003131745546noreply@blogger.com