tisdag 10 december 2013

Range access: now in an IN predicate near you.

Several users have reported that certain queries with IN predicates can't use index scans even though all the columns in the query are indexed. What's worse, if you reformulate your query without IN, the indexes are used. Let's take some example query. Suppose we have a table with two indexed columns:

CREATE TABLE t1 ( 
  col1 INTEGER,
  col2 INTEGER,
  ...
  KEY key1( col1, col2 )
);

Let's take a look at some queries that could take advantage of the key1 index to read rows without accessing the table.

  1. SELECT col1, col2 FROM t1 WHERE col1 = 100;
  2. SELECT col1, col2 FROM t1 WHERE col1 > 100 AND col1 < 200;
  3. SELECT col1, col2 FROM t1 WHERE col1 > 100 AND col1 < 200 OR col1 > 300 AND col1 < 400;
  4. SELECT col1, col2 FROM t1 WHERE col1 = 100 AND col2 > 100 AND cold2 < 200;

These queries will use what MySQL calls Index Range Scans. (although the first query could also use Ref Scan). This access method will fetch rows from the index trees given a start and end value. It's also possible to read multiple intervals, each with a start and end value, as we saw in query 3 above.

A special case of intervals is when the endpoints are the same value. Range scans can be used for conditions such as col1 = 100 because it's equivalent to the interval 100 <= col1 <= 100. This way we can use range scans for a broader class of queries.

Armed with multiple-interval scans, a.k.a. multi-range reads, or MRR for short, we can use the range access for queries such as 

SELECT col1, col2 FROM t1
WHERE col1 = 100 or col1 = 200 or col1 = 300;

We can use all columns in the index of course:

SELECT col1, col2 FROM t1
WHERE col1 = 100 AND col2 = 100
   OR col1 = 200 AND col2 = 200 
   OR col1 = 300 AND col2 = 300;

At some point, this syntax becomes unwieldy. And this isn't just aesthetics, for really big queries, we get a combinatorial blowup which can cause parsing to take a long time. This is the reason why SQL has IN predicates to say the same thing:

SELECT col1, col2 FROM t1
WHERE col1 = 100 OR col2 = 200 OR col2 = 300;

means the same as

SELECT col1, col2 FROM t1
WHERE col1 IN (100, 200, 300);

And for rows it gets even more convenient:

SELECT col1, col2 FROM t1
WHERE col1 = 100 AND col2 = 100
   OR col1 = 200 AND col2 = 200 
   OR col1 = 300 AND col2 = 300;

can be written as

SELECT col1, col2 FROM t1
WHERE (col1, col2) IN ((100, 100), (200, 200), (300, 300));

The problem that users saw is that suddenly MySQL doesn't use MRR any more, and resorts to scanning the entire index. This is because the range optimizer ignored IN conditions over rows. The range optimizer is the sub-optimizer that analyzes conditions and translates them into a multi-range structure that can be handed to the storage engine to fetch the rows from the index. It handled IN predicates as long as they were over scalars or just a single row, but completely ignored lists of rows.

As of 5.7.3 this hole in the net is stitched up. The range optimizer gladly opens the door for queries with IN predicates as long as
  • The predicate is only IN, not NOT IN.
  • The row on the predicate's left-hand side is only indexed column references, in the same index.
  • The rows contain only constants or come from a previously read table in nested-loops join.
Note that 'constants' is a pretty broad category. It consists of pre-evaluated expressions, even some sub-queries, SQL variables and similar beings.

