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?"
You may not notice any difference between 5.7 and 5.6, or even 5.5.
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.
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.
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:
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)
Lat/Long representation choices
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 If you want case folding, but accent sensitivity, please file a request at http://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 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
Request for tuning / slowlog info
Best of MySQL Forum -- index of lots of tips, discussions, etc
Analyze MySQL Performance
Analyze VARIABLEs and GLOBAL STATUS Analyze SlowLog
My slides from conferences
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
(older ones upon request)
Contact me via LinkedIn; be sure to include a brief teaser in the Invite request: