Happy 25th Birthday MySQL!

Today, May 23-rd 2020, MySQL turns 25 years old. I first got acquainted with it during my university years as I was searching for a free and open RDBMS system to learn and experiment with SQL. And I used it ever since then. I thus followed the evolvement of "The world's most popular open source database" over the past about 20 years since 3.2x versions. It's a big anniversary. So what does MySQL mean for me?

• First and foremost, MySQL is the relational (and also non-relational) database of choice for both my personal and professional projects. For me it's easier to think new database schemes in the terms of MySQL and architect new systems on top of MySQL as a database system;
• MySQL is getting better and better with the years, which keeps me interested. I still remember the arrival of:
• 4.0 (with support for unions, etc.),
• then 4.1 (sub-queries and prepared statements, etc.),
• then 5.0 (stored routines, triggers and views, distributed transactions, etc.),
• then 5.1 (table partitioning, XML and event scheduler, etc.),
• then 5.5 (SIGNAL and RESIGNAL, PERFORMANCE_SCHEMA),
• then 5.6 (Full-text for InnoDB, etc.),
• then 5.7 (JSON, named triggers, generated columns, optimizer hints, etc.),
• and more recently 8.0 (CTEs, window functions, regular expressions improvements, GROUPING function, document store, CHECK constraints, LATERAL, functional key parts and defaults and many, many more);
• I'm building MySQL server (and other products) for Slackware Linux since 2005, because at first I needed newer versions than the ones provided by the official repository and then, because Slackware switched to MariaDB in 2013, but I choose to stick with MySQL. This doesn't mean I do not use MariaDB at all and in some aspects I believe it's ahead;
• I'm reporting bugs for MySQL and related products, so I could get problems resolved (see the story of some of my bug reports);
• I'm blogging about MySQL for the past 2 years since the release of MySQL 8.0, which helps me get familiar with the new features;
• I'm also presenting about MySQL from time to time, which helps me engage with the community, learn from and exchange ideas with the more experienced then me;
• Last, but definitely not least, there is a great and very strong community around MySQL, with which I got the chance to engage more in the past 3 years. This is what makes a project like MySQL strong over so many years.

To put it simply for me MySQL is a lot more than a database. MySQL has been with me for a significant part of my life so far and it makes me happy to see the database continues to evolve. Still I believe that improved SQL standard support and new development features should have arrived earlier, but better later than never. Also the development could be more open, but I'm confident that the future is open and bright.

Here's to the next 25 years and beyond! Happy birthday MySQL! Cheers to all that use and love MySQL!

Functional dependencies recognition in groupping by MySQL

If you've used MySQL in the past you should remember it was possible by default to aggregate in queries without a GROUP BY clause. So you could write a query like this one

SELECT D.dname, COUNT(E.empno) cnt
  FROM dept D,
       emp  E
 WHERE D.deptno = E.deptno;

most probably with the idea to count the employees in each department. However, the result you'll get (without error) would be:

+------------+-----+
| dname      | cnt |
+------------+-----+
| ACCOUNTING |  14 |
+------------+-----+

which is simply incorrect and most probably not what you actually want. Such query would result in an error in other databases (e.g. Oracle would give ORA-00937: not a single-group group function, PostgreSQL would say SQL state: 42803 ERROR: column "d.dname" must appear in the GROUP BY clause or be used in an aggregate function, etc.).

The reason for this behavior was the default SQL mode of the server and more precisely the absence of ONLY_FULL_GROUP_BY in the list with modes. There are some applications (like Bugzilla and Cacti) that did not allow me to enable this mode on my server although it was long suggested by some (e.g. I remember the presentations of Ronald Bradford from BGOUG Autumn 2010). I remember having problems with these applications, so I put a comment in the defaults file to not enable ONLY_FULL_GROUP_BY. I must admit I haven't checked recently, so the situation could have improved, but with Bugzilla's current state it's doubtful.

The default SQL modes changed much with the latest MySQL releases:

• MySQL 5.5: empty;
• MySQL 5.6: NO_ENGINE_SUBSTITUTION;
• MySQL 5.7: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION;
• MySQL 8.0: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, and NO_ENGINE_SUBSTITUTION.

The only difference in 8.0 is in fact the absence of  NO_AUTO_CREATE_USER, because it was removed after being deprecated in previous releases. It's highly recommended to stick to the defaults especially when starting new application, so you do not have to fix queries and data later.