28 kommentarer:

  1. And by that, MySQL bug 31188 and all its duplicates (16247, 35819, 40029, 50571, 61541, 63167 and 64706) are fixed. Thanks Martin!

    SvaraRadera
  2. Did you test that those are actually fixed, or just guessing?

    SvaraRadera
    Svar
    1. They were all marked as duplicates without any objections from the feature request reporters, but since you asked I just verified that they were indeed duplicates :-)

      Radera
  3. I like how 16247 is a duplicate of 31188. Time machine!

    SvaraRadera
  4. Yep... but as MarkC says: "This is the same as http://bugs.mysql.com/bug.php?id=16247, but the author of this feature request is much more convincing." :-)

    SvaraRadera


  5. Awesome article. It is so detailed and well formatted that i enjoyed reading it as well as get some new information too.





    datawarehousing Training in Chennai


    Base SAS training in Chennai

    SvaraRadera
  6. Very nice… i really like your blog............visit our website for more information and you can get online training facilities in R12 Procurement Training

    SvaraRadera
  7. we have almost all country students as our subscribers for online course.We have 10+ years of experience we can serve various ascent people. oracle fusion Cloud HCM online training at erptree.com is worlds best online training center. we have excelent knowledge sharing Platform we have user friendly website where you will be provided with all the required details and Self-paced DEMO videos. we have our branches in pune,
    gurgaon, noida, india, usa, uk, uae, oracle fusion hcm training, fusion Procurement training, fusion hcm, scm training

    SvaraRadera
  8. Thanks for such a great website which is helping people who is new to oracle apps and professional also.Your site is very impressive and you are doing an amazing job.
    Oracle Fusion Cloud HCM Online Training

    SvaraRadera
  9. very interesting article I feel very enthusiastic while reading and the information
    provided in this article is so useful for me. Content in this article guides in clarifying some of my doubts.

    oracle fusion financials training
    oracle fusion financials online training
    oracle fusion Procurement training training
    oracle fusion procurement online training

    SvaraRadera
  10. What MySQL calls Index Range Scans.

    Can you briefly dig into it please?

    SvaraRadera
  11. It's A Great Pleasure reading your Article, learned a lot of new things, we have to keep on updating it salesforce certification training Thanks for posting.

    SvaraRadera

  12. Hey, Wow all the posts are very informative for the people who visit this site. Good work! We also have a Website. Please feel free to visit our site. Thank you for sharing. iot course fees in chennai | iot certification courses in chennai | iot certification chennai | iot training courses in chennai

    SvaraRadera
  13. Excellent blog I visit this blog it's really awesome. The important thing is that in this blog content written clearly and understandable. The content of information is very informative.
    Oracle Fusion HCM Online Training
    Oracle Fusion SCM Online Training
    Oracle Fusion Financials Online Training
    oracle fusion financials classroom training
    Workday HCM Online Training
    Oracle Fusion HCM Classroom Training

    SvaraRadera
  14. A befuddling web diary I visit this blog, it's incredibly grand. Strangely, in this present blog's substance made motivation behind fact and sensible. The substance of information is instructive
    Oracle Fusion Financials Online Training
    Oracle Fusion HCM Online Training
    Oracle Fusion SCM Online Training

    SvaraRadera
  15. Thanks for updating this quality stuff. Treasurebox always provide you quality stuff for your home and garden.
    We will also provide you outdoor furniture with home delievery.

    SvaraRadera
  16. The article is very interesting and very understood to be read, may be useful for the people. I wanted to thank you for this great read!! I definitely enjoyed every little bit of it. I have to bookmarked to check out new stuff on your post. Thanks for sharing the information keep updating, looking forward for more posts..
    Kindly visit us @
    Madurai Travels | Travels in Madurai
    Best Travels in Madurai
    Cabs in Madurai | Madurai Cabs
    Tours and Travels in Madurai

    SvaraRadera
  17. Excellent Blog. I really want to admire the quality of this post. I like the way of your presentation of ideas, views and valuable content. No doubt you are doing great work. I’ll be waiting for your next post. Thanks .Keep it up!
    Kindly visit us @
    Luxury Packaging Box
    Wallet Box
    Perfume Box Manufacturer
    Candle Packaging Boxes
    Luxury Leather Box
    Luxury Clothes Box
    Luxury Cosmetics Box
    Shoe Box Manufacturer
    Luxury Watch Box

    SvaraRadera
  18. Good blog information
    Best Play and Pre School for kids in Hyderabad,India. To give your kid a best environment and learning it is the right way to join in play and pre school were kids can build there physically, emotionally and mentally skills developed. We provide programs to kids like Play Group, Nursery, Sanjary Junior, Sanjary Senior and Teacher training Program.
    ­play school in hyderabad

    SvaraRadera
  19. I believe there are many more pleasurable opportunities ahead for individuals that looked at your site.Needed to compose you a very little word to thank you yet again regarding the nice suggestions you’ve contributed here.
    Sql server dba online training

    SvaraRadera
  20. Den här kommentaren har tagits bort av skribenten.

    SvaraRadera
  21. Good blog post information I liked it
    Sanjary Academy is the best Piping Design institute in Hyderabad, Telangana. It is the best Piping design Course in India and we have offer professional Engineering Courses like Piping design Course, QA/QC Course, document controller course, Pressure Vessel Design Course, Welding Inspector Course, Quality Management Course and Safety Officer Course.
    Piping Design Course in Hyderabad ­

    SvaraRadera
  22. I was scrolling the internet like every day, there I found this article which is related to my interest. The way you covered the knowledge about the subject and the top builders in bhopal was worth to read, it undoubtedly cleared my vision and thoughts towards B 3 bhk flat in ayodhy bypass road . Your writing skills and the way you portrayed the examples are very impressive. The knowledge about 2 bhk flat in ayodhya bypaas road is well covered. Thank you for putting this highly informative article on the internet which is clearing the vision about top builders in Bhopal and who are making an impact in the real estate sector by building such amazing townships.

    SvaraRadera
  23. Excellent information of the blog provided by the author

    Pressure Vessel Design Course is one of the courses offered by Sanjary Academy in Hyderabad. We have offer professional Engineering Course like Piping Design Course,QA / QC Course,document Controller course,pressure Vessel Design Course,Welding Inspector Course, Quality Management Course, #Safety officer course.
    Welding Inspector Course
    Safety officer course
    Quality Management Course
    Quality Management Course in India

    SvaraRadera
  24. Misbah Digital Marketing is an excellent online marketing agency in Bangalore.Misbah Digital Marketing!
    Misbah Digital Marketing! having certified professionals in Google Ads, Google Analytics and Bing Ads.

    Misbah Digital Marketing!

    SvaraRadera
  25. Well done! Pleasant post! This truly helps me to discover the solutions for my inquiry. Trusting, that you will keep posting articles having heaps of valuable data. You're the best!
    Data Science Online Training

    SvaraRadera