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.

83 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. 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
  13. Den här kommentaren har tagits bort av skribenten.

    SvaraRadera
  14. 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
  15. 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
  16. 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
  17. 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
  18. 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
  19. Great blog !It is best institute.Top Training institute In Velachery
    http://trainingsinvelachery.in/nodejs-training-in-velachery/
    http://trainingsinvelachery.in/reactjs-training-in-velachery/
    http://trainingsinvelachery.in/ccna-training-in-velachery/
    http://trainingsinvelachery.in/ccnp-training-in-velachery/
    http://trainingsinvelachery.in/mcsa-training-in-velachery/
    http://trainingsinvelachery.in/mcse-training-in-velachery/

    SvaraRadera

  20. I am very happy when read this blog post because blog post written in good manner and write on good topic. Thanks for sharing valuable information.

    Java training in chennai | Java training in annanagar | Java training in omr | Java training in porur | Java training in tambaram | Java training in velachery

    SvaraRadera
  21. Thanks for provide great informatic and looking beautiful blog, really nice required information & the things i never imagined and i would request, wright more blog and blog post like that for us. Thanks you once agian

    name change procedure in ghaziabad
    name change procedure delhi
    name change procedure gurgaon
    name change in faridabad
    name change in noida
    name change
    name change in india
    name change procedure in bangalore
    name change procedure in rajasthan
    name change procedure in maharashtra

    SvaraRadera
  22. Providing the right information how to use databse queruies. Thank for nice blog. In case someone want to know about IT training courses in Rohtak.
    https://merientinfotech.com/course/digital-marketing
    https://merientinfotech.com/course/php/
    https://merientinfotech.com/course/html/
    https://merientinfotech.com/course/wordpress/
    https://merientinfotech.com/course/seo/
    https://merientinfotech.com/course/c-language/
    https://merientinfotech.com/course/cpp-langauge/
    https://merientinfotech.com/course/basic-computer/.

    SvaraRadera
  23. Nice & Informative Blog !
    In case you face any technical issue in QuickBooks, call us at QuickBooks Customer Service Phone Number 1-(855) 550-7546 and get feasible solutions for QuickBooks problems.

    SvaraRadera
  24. Hey! Good blog. I was facing an error in my QuickBooks software, so I called QuickBooks Support Phone Number USA (855)756-1077. I was tended to by an experienced and friendly technician who helped me to get rid of that annoying issue in the least possible time.

    SvaraRadera
  25. Thanks for sharing this information. fue turkey

    SvaraRadera
  26. Hey! Mind-blowing blog. Keep writing such beautiful blogs. In case you are struggling with issues on QuickBooks software, dial QuickBooks Support (877)603-0806. The team, on the other end, will assist you with the best technical services.

    SvaraRadera
  27. Hey! Lovely blog. Your blog contains all the details and information related to the topic. In case you are a QuickBooks user, here is good news for you. You may encounter any error like QuickBooks Error, visit at QuickBooks Customer Service for quick help.

    SvaraRadera
  28. Hey! Well-written blog. It is the best thing that I have read on the internet today. Moreover, if you are looking for the solution of QuickBooks Software, visit at QuickBooks Error 404 to get your issues resolved quickly.

    SvaraRadera

  29. Hey! Well-written blog. It is the best thing that I have read on the internet today. Moreover, if you are looking for the solution of QuickBooks Software, visit at QuickBooks Support Phone Number to get your issues resolved quickly.

    SvaraRadera
  30. Hey! Lovely blog. Your blog contains all the details and information related to the topic. In case you are a QuickBooks user, here is good news for you. You may encounter any error like QuickBooks Error, visit at QuickBooks Customer Service Number for quick help.

    SvaraRadera
  31. Thank you for this post! If you want to make youtube video about this optimization you can get some likes from this site https://soclikes.com/ for your video

    SvaraRadera
  32. You really make it seem so easy with your presentation but I find this matter to be really something which I think I would never understand. It seems too complicated and very broad for me. I am looking forward for your next post, I will try to get the hang of it!

    립카페

    SvaraRadera
  33. I have always disliked the idea because of the costs.

    외국인출장

    SvaraRadera
  34. Thanks for sharing such a wonderful information from this post. We are also providing the best services click on below links to visit our website.
    Oracle Fusion HCM Training
    Workday Training
    Okta Training
    Palo Alto Training
    Adobe Analytics Training

    SvaraRadera
  35. Hey! Well-written blog. It is the best thing that I have read on the internet today. Moreover, if you are looking for the solution of QuickBooks Software, visit at QuickBooks Customer Service (855)538-8273 to get your issues resolved quickly.

    SvaraRadera
  36. Great work! It is the best thing that I have read on the internet today. Moreover, If you encounter any error while working on QuickBooks software , do contact this QuickBooks Customer Service Number (855)963-5959 number for quick assistance.

    SvaraRadera
  37. 안전토토사이트29 december 2021 kl. 22:36

    When I read your article on this topic, the first thought seems profound and difficult. There is also a bulletin board for discussion of articles and photos similar to this topic on my site, but I would like to visit once when I have time to discuss this topic. 안전토토사이트
    When I read your article on this topic, the first thought seems profound and difficult. There is also a bulletin board for discussion of articles and photos similar to this topic on my site, but I would like to visit once when I have time to discuss this topic. 안전토토사이트

    SvaraRadera
  38. nice blog. if you are searching for a quickbook customer service you can contact us on call.+1 866-669-5068

    SvaraRadera
  39. Awesome bolg.if you looking for a best quickbook customer service you can contact us on phone call.+1 888-272-4881

    SvaraRadera
  40. nice blog. if you are looking for Quickbook support service you can contact us at.+1 866-669-5

    SvaraRadera
  41. webgirls In terms of preventing yeast infections, victims usually have their operate eliminate on their behalf. It is because candidiasis can certainly grow to be long-term and continuing. With that in mind, in this article, we are going to provide a variety of the best proven candida treatment and avoidance tips close to.

    SvaraRadera
  42. Nice & Informative Blog !
    In case you face any technical issue in QuickBooks, call us at QuickBooks Customer Service Phone Number+18554442233 and get feasible solutions for QuickBooks problems.

    SvaraRadera
  43. Good content!!
    if you want to a best service so go to Quickbook support serviceyou can contact us at.+1 888-210-4052

    SvaraRadera
  44. https://gamebegin.xyz You can exercise by itself. A pitching machine enables you to set up the speed of your ball. By packing several baseballs to the equipment, it is possible to exercise striking without the need for a pitcher. This electrical unit is ideal for individuals who wish to exercise baseball by yourself. Pitching equipment may be found in your neighborhood wearing merchandise store.

    SvaraRadera
  45. https://gameeffect.xyz A lot of people have cherished the overall game of baseball for a long time. You will find followers around the globe, from committed tiny-leaguers to die-tough spectators. This article has tips to prove how pleasant baseball actually is.

    SvaraRadera
  46. Hey! Mind-blowing blog. Keep writing such beautiful blogs. In case you are struggling with issues on QuickBooks software, dial QuickBooks Customer Service Number +1 855-548-4814. The team, on the other end, will assist you with the best technical services.+1 855-548-4814.

    SvaraRadera
  47. https://gameboot.xyz You can see them on publications and on Television, men and women who appear like their arms and thighs will explode since their muscles are really massive! There is absolutely no need that you can take your whole body for that degree should you don't desire to, as the simple techniques in this article will help you to construct muscles in a healthier way.

    SvaraRadera
  48. https://gamezoom.xyz Acquiring a exercise companion can considerably boost your muscle-constructing effects. Your partner can be quite a valuable supply of determination for sticking with your regular workout treatment, and forcing you to optimize your efforts whilst you exercise. Possessing a dependable spouse to determine with will also help make you stay safe since you will possess a spotter.

    SvaraRadera
  49. I’ve been browsing online more than 3 hours today, yet I never found any interesting article like yours. It’s pretty worth enough for me. In my opinion, if all webmasters and bloggers made good content as you did, the internet will be much more useful than ever before.

    BSc 3rd year result

    SvaraRadera
  50. Awesome bolg. Keep calm your Mind anwith peaceful Yoga and regulate it with peaceful yoga Suryanamaskar. meditation basics yoga , yoga benefits , types of yoga , yoga history , health benefit yoga , yoga pose , yoga asanas - theyogainfo.com you reach us at

    SvaraRadera
  51. Your blog great. New chapter of life is yoga , Let's start to do. provide yoga , meditation basics yoga , theyogainfo.com you reach us at

    SvaraRadera
  52. It`s a very good blog. Start your day from Suryanamaskar to make day Energetic. meditation basics yoga , yogainfo, theyogainfo.com you reach us at


    SvaraRadera
  53. When you or your company need help with QuickBooks or any other aspect of your business, dial Quickbooks Suppport Phone Number +1 866-669-5068,NJ.

    SvaraRadera
  54. If you need help on correctable errors or issues on your desktop, call us at Quickbooks Customer Service Phone Number+1 855-769-6757 to get the best services. We are here 24/7 and are ready to provide answers to all your QuickBooks questions.

    SvaraRadera
  55. Thanks for sharing Supper blog. My website for best yogainfo any types yoga information and update yoga news. Yoga, yogainfo , Female fitness, you reach us at

    SvaraRadera
  56. Your style is really unique compared to other people I have read stuff from. Thank you for posting when you’ve got the opportunity, Guess I’ll just book mark this site.
    mgsu ba 1st year result subject wise

    SvaraRadera
  57. Thanks For Sharing awesome blog if you are suffering any Quickbooks issue while Quickbooks Update then you can get solution from
    Quickbooks Error Support +1 (213) 417-0111

    SvaraRadera