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
Data Warehouse techniques:
Schema and code design for large Sensor database
Entity-Attribute-Value (EAV) -- a common, poorly performing, design pattern; plus an alternative
Lat/Lng search to Find the nearest 10 pizza parlors
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).
-- Rick James
MySQL Documents by Rick James
HowTo Techniques for Optimizing Tough Tasks:
Chunking lengthy DELETE/UPDATE/etc.
Data Warehouse Overview
Summary Tables
High speed ingestion
Bulk Normalization
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
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:
