MySQL Forge

Resources for the MySQL Community

Categories: Contributing | Development | MySQLUniversity | Optimizer

How MySQL Handles ORDER BY, GROUP BY, and DISTINCT

Contents 1 How MySQL Handles ORDER BY, GROUP BY, and DISTINCT 2 Presentation 3 Questions 3.1 Questions asked during the session 4 Voice recording and other links

← Back to MySQL University main page

[edit] How MySQL Handles ORDER BY, GROUP BY, and DISTINCT

• Date: 2007-11-01
• Presenter: Sergey Petrunia
• Scribe: Paul DuBois
• Attendees (please register by filling in your name below, and read the Instructions for Attendees):
  1. Alexander Nozdrin
  2. Alexey Kopytov
  3. Timour Katchaounov
  4. Tatjana A. Nurnberg
  5. Philip Stoev
  6. Georgi Kodinov

[edit] Presentation

• Abstract:
  • Survey of available execution strategies for ORDER BY/GROUP BY/DISTINCT
    = explanation of interplay between handling of those three
    = how they work together with NL-join execution algorithm
  • Optimization of ORDER BY/GROUP BY/DISTINCT
    = How the optimizer choses the execution strategy for those
    = Interaction with join optimizer optimization
• Slides: 318kb pdf

[edit] Questions

[edit] Questions asked during the session

1. (slide 2) It is possible to scan hash indexes on MEMORY tables IMO.
   • There is no benefit
   • no benefits, I agree
2. (slide 4) Is this decision always made in favor of avoiding sorting? (that is skip join buffering)
   • If you have a join order that matches, yes

[edit] Voice recording and other links

• Voice Recording: Ogg Audio (7MB)
• IRC log: IRC Text (4KB)
• Other resources:
  • Sergey's blog
  • Slide 4 extra information: Sergey's blog: Use of join buffer is now visible in EXPLAIN