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



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.

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


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


Written Oct, 2014; This blog needs revision -- 5.7 is evolving as we speak. Such as JSON (2015) and Document Store (2016).
-- 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:
    Data Warehouse Overview   Summary Tables   High speed ingestion   Bulk Normalization  

Schema and code design for large Sensor database

Entity-Attribute-Value (EAV) -- a common, poorly performing, design pattern; plus an alternative

5 methods for 'Find Nearest'

Lat/Lng search to Find the nearest 10 pizza parlors
    Lat/Long representation choices

Z-Order 'find nearest'

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

Efficient List of Latest 10 news articles

Build and execute a Pivot SELECT (showing rows as columns)

(Groupwise Max): Efficiently find largest row(s) for each group

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

Rick's RoTs (Rules of Thumb -- lots of tips)

Datatypes and building a good schema

Memory Allocation (caching, etc)

Character Set and Collation problem solver
    Trouble with UTF-8   If you want case folding, but accent sensitivity, please file a request at https://bugs.mysql.com .
    Python tips,   PHP tips,   other language tips
    utf8 Collations   utf8mb4 Collations on 8.0

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   Handler counts   wp_postmeta   UNION+OFFSET

MySQL Limits -- built-in hard limits
    767-byte INDEX limit

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

Analyze MySQL Performance
    Analyze VARIABLEs and GLOBAL STATUS     Analyze SlowLog

My slides from conferences
MiniFest 2021 - Rick James & Daniel Black - Answering on Stack Overflow(+comments) - MariaDB Frontlines
Percona Live 4/2017 - Rick's RoTs (Rules of Thumb) - MySQL/MariaDB
Percona Live 4/2017 - Index Cookbook - MySQL/MariaDB
Percona Live 9/2015 - PARTITIONing - MySQL/MariaDB

Contact me via LinkedIn; be sure to include a brief teaser in the Invite request:   View Rick James's profile on LinkedIn

Did my articles help you out? Like what you see? Consider donating:

☕️ Buy me a Banana Latte and bagel ($10) There is no obligation but it would put a utf8mb4 smiley 🙂 on my face, instead of the Mojibake "🙂"