High Performance MySQL, Second Edition: Advanced SQL Functionality

Work continues apace on High Performance MySQL, Second Edition (the link leads to the chapter outline). I’m working now on Chapter 6, Advanced SQL Functionality, and thought I’d solicit input on it. Are there things you’d like to see us cover? Do you have any favorite techniques you’d like to see us include? Feel free to leave feedback in the comments. The chapter is already significantly done, with 26 pages written, but the ink’s not on paper yet, so there’s still time to correct omissions!

I should note that there are separate chapters on architecture, schema and indexing design, application optimization, query optimization, etc. We’re trying to focus this chapter on “advanced features” and how to get high performance out of them. Also, the outline is still subject to change: there’s so much material that it’s hard to decide the best place to put something, and exactly what is an “advanced feature” might be open to interpretation, or we might feel something is better placed elsewhere. That said, please throw your ideas at us and we’ll worry about the details for you. Here’s the outline of this chapter, as of three minutes ago:

[Intro]
The MySQL Query Cache
  Operational Detail and Caveats
  Optimizations
  Alternatives
Prepared Statements and Multiple Query Execution
  Client-side Prepared Statements
  Client-side Prepared Statements
  Optimization of the Execution Plan
  Server-side Prepared Statements
  Prepared Statements in Stored Procedures
  Prepared Statement Caveats
Cursors
  Client-side Cursors
Stored Procedures
  Pros and Cons
  Recommendation
  Example
User Defined Functions
Events
Views
  Updatable Views
  Limitations of Updatable Views
  Security
  Performance
Triggers
Working with Multi-byte Character Sets
Full-Text Search
  Full-Text Search Queries
  Changes in MySQL 5.1
  Full-Text Trade-Offs and Workarounds
  Full-Text Tuning and Optimization
Foreign Key Constraints
Merge Tables and Partitioning
  Merge Tables
    Merge Table Performance Limitations
    Merge Table Strengths
  Partitioned Tables
    Optimizing Queries Against Partitioned Tables
    Maintaining Partitioned Tables
Distributed Transactions (XA)
Technorati Tags:, , ,

You might also like:

  1. High Performance MySQL, Second Edition: Query Performance Optimization
  2. High Performance MySQL, Second Edition: Backup and Recovery
  3. Progress on High Performance MySQL Backup and Recovery chapter
  4. High Performance MySQL, Second Edition: Schema Optimization and Indexing
  5. Progress report on High Performance MySQL, Second Edition

9 Responses to “High Performance MySQL, Second Edition: Advanced SQL Functionality”


  1. 1 William Newton

    I’d like to see some information about federated tables as well. I think it would make sense to include them in the section about merge tables and partitioning. I’d also like to see some discussion about when it make sense to use which one of the methods.

  2. 2 Lukas

    Hmm aggregates and group by in general seems to be missing.

  3. 3 Xaprb

    William, thanks for the suggestion. I’m going to have to bring this up via email with the authors and editor (and I’ll check chapter 2, which might already include it).

    Lukas, those are good topics. I will have to double-check but I think we cover them in chapter 5, Query Optimization.

  4. 4 Lukas

    I think for federated it is enough at this point to just say there are way too limited at this point. Also the term “advanced SQL” seems a bit fuzzy to me now that I see topics like merge tables. I would expect advanced stuff I can write into my DML queries (not so much DDL). So stuff like “WITH ROLLUP”, maybe even SQL hints (though that would better fit in an optimization chapter).

  5. 5 Xaprb

    It is quite a fuzzy term, isn’t it? I never thought of it this way, but maybe the chapter is just flat-out named wrong. Can you think of a better name? I’ve got tunnel vision at the moment, so I’m not coming up with anything better.

    SQL hints are in the optimization chapter, where we talk about the query optimizer and how to influence it. I should post the outline for that one too; it’s mostly done.

    WITH ROLLUP should go in the GROUP BY optimization section, which is NOT written yet. I’ll make a note of this if it’s not there yet. Thanks!

  6. 6 Lukas

    Well I would get rid of “SQL” in the chapter name. “Advanced” is fine. Maybe replace “SQL” with “Features” or “Functionality”.

  7. 7 Xaprb

    Yeah, that’s a good idea.

  8. 8 Xaprb

    Advanced MySQL Features it is (he decides, without consulting the other authors ;-)

  1. 1 High Performance MySQL, Second Edition: Query Performance Optimization at Xaprb

Leave a Reply

Please do not use this blog to get help with problems or bugs in Maatkit or innotop: use the Sourceforge forums, mailing list, or bug trackers. If you're asking for help with MySQL, please use the MySQL mailing list instead. I'm writing a book and my time is extremely limited :-)