When ONLY_FULL_GROUP_BY was enabled with MySQL 5.7.5 (released on 2014-09-25) Oracle took care to implement a sophisticated functional dependencies as Guilhem Bichot wrote in December 2014. The effort was praised by Markus Winand in his great article One Giant Leap For SQL: MySQL 8.0 Released from April 2018, who wrote that MySQL now has "the most complete functional dependencies checking among the major SQL databases". Let's explore this, but before it you may refer to Wikipedia for a quick review of the definition of functional dependency. To put it simply Y is functionally dependent on X if the values of X unique determine the values of Y. For example the column values in a table are functionally dependent of the primary key, because its value uniquely determines all other values in a row.

Take a look at the following example with the primary key. It's the query from above, but with a GROUP BY clause:

SELECT D.dname, COUNT(E.empno) cnt
  FROM dept D,
       emp  E
 WHERE D.deptno = E.deptno
 GROUP BY D.deptno;

Is this query valid? The grouping is by D.deptno, but the column list in SELECT consist only of D.dname and aggregation on E.empno, so it's wrong, right? Not in MySQL 5.7 and above with enabled ONLY_FULL_GROUP_BY, so the old rule to put everything from GROUP BY into SELECT or use aggregation doesn't apply (i.e. no more SQL-92 restrictions). The result is:

+------------+-----+
| dname      | cnt |
+------------+-----+
| ACCOUNTING |   3 |
| RESEARCH   |   5 |
| SALES      |   6 |
+------------+-----+

Why so? MySQL properly determines that D.dname is functionally dependent on D.deptno, because this is the primary key for dept table. Same query works properly also in PostgreSQL, but not in Oracle and other major SQL databases. MySQL could properly determine functional dependencies also on unique keys, equalities in the WHERE clause or [LEFT] JOIN ON clauses and from selected expressions in a view’s or derived table’s body. It's quite exciting, right? Be sure to use it next time when you consider writing a GROUP BY query.

For more details, please refer to chapter Detection of Functional Dependence in the manual.

Execution plan change in MySQL 8.0

About 6 weeks ago at work I had to write a new report query, because an application page was loading slowly (about 25 seconds). It turned out there is a problem in the application, because it was first executing a relatively slow query (taking about 6 seconds) and then a relatively fast query for each returned row from the first query. That second query was taking between 0.3 and 0.5 seconds per execution, but it was executed about 60 times, so the delay accumulated and explained the slow page load.

I decided to provide a single query that produces the whole result set, so I had to join 6 tables. One table (msg_progs) had just 13 rows, another two had just 64 rows (bldenv and prjprocstat) and another one just about 100 rows (p) and these four had no tendency of growing much more. There was one table with several tens of thousands of rows (m) and another with several hundreds thousands of rows (mtp), which for sure were going to grow on a daily basis. To improve the selectivity in the second largest table (m) I created a multi-column index on three columns involved in the query, but the problem was that sill much of its data was selected. Anyway, I ended up with a query that was executing for 6-7 seconds, which was much more acceptable from the initial 25 seconds. I'm not able to provide all the details, but I have captured the execution plan and I consider it safe to publish.

Remark: MySQL Workbench nicely visualizes the execution plans graphically (notice the lines connecting the different operations - the higher the number of rows the thicker the line). Textual execution plans are thing of the past, right?

That was until 3 weeks ago when we upgraded to MySQL 8.0.13 (from 5.7.20). The same query and in the same database started running for 24 seconds. When I checked the execution plan it has changed (see below).

I think you could easily notice that the join order has changed. Previously the second largest table was considered in the first nested loop, then the largest table and finally the smaller tables. Now it's the opposite as the optimizer first considers the smaller tables and only then the largest ones. I could have used a hint to fix the join order (e.g. JOIN_ORDER), but I noticed that if I re-create the multi-column index on just two of the columns (that are more selective in fact) the optimizer chooses a different plan, but the query execution time is still about 14 seconds. I finally decided to remove one of the tables in the joins (the smallest msg_progs with just 13 rows), which made the optimizer choose a different execution plan (here below) and the query now runs for about 4 seconds.

I'm not sure where this difference come from apart from the server, but I cannot blame without a proof (e.g. a reference in the manual that explains the difference), which unfortunately I haven't had the time to search. If you know the reason for the change of execution plans in MySQL 8.0 please, let me know.

The situation reminded me of Oracle database where after each major release we had queries with modified execution plans and had to use query hints or other tricks to make them fast again. It appears to be no different with MySQL nowadays.

Update 2018-12-17: I made a premature conclusion above, because the change of the plan happened (or was at least noticed) after we upgraded the server. However, as it turned out the real reason was the number of rows into tables m and mtp for which the server was choosing a different execution plan. We found it out experimenting with different in volume data sets.