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:High Performance MySQL, mysql, optimization, sql
You might also like:
- High Performance MySQL, Second Edition: Query Performance Optimization
- High Performance MySQL, Second Edition: Backup and Recovery
- Progress on High Performance MySQL Backup and Recovery chapter
- High Performance MySQL, Second Edition: Schema Optimization and Indexing
- Progress report on High Performance MySQL, Second Edition
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.
Hmm aggregates and group by in general seems to be missing.
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.
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).
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!
Well I would get rid of “SQL” in the chapter name. “Advanced” is fine. Maybe replace “SQL” with “Features” or “Functionality”.
Yeah, that’s a good idea.
Advanced MySQL Features it is (he decides, without consulting the other authors ;-)