Articles

Re-factoring some internals of prepared statements in 5.7

[ this is a re-posting of what I published on the  MySQL server team blog a few days ago ]   When the MySQL server receives a SELECT query, the query goes through several consecutive phases: parsing : SQL words are recognized, the query is split into different parts following the SQL grammar rules: a list of selected expressions, a list of tables to read, a WHERE condition, … resolution : the output of the parsing stage contains names of columns and names of tables. Resolution is about making sense out of this. For example, in “ WHERE foo=3 “, “foo” is a column name without a table name; by applying SQL name resolution rules, we discover the table who contains “foo” (it can be complicated if subqueries or outer joins are involved). optimization : finding the best way to read tables: the best order of tables, and for each table, the best way to access it (index lookup, index scan, …). The output is the so-called “plan”. execution : we read tables as dictated ...

WITH RECURSIVE and MySQL

[2017 update: MySQL 8.0.1 now features SQL-standard CTE syntax; more information is here ; the entry below, from 2013, shows how to work around the absence of CTEs in older MySQL versions.] If you have been using certain DBMSs, or reading recent versions of the SQL standard, you are probably aware of the so-called "WITH clause" of SQL. Some call it Subquery Factoring . Others call it  Common Table Expression (CTE). In its simplest form, this feature is a kind of "boosted derived table". Assume that a table T1 has three columns: CREATE TABLE T1( YEAR INT, # 2000, 2001, 2002 ... MONTH INT, # January, February, ... SALES INT # how much we sold on that month of that year ); Now I want to know the sales trend (increase/decrease), year after year: SELECT D1.YEAR, (CASE WHEN D1.S>D2.S THEN 'INCREASE' ELSE 'DECREASE' END) AS TREND FROM (SELECT YEAR, SUM(SALES) AS S FROM T1 GROUP BY YEAR) AS D1, (SELECT YEAR, SUM(SALES) AS S FROM T1 GR...

Fixing awkward TIMESTAMP behaviors...

There are great features in MySQL 5.6. But not only that. We also tried to correct some old behaviors and limitations which, over the years, have shown to irritate our Community. The behavior of TIMESTAMP columns is one of them. My colleague Martin Hansson did most of the work and summarized it well in his blog . Thanks to him, since MySQL 5.6.5, it's possible to declare more than one TIMESTAMP column with the DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP attributes. And it's possible to have DATETIME columns with such attributes. Two limitations lifted! But that is not the end of the story. TIMESTAMP was still special. Unlike other datatypes, if not declared with the NULL or NOT NULL attributes, it would automatically get NOT NULL. And the first TIMESTAMP column of the table would automatically get DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. Many people found these behaviors odd. For them, my colleague Gopal Shankar added a new option to the server, d...

Cost-based choice between subquery materialization and EXISTS

In a previous post , I had demonstrated how subquery materialization , introduced in MySQL 5.6.5, improves the performance of certain queries, like query Q16 of DBT3. Such improvement was easily explained: Subquery materialization has a high start up cost (it needs to create and fill the temporary table). But afterwards it has fast lookups (temporary table has a hash index, no duplicates, and is in memory). In other words, compared to EXISTS, the first evaluation of the IN predicate is slow (high start up cost) and all following evaluations are fast (just a hash lookup). In the DBT 3 setup, one outer table (named "part") has 200,000 rows, so there are 200,000 evaluations of IN, thus subquery materialization wins over EXISTS because the time it loses in the first evaluation is more than compensated by the many faster following evaluations. However, if there were only few outer rows, then subquery materialization should logically be slower than EXISTS (the compensation...

Faster subqueries with materialization

In a previous post , I analyzed how a query of the famous DBT3 benchmark was optimized by MySQL. It was this query, named "Q16" in the DBT3 jargon: select p_brand, p_type, p_size, count(distinct ps_suppkey) as supplier_cnt from partsupp, part where p_partkey = ps_partkey and p_brand <> 'Brand#23' and p_type not like 'LARGE PLATED%' and p_size in (43, 1, 25, 5, 35, 12, 42, 40) and ps_suppkey not in ( select s_suppkey from supplier where s_comment like '%Customer%Complaints%' ) group by p_brand, p_type, p_size order by supplier_cnt desc, p_brand, p_type, p_size; Here is a brief recap of conclusions I had drawn: for this query, MySQL tranforms the IN condition to EXISTS and then evaluates it with the "unique_subquery" technique, which does an index lookup into the subquery's table. IN is evaluated 120,000 times (once per combined row of the outer tables). The total execution time of query Q16...

Optimizer tracing used by others!

In a previous post, I had explained how to use MySQL's optimizer tracing , a new feature which appeared in MySQL 5.6.3. As a developer, it feels really good to see others adopt my work and make something useful out of it! My colleague Dimitri Kravtchuk, who is one of our top Benchmarking experts, has written a blog post where he shows how the optimizer tracing has helped him to figure out why, under load, once in a while and randomly, a query performed badly. His investigation technique may be reusable by other people, so I encourage you to read more about it, here .

Understanding the unique_subquery optimization

If you use the EXPLAIN SELECT statement to see how your subqueries are treated by MySQL, you may sometimes meet the "unique_subquery" optimization. Here is how the manual describes it: " unique_subquery : this type replaces ref for some IN subqueries of the following form: value IN (SELECT primary_key FROM single_table WHERE some_expr); unique_subquery is just an index lookup function that replaces the subquery completely for better efficiency". Few weeks ago, while I was reviewing a patch fixing a bug in unique_subquery, I got a "simplification" pulsion. I told myself that:  unique_subquery is an optimization for a special case of simple subqueries (single inner table, using index, no aggregates); we have a more general system around, used for more complex subqueries, naturally capable of handling simple ones too if we wanted; this general system does not have the bug in question... Then I wondered: what if we removed the unique_subquery optimizat...