Xaprb

Stay curious!

High Performance MySQL, Second Edition: Advanced SQL Functionality

with 10 comments

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)

Further Reading:

Written by Xaprb

October 5th, 2007 at 11:11 am

Posted in Uncategorized

Tagged with , , ,

«
»

10 Responses to 'High Performance MySQL, Second Edition: Advanced SQL Functionality'

Subscribe to comments with RSS

  1. 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.

    William Newton

    5 Oct 07 at 11:24 am

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

    Lukas

    5 Oct 07 at 11:39 am

  3. 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.

    Xaprb

    5 Oct 07 at 12:26 pm

  4. 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).

    Lukas

    5 Oct 07 at 5:58 pm

  5. 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!

    Xaprb

    5 Oct 07 at 6:01 pm

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

    Lukas

    5 Oct 07 at 6:05 pm

  7. Yeah, that’s a good idea.

    Xaprb

    5 Oct 07 at 6:09 pm

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

    Xaprb

    5 Oct 07 at 6:11 pm

  9. [...] Archives « High Performance MySQL, Second Edition: Advanced SQL Functionality [...]

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

    7 Oct 07 at 4:05 pm

  10. Very well written book, everything is explained from the very begging to very sophisticated stuff.

    Vahur

    8 May 09 at 4:37 am

Leave a Reply