What's New in MySQL 5.7? Mostly "Query Rewrite"

Table of Contents

What is new in MySQL Version 5.7
Casual user
Indirect user
Advanced User
High level view of Query Rewrite
Types of Rewrite
How Query Rewrite works
Building Plugins
Reference
Postlog
Brought to you by Rick James


What is new in MySQL Version 5.7


There is a long, boring, list. I'll enumerate it later. More important is "what might be useful to you?". To answer that, let's ask "what kind of user are you?"

Casual user


You may not notice any difference between 5.7 and 5.6, or even 5.5.

Indirect user


Are you using Django, Drupal, Hibernate, Joomla, Magento, SQLAlchemy, Wordpress, or any other of the myriad of 3rd party products that uses MySQL under the hood? Are you having performance problems? Have you asked for help on these forums, but did not get much help since you could not change the SQL queries? Did you then go to the third party software and get no help there?

Then there is one feature of 5.7 that is made for you!

But... Unless you want to learn a lot about query optimization, you will still need to wait for someone to help you. Here's an overview how we can get 5.7 to improve your performance.
    ⚈  The slow queries are identified
    ⚈  Someone versed in query optimization designs a "plugin"
    ⚈  That plugin becomes available (open source, or whatever)
    ⚈  You install it with 5.7
    ⚈  Poof! Your system runs faster.

Advanced User


There are a lot of optimization improvements in 5.7. But what are you doing here? You can read the official list of features and you understand what they mean. OK, maybe I can help by explaining what it "really means", as I am trying to do with Query Rewriting in this blog.

High level view of Query Rewrite


You have a query that performs poorly. There are no indexes that would help. The only solution is to rewrite the SQL. But you are using a 3rd party software package that generates the SQL and does not let you tweak it. Well, Query Rewrite does let you change the SQL -- after the package generated it, but before MySQL executes it.

Types of Rewrite


You can rewrite the query in virtually any way, but here are some 'likely' changes:
    ⚈  Add STRAIGHT_JOIN or FORCE INDEX for a case where you know better than the optimizer.
    ⚈  Remove STRAIGHT_JOIN or FORCE INDEX from a query where the previous DBA thought he new better than the optimizer.
    ⚈  Remove LOWER() when it inhibits index usage.
    ⚈  Convert to using a subquery. Forum 22:616995 has an example that is possibly too complex to implement.
    ⚈  (maybe) Cache SHOW FIELDS for Drupal. Forum 10,620597.
    ⚈  (maybe) Fold proliferation of databases or tables into a single one (WordPress, Drupal) Forum 10:580012, 10,295417.
    ⚈  Turn "IN (SELECT...)" into a JOIN (in case new optimization is inadequate) Forum 20:597207.

How Query Rewrite works


Caution: I'm going to get technical here.

Here's a simplified look at how an SQL query is processed:
    ⚈  Parser breaks up the query into tokens
    ⚈  Optimizer decides on the most efficient way to execute the statement: which table to start with, which index to use, whether to use tmp tables, etc
    ⚈  Perform the query
    ⚈  Deliver the results

There are two times for the plugin can work -- pre-parse and post-parse.

Pre-parse:
    ⚈  Regular expressions, etc, can be used to pick apart the statement and replace it before parsing.

Post-parse, using parse tree:
    ⚈  The "parse tree" was generated by the Parser -- this is a data structure that has the components of the query. There are API calls to let you look at pieces.
    ⚈  The plugin manipulates the tree and builds a replacement query.
    ⚈  The revised statement is parsed, then on to Optimization, etc.

Post-parse, using hash:
    ⚈  Compute a 'signature' of the statement -- constants were replaced by '?', then a hash was taken. (This is already done if Performance Schema is turned on.)
    ⚈  A simple hash lookup says whether to launch the plugin for this statement. (This cost here is in nanoseconds.)
    ⚈  The revised statement is parsed, then on to Optimization, etc.

The overhead for the plugins is very small if the statement is left alone. The overhead when a statement is rewritten is "a few percent", mostly due to re-parsing. Hopefully, the rewritten query will run enough faster that the overhead won't matter.

Building Plugins


It is unclear who will build plugins, or when. One thing that might help the process is for you to provide a 'digested' summary of your SlowLog. From that, someone could probably deduce what rewrites would be beneficial.

Reference


Martin's blog on Query Rewrite
Post comments, questions, digested slowlogs, etc in Performance forum

Postlog


Written Oct, 2014; This blog needs revision -- 5.7 is evolving as we speak. Such as JSON (2015) and Document Store (2016).


Contact me by posting a question at
MySQL Forums :: Performance
-- Rick James

MySQL Documents by Rick James

HowTo Techniques for Optimizing Tough Tasks:

Partition Maintenance (DROP+REORG) for time series (includes list of PARTITION uses)
Big DELETEs - how to optimize -- and other chunking advice, plus a use for PARTITIONing
    Chunking lengthy DELETE/UPDATE/etc.
Data Warehouse techniques:
    Overview   Summary Tables   High speed ingestion  
Entity-Attribute-Value -- a common, poorly performing, design pattern (EAV); plus an alternative
Find the nearest 10 pizza parlors -- efficient searching on Latitude + Longitude (another PARITION use)
Pagination, not with OFFSET, LIMIT
Techniques on efficiently finding a random row (On beyond ORDER BY RAND())
GUID/UUID Performance (type 1 only)
IP Range Table Performance -- or other disjoint ranges
Rollup Unique User Counts
Alter of a Huge table -- Mostly obviated by 5.6
Latest 10 news articles -- how to optimize the schema and code for such
Build and execute a "Pivot" SELECT (showing rows as columns)
Find largest row for each group ("groupwise max")

Other Tips, Tuning, Debugging, Optimizations, etc...

Rick's RoTs (Rules of Thumb -- lots of tips)
Memory Allocation (caching, etc)
Character Set and Collation problem solver
    Trouble with UTF-8
Converting from MyISAM to InnoDB -- includes differences between them
Compound INDEXes plus other insights into the mysteries of INDEXing
Cookbook for Creating Indexes
    Many-to-many mapping table
MySQL Limits -- built-in hard limits
Galera, tips on converting to (Percona XtraDB Cluster, MariaDB 10, or manually installed)
5.7's Query Rewrite -- perhaps 5.7's best perf gain, at least for this forum's users
Best of MySQL Forum -- index of lots of tips, discussions, etc

View Rick James's profile on LinkedIn