At a recent optimizer webinar, I talked about MySQL introducing a new style for hints, and that MySQL 5.7 also added support for more hints, see Sergey Glukhov’s blog. A question I got at the end of the webinar was what to do with the hints in the application code after an upgrade?…

In a recent blog post my colleague Roy presented his work for MySQL 5.7 that makes the query optimizer merge sub-queries in the  FROM clause (so-called derived tables) with the outer query.  I will in this blog post show an example of how this work improves the performance of a MySQL query.…

In MySQL 5.7, we have enhanced the optimizer so that derived tables and views are handled more consistently.

Just to recap: A derived table is a subquery that can take the place of a table in the FROM clause of an SQL statement.…

Generally in MySQL we send queries massaged to a point where optimizer doesn’t have to think about anything. In our major user database environment 99.9% of queries don’t have alternative query plans available (either because of forced indexes or just straightforward Primary Key read). We have various other systems and from time to time we have to do SQL work there and chase optimizer errors.

There’re multiple places where optimizer can make a choice in very basic queries, for example:

• Which index returns less rows
• Which index can be used for ORDER BY

A query that I was looking asked a very basic question, on a job instances table, show state and status for latest-by-ID entry for job name=’Ship Christmas Presents’ (real name was a bit different ;-). So, it was SELECT c,d FROM t WHERE b=X ORDER BY a DESC LIMIT 1, where PK is (a) and a possible index is on …

We discussed in an earlier post how to design indexes for many types of queries using a single table. Here is a real-world example of the challenges you will face when trying to optimize queries: two similar queries, but one is performing a full table scan while the other one is using the index we specially created for these queries. Bug or expected behavior? Read on!

Our two similar queries

# Q1
mysql> explain select col1, col2 from t where ts >= '2015-04-30 00:00:00';
+----+-------------+---------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table         | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+---------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | t …

In version MySQL 5.7.7 Oracle presented a new promising feature: optimizer hints. However it did not publish any documentation about the hints. The only note which I found in the user manual about the hints is:

• It is now possible to provide hints to the optimizer by including /*+ ... */ comments following the SELECT, INSERT, REPLACE, …

This is a follow-up post to my recent announcement of only_full_group_by improvements in 5.7, where I’d like to address some tricky corner cases where GROUP BY contains non-column expressions (functions).

In the SQL standard, GROUP BY should contain only table columns and never expressions. So this example query where we want to count how many people have the same name is illegal in the SQL2011 standard because the GROUP BY clause contains a function:

CREATE TABLE people (first_name VARCHAR(100), last_name VARCHAR(100));

SELECT CONCAT(first_name, ' ', last_name), COUNT(*)
FROM people
GROUP BY CONCAT(first_name, ' ', last_name);

But MySQL does allow expressions in a GROUP BY clause, and it’s a very convenient addition to the standard. The logic …

In MySQL 5.6 we introduced a huge improvement in the way that index and table statistics are gathered by InnoDB and subsequently used by the Optimizer during query optimization: Persistent Statistics. Some aspects of the way that Persistent Statistics work could be improved further though, and we’d really like your input on that.

How much to sample?

The statistics are gathered by picking some pages semi-randomly, analyzing them, and deriving some conclusions about the entire table and/or index from those analyzed pages. The number of pages sampled can be specified on a per-table basis with the STATS_SAMPLE_PAGES clause. For example:

ALTER TABLE t STATS_SAMPLE_PAGES=500;

This way …

Here’s an account of recent work which has kept me busy and excited for a few months. For those unfamiliar with the only_full_group_by sql mode, let me provide some context. I’ll use the world database, which can be downloaded from this MySQL Documentation page. You can find details on how to install the database on the same page. We have a table of countries and a table of languages spoken in each country. It’s a 1-to-N relationship: a country can have many languages so CountryLanguage.CountryCode is a foreign key referencing Country.Code:

mysql> desc Country;
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra | …

In my last post I covered how to use the query rewrite framework to write your own pre-parse plugin. The interface is simplistic: a string goes in, a string comes out, and the rest is up to your plugin’s internal workings. It doesn’t interact that much with the server. Today I am going to show you the other type of plugins that rewrite queries, post-parse query rewrite plugins. This type is, out of necessity, more tightly coupled with the server; it operates on the internal data structures that make up the query’s parse tree.

Creating the Plugin

Declaring the plugin is similar to declaring a pre-parse plugin: you declare the plugin in the usual way but with the addition of a specific plugin descriptor for the post-parse query rewrite plugin type. This is a struct, as usual:

struct …