Once again: I should lay off the SQL discussions on social media

LinkedIn is the worst for these SQL "hot takes." BTW regarding that phrase - "hot take" - I'm starting to find that annoying, even though I've used it myself before. It seems to be a way to attempt to say, "Hey I'm gonna be a bit of a jerk here and express an obnoxious opinion, but don't worry bro, I'm describing it as a 'hot take' so it's all good bro."

This one wouldn't bother me so much if they hadn't painted those who prefer the style that they dislike as "inexperienced." Look at the file names at the bottom of the post even: inexperienced.sql vs. experienced.sql. 🙄 It just feels condescending. And it's not an informed opinion either. It is feels based.

Below is my reply. I had to leave out some details and clarifications because I was fighting the LinkedIn character limit, but here goes:

Disagree, because I work with MySQL which processes WHERE bool less efficiently. It's not a universal problem-there's likely no discrepancy in most SQL engines, so fair enough if you prefer that style and it causes no issues for you. (SQL best practices should not be discussed universally IMO.)

In MySQL I see THIS take ~500 ms:

EXPLAIN ANALYZE
SELECT COUNT(*) FROM MY_TABLE WHERE IS_ACTIVE = TRUE;

-> Aggregate: count(0) (cost=456525 rows=1) (actual time=511..511 rows=1 loops=1)
-> Covering index lookup on MY_TABLE using IDX_IS_ACTIVE (IS_ACTIVE=true) <snip>

And THIS takes ~650 ms, because it results in a more complex search expression, and a more complex execution plan:

EXPLAIN ANALYZE
SELECT COUNT(*) FROM MY_TABLE WHERE IS_ACTIVE;

-> Aggregate: count(0) (cost=683424 rows=1) (actual time=657..657 rows=1 loops=1)
-> Filter: (0 <> MY_TABLE.IS_ACTIVE) <snip>
-> Covering index range scan on MY_TABLE using IDX_IS_ACTIVE over (NULL < IS_ACTIVE < 0) OR (0 < IS_ACTIVE) <snip>)

This behavior likely stems from MySQL's lack of a native boolean type - boolean is just an alias for tinyint - so in this example, IS_ACTIVE is a tinyint. Therefore if you do "WHERE <tinyint>" it says to check for any not null, non-zero value.

Guess I'm just inexperienced when I make MySQL queries run fairly significantly faster!

Ultimately, if you prefer one style and it works better for you and your SQL engine/DBMS, then great! But maybe others see something you don't - something measurable and tangible, not just feels based! So don't dismiss others as inexperienced before learning the other point of view. Not a good look.

MySQL automatically indexes materialized CTEs

Did you know -

When you use a CTE in MySQL, should the optimizer choose to materialize it, it may automatically create an index on the fly, if it thinks your query could benefit from it?

Let's dig a bit into this.

First a little context, in case anyone is not aware, the materialization approach to CTE processing is when MySQL chooses to execute the CTE query, and store its results in an internal temporary table, to be reused in the parent query. This tends to be a useful approach when the CTE is referenced multiple times.

The alternate approach is to merge the CTE into the parent query. I won't discuss the merge approach for now.

For example, say you ran the following query:

WITH data_cte AS (
    /* expensive query */ 
    SELECT group_id, AVG(x) AS avg_x, MAX(x) AS max_x FROM table_100m GROUP BY group_id
)
/* "parent" part of the query; notice multiple references to data_cte
SELECT group_id, avg_x FROM data_cte WHERE max_x = 100000 -- search by max_x
UNION ALL
SELECT group_id, avg_x FROM data_cte WHERE max_x = 99999
UNION ALL
SELECT group_id, avg_x FROM data_cte WHERE max_x = 99998
;

For this query, MySQL's optimizer may be able to materialize the CTE. In addition to that, because the parent query searches by max_x three times, the optimizer may decide, "hey, I'd better create an index on max_x in the materialization."

The execution plan shows that:

1. The CTE called data_cte was indeed materialized

...and...

2. For all three lookups by max_x in the parent query, we can see in the plan an index lookup occurred. e.g. Index lookup on avg_data_cte using <auto_key0> (max_x = 99998). MySQL's optimizer automatically created an index to assist in these searches!

Execution plan:

 | -> Append  (cost=10.5 rows=30) (actual time=16912..16912 rows=2115 loops=1)
    -> Stream results  (cost=3.5 rows=10) (actual time=16912..16912 rows=419 loops=1)
        -> Filter: (data_cte.max_x = 100000)  (cost=3.5 rows=10) (actual time=16912..16912 rows=419 loops=1)
            -> Index lookup on data_cte using <auto_key0> (max_x = 100000)  (cost=0.35..3.5 rows=10) (actual time=16912..16912 rows=419 loops=1)
                -> Materialize CTE data_cte if needed  (cost=0..0 rows=0) (actual time=16912..16912 rows=10000 loops=1)
                    -> Table scan on <temporary>  (actual time=16909..16909 rows=10000 loops=1)
                        -> Aggregate using temporary table  (actual time=16909..16909 rows=10000 loops=1)
                            -> Table scan on table_100m  (cost=10.2e+6 rows=99.8e+6) (actual time=2.2..8625 rows=100e+6 loops=1)
    -> Stream results  (cost=3.5 rows=10) (actual time=0.0248..0.117 rows=786 loops=1)
        -> Filter: (data_cte.max_x = 99999)  (cost=3.5 rows=10) (actual time=0.014..0.0798 rows=786 loops=1)
            -> Index lookup on data_cte using <auto_key0> (max_x = 99999)  (cost=0.35..3.5 rows=10) (actual time=0.0129..0.0546 rows=786 loops=1)
                -> Materialize CTE data_cte if needed (query plan printed elsewhere)  (cost=0..0 rows=0) (never executed)
    -> Stream results  (cost=3.5 rows=10) (actual time=0.00196..0.108 rows=910 loops=1)
        -> Filter: (data_cte.max_x = 99998)  (cost=3.5 rows=10) (actual time=0.00183..0.0774 rows=910 loops=1)
            -> Index lookup on data_cte using <auto_key0> (max_x = 99998)  (cost=0.35..3.5 rows=10) (actual time=0.00158..0.0493 rows=910 loops=1)
                -> Materialize CTE data_cte if needed (query plan printed elsewhere)  (cost=0..0 rows=0) (never executed)

Notice the three instances of Index lookup on data_cte using <auto_key0> - that's the automagic index goodness in action.

There's some meta-context here. There's a recurring debate in internet SQL discussions about what is better: CTEs or temp tables. In my view, the two apply to only partially overlapping use cases, but the idea is you could rewrite the above query by splitting it into two statements, the first of which substitutes a temp table for the CTE, like so:

CREATE TEMPORARY TABLE data_tmp AS 
    SELECT group_id, AVG(x) AS avg_x, MAX(x) AS max_x FROM table_100m GROUP BY group_id;

Then query the temp table, like so:

SELECT group_id, avg_x FROM data_tmp WHERE max_x = 100000 -- search by max_x
UNION ALL
SELECT group_id, avg_x FROM data_tmp WHERE max_x = 99999
UNION ALL
SELECT group_id, avg_x FROM data_tmp WHERE max_x = 99998
;

Unfortunately, MySQL presents a blocker to this query: an unfortunate limitation that prevents me from even testing this approach because you can't reference a temporary table multiple times in one query. 

ERROR 1137 (HY000): Can't reopen table: 'data_tmp'

But I could instead try creating a "normal" table that is only used temporarily, to serve this query, then drop it after.

Below is the execution plan from this approach. Observe that it did NOT use an index - instead it ran Table scan on data_tbl three times - because we didn't explicitly create an index:

| -> Append  (cost=3011 rows=2989) (actual time=0.0747..15.2 rows=2115 loops=1)
    -> Stream results  (cost=1004 rows=996) (actual time=0.0734..6.31 rows=419 loops=1)
        -> Filter: (data_tbl.max_x = 100000)  (cost=1004 rows=996) (actual time=0.0675..6.21 rows=419 loops=1)
            -> Table scan on data_tbl  (cost=1004 rows=9963) (actual time=0.0488..5.69 rows=10000 loops=1)
    -> Stream results  (cost=1004 rows=996) (actual time=0.0383..4.23 rows=786 loops=1)
        -> Filter: (data_tbl.max_x = 99999)  (cost=1004 rows=996) (actual time=0.0304..4.15 rows=786 loops=1)
            -> Table scan on data_tbl  (cost=1004 rows=9963) (actual time=0.0219..3.74 rows=10000 loops=1)
    -> Stream results  (cost=1004 rows=996) (actual time=0.025..4.56 rows=910 loops=1)
        -> Filter: (data_tbl.max_x = 99998)  (cost=1004 rows=996) (actual time=0.021..4.47 rows=910 loops=1)
            -> Table scan on data_tbl  (cost=1004 rows=9963) (actual time=0.0122..3.99 rows=10000 loops=1)

But we could explicitly create an index. After doing so, and rerunning the query, we see a similar execution plan to the search portion of the CTE query's plan:

| -> Append  (cost=277 rows=2115) (actual time=0.0567..3.73 rows=2115 loops=1)
    -> Stream results  (cost=63.7 rows=419) (actual time=0.056..0.928 rows=419 loops=1)
        -> Index lookup on data_tbl using my_idx (max_x = 100000), with index condition: (data_tbl.max_x = 100000)  (cost=63.7 rows=419) (actual time=0.0504..0.88 rows=419 loops=1)
    -> Stream results  (cost=100 rows=786) (actual time=0.017..1.27 rows=786 loops=1)
        -> Index lookup on data_tbl using my_idx (max_x = 99999), with index condition: (data_tbl.max_x = 99999)  (cost=100 rows=786) (actual time=0.0151..1.19 rows=786 loops=1)
    -> Stream results  (cost=113 rows=910) (actual time=0.0143..1.42 rows=910 loops=1)
        -> Index lookup on data_tbl using my_idx (max_x = 99998), with index condition: (data_tbl.max_x = 99998)  (cost=113 rows=910) (actual time=0.012..1.32 rows=910 loops=1)

When this debate arises on the r/sql subreddit, I see temp tables getting all the upvotes over CTEs. I'm not always in agreement with the reasoning. Probably the single biggest fallacy I notice is they discuss how the CTEs vs. temp tables are processed under the hood, without specifying a DBMS. But you've heard my rants on that subject about 100 times.

One of the reasons sometimes offered is that you can index a temp table, but (supposedly) can't do that with a CTE. As we've seen just now, this is not true in MySQL. In fact one could argue the CTE is more automagic and therefore preferable. 

Also consider that merging may be the better approach than materialization, which is also part of the optimizer's automagic! If you create a temp table, that's forcing a materialization, whether it's better or not. If the MySQL optimizer is doing it's job right - which it usually does - we should let it do its thing and make the right decision. To be fair, it's from from perfect.

I'd also wager that at least 80% of developers wouldn't think to make the explicit index on their temp table.

Automagic ftw, in my opinion. Unless the automagic fails, then take other measures.

Here's one example of a Reddit comment on the debate. This is just a fundamental misunderstanding. (I'd feel better about it if it didn't get piles of upvotes.)

 

A classic r/sql error: sweeping statements about SQL engine behavior that are 1) not correct (at least not always, and far from always), 2) not qualified with a specific DBMS, and 3) somehow upvoted heavily

Contradicting the Reddit commenter, and their 26 upvoters, we've observed already that the CTE materialization MySQL can perform means the CTE query is NOT executed once per reference While it's possible they are correct about *some* DBMS out there (and perhaps in some querying scenarios but not all), every one of the several other DBMSs for which I've tested this subject is also capable of materializing CTEs.

It used to be the case in Postgres that CTEs were always materialized! That isn't the best approach 100% of the time.

Funny thing is, in MySQL, the materialized CTE literally becomes a temp table under the hood! So it almost turns into an argument of, "My temp table is better than your temp table!" You just don't have to manage it yourself using the CTE approach, and again, MySQL might choose the merge approach which may run better. Automagic ftw!

But I may be digressing a bit. I'll cut the discussion here unless anyone has any questions/comments. 

Division in MySQL vs. Postgres: SQL is SQL so it's all interchangeable am-I-right?

Did you know -

The integer division operator in MySQL is DIV? The operator we all know and love, / , will always produce a DECIMAL as its output. so if you want it to yield an integer, use DIV:

mysql> CREATE TABLE T AS SELECT 4 DIV 3 AS DUMMY1, 4/3 AS DUMMY2, 4/4 AS DUMMY3;
Query OK, 1 row affected, 1 warning (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> SELECT * FROM T; /* observe the results */
+--------+--------+--------+
| DUMMY1 | DUMMY2 | DUMMY3 |
+--------+--------+--------+
|      1 | 1.3333 | 1.0000 |
+--------+--------+--------+
1 row in set (0.01 sec)

mysql> DESC T; /* observe the data types each kind of division yielded */
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| DUMMY1 | int          | YES  |     | NULL    |       |
| DUMMY2 | decimal(5,4) | YES  |     | NULL    |       |
| DUMMY3 | decimal(5,4) | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

How about other DBMSs? Well in Postgres, there is no DIV but there is of course /- and basically the way it works is if you divide integers, it will produce an integer. But say you divide floats or numeric types, then it will produce floats or numeric types.

postgres=# CREATE TABLE T AS SELECT 4/3 AS DUMMY1, 4::float/3::float AS DUMMY2, 4.0/3.0 AS DUMMY3, 4/4 AS DUMMY4;
SELECT 1

postgres=# SELECT * FROM T;
 dummy1 |       dummy2       |       dummy3       | dummy4
--------+--------------------+--------------------+--------
      1 | 1.3333333333333333 | 1.3333333333333333 |      1
(1 row)

postgres=# \d T
                      Table "public.t"
 Column |       Type       | Collation | Nullable | Default
--------+------------------+-----------+----------+---------
 dummy1 | integer          |           |          |
 dummy2 | double precision |           |          |
 dummy3 | numeric          |           |          |
 dummy4 | integer          |           |          |
 

This is something like exhibit #5001 as to why, when the topic of database interchangeability comes up - because “SQL is SQL” - I think people tend to underestimate the huge number of “gotchas” that can be encountered. 

SQL here != SQL there. 

The LENGTH() function - or - SQL is SQL so it's all interchangeable am-I-right?

Let's test the LENGTH() vs. CHAR_LENGTH() function on some emoji characters in MySQL and Postgres: 

mysql> SELECT x FROM emoji;
+--------------+
| x |
+--------------+
| 😄👋👍 |
+--------------+
1 row in set (0.02 sec) 

mysql> SELECT LENGTH(x) AS len, CHAR_LENGTH(x) AS char_len FROM emoji; +------+----------+ | len | char_len | +------+----------+ | 12 | 3 | +------+----------+ 1 row in set (0.02 sec) 

---

postgres=# SELECT x FROM emoji;
x
--------
😄👋👍
(1 row) 

postgres=# SELECT LENGTH(x) AS len, CHAR_LENGTH(x) AS char_len FROM emoji; len | char_len -----+---------- 3 | 3 (1 row) 

 

Expanded thoughts on the subject:

LENGTH() is the most common function we tend to reach for when we want the length of a string - typically we want number of characters and not bytes, at least in my experience. But LENGTH() gets number of bytes in MySQL, and on the other hand, number of characters in Postgres.

How could they be so different?!? Here's the thing: LENGTH() is not part of standard SQL, if that even matters since implementations deviate from it so much.

At least, I looked into the few documents freely available, and there IS no LENGTH() function in standard SQL. Rather, there are: CHARACTER_LENGTH() (aka CHAR_LENGTH()), OCTET_LENGTH(), and BIT_LENGTH()

If you test ONLY these standard functions (or "length expressions" as standard SQL puts it), you find the results do match between MySQL and Postgres:

mysql> SELECT CHAR_LENGTH(x), CHARACTER_LENGTH(x), BIT_LENGTH(x), OCTET_LENGTH(x) FROM emoji;
+----------------+---------------------+---------------+-----------------+
| CHAR_LENGTH(x) | CHARACTER_LENGTH(x) | BIT_LENGTH(x) | OCTET_LENGTH(x) |
+----------------+---------------------+---------------+-----------------+
|              3 |                   3 |            96 |              12 |
+----------------+---------------------+---------------+-----------------+
1 row in set (0.02 sec)

and:

postgres=# SELECT CHAR_LENGTH(x), CHARACTER_LENGTH(x), BIT_LENGTH(x), OCTET_LENGTH(x) FROM emoji;
 char_length | character_length | bit_length | octet_length
-------------+------------------+------------+--------------
           3 |                3 |         96 |           12
(1 row)

More fun facts:

Microsoft loves using its own special Microsofty style functions, for Microsofty consistency purposes, so it has no LENGTH(), only LEN(), just like the Len() function you might have used in Excel, or in Visual Basic in 1995. (See also DateDiff() and others.) 

• Oracle has LENGTH() (number of chars in the string), and LENGTHB() (number of bytes in the string)
• In Snowflake and Databricks/Spark SQL, LENGTH() returns number of chars.
• So, it appears that MySQL is the odd one out.

This can be a particularly hairy gotcha if you are porting SQL queries from MySQL to another DBMS or vice versa. It may elude testing even, since most of our strings, generally speaking, tend not to include "special" characters such as Asian characters or emoji. (I've only worked for US-based companies, so I may be biased.) But they do crop up on occasion. So those occasions may potentially break things. 






 

 

Playing SQL Mythbuster - or - I really ought to lay off the SQL on social media

Often, when I visit SQL-related forums and social media, I encounter posts that are half-truths, overgeneralizations, and sometimes, outright myths. This is especially true on the subject of SQL performance. 

The problem is I can't just scroll past. I have this compulsion to play SQL Mythbuster. If the posts had, say, 2 likes and that's it, I'd probably move on. But they often have hundreds of positive reactions and comments in response to some dubious, unsupported, or at least badly qualified claims. 

 

For example, here's a recent LinkedIn post:

Here's why I'm not a fan of this post (no offense to the author):

1. The proposed optimization is a magic performance trick. It is essentially saying, "Hey, instead of writing this straightforward query, you can rearrange it in this magic way to make it go fast!" The post doesn't attempt to justify the claim other than by unconvincingly stating that the more straightforward query (hereafter called Query 1) causes "poor index utilization," while the supposedly optimized query (hereafter called Query 2) presumably does not.
   
   
2. The post doesn't even state which of the 100+ DBMSs* in the world he's running, each of which has unique performance characteristics. Always be skeptical of claims resembling, "In SQL, always do this thing to make it fast," without qualifying the claim with a specific DBMS. It is like saying, "In vehicles with an internal combustion engine, always do this to make them run fast." For starters, what kind of vehicle: car? truck? plane?
   
   It is worth mentioning, despite my skepticism, that it's quite possible this trick did work for some DBMS. But which one? Tell us! My intuition tells me there's no way the claim is universally true, but it could be accurate for some specific version of some specific DBMS (in a specific schema, specific data set, specific configuration, etc.) The problem I have is that it's presented as if the trick applies to all of "SQL." (Whatever that is; SQL doesn't exist in a vacuum, remember!) To be fair, it didn't explicitly say "all of SQL," but IMHO, it is implied.
   
   Also worth mentioning, a commenter asked which DBMS, but the original poster didn't reply.
   
   *I am using "DBMS" as a generic, catch-all term of choice to refer to a relational DBMS or any other SQL engine
   
   
3. There is no evidence presented to convince the reader that Query 2 is faster: no timing data; no execution plans. There is no demonstration of the "poor index utilization" of Query 1. The poster just said it's real and left it at that. 🤷 One commenter did ask to see some evidence, and the poster pushed back. 🤷🤷
   
   
4. Although we can make some reasonable assumptions - e.g. id is probably the sole primary key column, and it's an integer type - nothing is mentioned about the table definition, number of rows, width of the rows, the table's physical structure (i.e. is it a clustered index?) Does it have indexes? And so on.
    
5.  The final, and perhaps most important, reason I'm not a fan of the post is this:
   
   Great (/s) - so 926 reactions, most of which are positive. In other words, a lot of folks accepting this performance trick as factual. Also notice the 51 reposts. Way to spread yet another new myth. 🤦🏻‍♂️
   

That said, is it actually a myth? Could it be possible that despite being not the best-written post, Query 2 does actually run faster than Query 1 on some DBMSs?

When in doubt, test it out!

But here's the thing - I can't test it on all 100+ SQL DBMSs in the world. Who can? (Maybe somebody with a really cool test suite set up?) What I can do is test it on a recent version of an extremely popular DBMS.

Below is a test case on MySQL 8.0, using reasonable assumptions, that demonstrates Query 2 is not faster, so it disproves the claim...at least for my specific test in MySQL 8.0. The test below, of course, does not prove anything about Oracle or Snowflake, or Microsoft or any other DBMS.

But all it really takes to disprove a universal claim is a single counterexample, and MySQL 8.0 isn't exactly an obscure DBMS. So I consider the claim to be a myth. ("BUSTED," if you want. :)) 

I should put my money where my mouth is and show my tests. So, here they are! 

MySQL 8.0 Tests: 

mysql> /* setup */
mysql> CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), salary INT);
Query OK, 0 rows affected (0.01 sec)

mysql> /* generate 10 million dummy rows */
mysql> INSERT INTO users (name, salary)
    -> SELECT RAND(), RAND()*10000 FROM information_schema.columns c1, information_schema.columns c2, information_schema.columns c3
    -> LIMIT 10000000;
Query OK, 10000000 rows affected (1 min 39.74 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

For the purpose of this demonstration, I am searching by 10,000 IDs, from a 10 million row table. If I search by a mere five IDs, both queries execute so fast that the MySQL CLI (my client) reports both as running in 0.00 seconds flat. So I needed to scale up the search to the point at which there can be a measurable difference.

Query 1:

mysql> EXPLAIN ANALYZE
    -> SELECT * FROM users WHERE id IN (9462823,6458594,3326348, <snip>); 

Query 1 Average Time to Execute (6 trials): 122 ms 

Show details

• Trial 1: 200 ms
• Trial 2: 160 ms
• Trial 3: 140 ms
• Trial 4: 80 ms
• Trial 5: 90 ms
• Trial 6: 60 ms
(MySQL cli used, which rounds to hundredths of a second)

 

Query 1 Sample Execution Plan:

-> Filter: (users.id in (9462823,6458594,3326348,<snip>))  (cost=10122 rows=9994) (actual time=0.0575..43.9 rows=10000 loops=1)
    -> Index range scan on users using PRIMARY over (id = 741) OR (id = 1245) OR (9998 more)  (cost=4502 rows=10000) (actual time=0.0525..41.9 rows=10000 loops=1)

 
Note MySQL used an index range scan on users using PRIMARY. that means it searched essentially for a whole batch of the id values using the primary index in one scan, which you can't really hope to do much better than. Absolutely not "poor index utilization."

Query 2:

mysql> WITH ids(id) AS (
    ->     /* should use row constructors IMO, but this is how OOP did it */
    ->     SELECT 9462823 UNION ALL
    ->     SELECT 6458594 UNION ALL
    ->     SELECT 3326348 UNION ALL
    ->     <snip>
    -> )
    -> SELECT u.*
    -> FROM users u
    -> JOIN ids ON u.id = ids.id;

Query 2 Average Time to Execute (6 trials run): 197 ms 

Show details

• Trial 1: 240 ms
• Trial 2: 230 ms
• Trial 3: 220 ms
• Trial 4: 200 ms
• Trial 5: 150 ms
• Trial 6: 140 ms
(MySQL cli used, which rounds to hundredths of a second)

 

Query 2 Sample Execution Plan:

-> Nested loop inner join  (cost=11516 rows=10000) (actual time=7.88..70.8 rows=9955 loops=1)
    -> Table scan on ids  (cost=2304..2432 rows=10000) (actual time=7.71..8.19 rows=10000 loops=1)
        -> Materialize union CTE ids  (cost=2304..2304 rows=10000) (actual time=7.71..7.71 rows=10000 loops=1)
            -> Rows fetched before execution  (cost=0..0 rows=1) (actual time=166e-6..208e-6 rows=1 loops=1)
            -> Rows fetched before execution  (cost=0..0 rows=1) (actual time=167e-6..209e-6 rows=1 loops=1)
            -> Rows fetched before execution  (cost=0..0 rows=1) (actual time=42e-6..84e-6 rows=1 loops=1)
            <snip>
(cost=0..0 rows=1) (actual time=0..41e-6 rows=1 loops=1)
    -> Filter: (u.id = ids.id)  (cost=0.25 rows=1) (actual time=0.00218..0.00226 rows=1 loops=10000)
        -> Single-row index lookup on u using PRIMARY (id = ids.id)  (cost=0.25 rows=1) (actual time=0.00207..0.0021 rows=1 loops=10000)

What this plan tells us in plain English:

1. Materialize the CTE ids (basically think like making a temp table) based on all those UNION ALL'ed constants (each of those tiny UNION ALL statements is represented by "Rows fetched before execution")
2. Table scan ids, and for each id, do a single-row index lookup on the primary index. Notice this is done in a Nested loop inner join. Essentially: for each id in ids, do a single-row index lookup on primary index.

Conclusion: 

All in all, Query 2 didn't perform badly (avg 197 ms), but definitely worse than Query 1 (avg 122 ms), a relative difference of about 38%, which is pretty significant. (I actually wonder if I replaced all those UNION ALLs in Query 2 with row constructors, could that help the performance? But I'm not going to explore that today.)

Aside from performance claims, the post also claims Query 2 provides:

• "single query execution": sure it's one query, but so is Query 1. Huh???
• Readability: subjective, but I'm not seeing it in this instance, and I say this as a CTE fanboy. (Query 2 uses a CTE, i.e. the WITH clause.)
• Portability: Either of these queries should successfully parse and execute on pretty much any modern SQL engine, but if you came up to me and said "I know a SQL DBMS that can only handle one of these two queries -- which is it? Dollars to doughnuts it would be Query 1. (I think of how MySQL didn't even support CTEs until MySQL 8!)

OK, end of rant for today! But yeah, sometimes I need to stay away from SQL chatter on the internet. There's all kinds of this stuff that drives me crazy. 

Edit Weeks Later: Now Let's Test PostgreSQL 17.4! 

I thought I might enhance this post by repeating the same MySQL tests, but on Postgres 17.4 instead! See below.

 

postgres=# CREATE TABLE users (id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name VARCHAR(100), salary INT);
CREATE TABLE

postgres=# INSERT INTO users (name, salary)
postgres-# SELECT RANDOM(), RANDOM()*10000
postgres-# FROM generate_series(1, 10000000);
INSERT 0 10000000
Time: 38705.863 ms (00:38.706)

 

Query 1, Postgres:

postgres=# EXPLAIN ANALYZE
postgres-# SELECT * FROM users WHERE id IN (1322614, 8993900, 6548546, <snip>);

 

Query 1, Postgres, Average Time to Execute (6 trials): 103 ms 

Show details

• Trial 1: 11.5 ms (planning) + 99.0 ms (execution) = 110.5 ms
• Trial 2: 7.9 ms (planning) + 156.6 ms (execution) = 164.5 ms
• Trial 3: 8.6 ms (planning) 60.7 ms (execution) = 69.3 ms
• Trial 4: 11.1 ms (planning) 92.5 ms (execution) = 103.6 ms
• Trial 5: 6.4 (planning) 70.4 ms (execution) = 76.8 ms
• Trial 6: 11.6 (planning) 88.1 ms (execution) = 99.7 ms

Query 1, Postgres, Sample Execution Plan:

 Index Scan using users_pkey on users  (cost=0.43..35565.89 rows=10000 width=27) (actual time=3.200..88.645 rows=10000 loops=1)
   Index Cond: (id = ANY ('{1322614,8993900,6548546,3547394,9242769,4281742,4334656, <snip>}'::integer[]))

Query 2, Postgres:

postgres=# EXPLAIN ANALYZE
postgres-# WITH ids(id) AS (
postgres(#     SELECT 1322614 UNION ALL
postgres(#     SELECT 8993900 UNION ALL
postgres(#     SELECT 6548546 UNION ALL
postgres(# <snip>
postgres(# )
postgres-# SELECT u.*
postgres-# FROM users u
postgres-# JOIN ids ON u.id = ids.id;

Query 2, Postgres, Average Time to Execute  (6 trials): 415.5 ms 

Show details

• Trial 1: 170.6 ms (planning) + 244.0 ms (execution) = 414.6 ms
• Trial 2: 180.9 ms (planning) + 284.4 ms (execution) = 465.3 ms
• Trial 3: 169.9 ms (planning) + 220.1 ms (execution) = 389.1 ms
• Trial 4: 168.5 ms (planning) + 214.2 ms (execution) = 382.7 ms
• Trial 5: 176.6 ms (planning) + 268.1 ms (execution) = 444.7 ms
• Trial 6: 174.8 ms (planning) + 219.3 ms (execution) = 394.1 ms

Query 2, Postgres, Sample Execution Plan:

 Gather  (cost=1000.43..2057.59 rows=10000 width=27) (actual time=25.238..216.174 rows=10000 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Nested Loop  (cost=0.43..57.59 rows=4167 width=27) (actual time=0.052..87.485 rows=3333 loops=3)
         ->  Parallel Append  (cost=0.00..50.00 rows=1 width=4) (actual time=0.002..3.282 rows=3333 loops=3)
               ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
               ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)
               ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
               <snip>
         ->  Index Scan using users_pkey on users u  (cost=0.43..7.58 rows=1 width=27) (actual time=0.025..0.025 rows=1 loops=10000)
               Index Cond: (id = (1322614))


⚠️ On Postgres, my first attempt at running Query 2 failed because:

ERROR:  stack depth limit exceeded
HINT:  Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.

This is an issue when stringing all those SELECTs of literals separated by UNION ALLs, which nobody should be doing in 2025 unless your DBMS of choice does not support row constructors (Postgres does). My workaround for the purposes of running these tests was to add this to the top of my script:

set max_stack_depth = '7664kB';

 

7664kB is the max allowed by Postgres, and it isn't that much larger than the default of 2048kB. I got lucky in this particular use case, but if that's the ceiling, the Query 2 approach appears to be much less scalable on Postgres.

 

Conclusion for Postgres: 

So in summary, this magic performance trick was also BUSTED on Postgres 17.4. Query 2 is actually much slower than Query 1 for this given test; it took almost exactly 4 times longer to run!

 

Notice the Postgres planner tried to use parallel processing for Query 2, which my Postgres instance is configured to cap at 2 workers at the moment. It's possible that if I tuned parallel query settings, it could do better than presented here. It's not impossible that it could beat Query 1 even, but I have to go with the results I have here. This is why "for this particular configuration" is important to qualify your tests with, in addition to DBMS/version/etc.

 

It may even be worth mentioning all the tests in this post were run on my 2021 M1 Macbook Pro. The results could vary if I ran it on another machine, or on some cloud database service such as Amazon RDS.

 

 

Overall Conclusion: 

The major point I'm trying to make here is not "they are wrong and I am right," but rather, it is important to communicate about SQL performance more carefully! Always, with respect to performance quirks, qualify a post with your DBMS of choice. Ideally, talk about version and more detail about the schema and data as well. 

If some performance trick - say approach A runs faster than approach B - on your specific database running on, say, Oracle 19c, your experience is valid. But absolutely do not take away from your experience a vast overgeneralization such as: "In SQL, you should always do A because it's faster than B." 

 

And: always show the proof that one approach was slower or faster than the other! Show the respective timings, and ideally, the respective execution plans. Don't just say it's true. Convince us with evidence!

Always be skeptical of these magic performance tricks you see on the internet. If they can't even specify a DBMS, do not believe them! At least not by default. Make them put up or shut up. :) If they present the full test case and it supports their claim, great! But even then, the test shouldn't be generalized to all of "SQL." Rather, the test demonstrates something about a specific data set in a specific schema on a specific version of a specific DBMS product with a specific configuration, etc.

 

Question to the readers: what do you guys think? Should I test the claim on more DBMSs? Am I being fair? Do the tests seem valid? Let me know what you think in the comments!

 

 

MySQL metadata locks on child tables in foreign key relationships, and how Postgres compares

Probably the single biggest misconception I hear in SQL discussions is that there's this monolithic thing called the "RDBMS," or perhaps "SQL Database," and that they all work exactly (or "pretty much exactly") the same.

The logical chain usually goes something like:

MySQL-specific problem occurs => The problem is incorrectly generalized to all of "RDBMS" or all of "SQL" => "RDBMS"/"SQL" is bad and we should throw it out"

My motto is "SQL here != SQL there." (My other one is "When in doubt, test it out.")

Let's talk about this particular MySQL issue. (Assume the InnoDB storage engine whenever I talk about MySQL.) The issue is, in plain-if-not-technically-perfect language, that if a table, let's call it parent, has so much as a single row modified, all child tables (i.e. tables with a foreign key referencing parent) are blocked from running any DDL. 

This is because MySQL, when you update parent, acquires a shared metadata lock on all child tables. Observe a test between two concurrent sessions in MySQL:

MySQL Session 1:  do an update on one single row in parent, and don't commit (not commiting just makes the timing issue easier to recreate for demo purposes):

mysql> BEGIN;
mysql> UPDATE parent SET x=999 WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0  

MySQL Session 2 (I'm switching to another terminal tab that has a separate connection to the database):

mysql> ALTER TABLE child ADD COLUMN new_col INT;

This statement hangs. This is because the ALTER TABLE statement needs to acquire an exclusive metadata lock on  child. Exclusive means just that -- exclusive. As long as a shared metadata lock is held on a table, an exclusive metadata lock cannot be acquired. On the flip side -- as long as an exclusive metadata lock is held on a table, a shared metadata lock cannot be acquired.

Perhaps a diagram can explain better than words:

 

 

Let's take a brief look at the locking situation in yet another session:

mysql> SELECT * FROM performance_schema.metadata_locks
    -> WHERE LOCK_STATUS = 'PENDING';
+-------------+---------------------+-------------+-------------+-----------------------+-----------+---------------+-------------+-------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA       | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE      | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+---------------------+-------------+-------------+-----------------------+-----------+---------------+-------------+-------------+-----------------+----------------+
| TABLE       | my_schema | child       | NULL        |       281469012339360 | EXCLUSIVE | TRANSACTION   | PENDING     | mdl.cc:3753 |           17282 |            893 |
+-------------+---------------------+-------------+-------------+-----------------------+-----------+---------------+-------------+-------------+-----------------+----------------+
1 row in set (0.00 sec)

The above result confirms that a metadata lock is pending.

When I finally issue a COMMIT or ROLLBACK in Session 1, Session 2 will complete. But think about what this means -- if you try to run DDL on a table in pretty much any way, and it has a foreign key reference to a table that is heavily updated, it may be a while. Furthermore, considering that the exclusive metadata lock blocks shared metadata locks, then when the DDL on child is finally running, all updates on the parent are blocked!

So does this mean you should always throw foreign keys in the garbage when using any RDBMS, as I sometimes hear? Not if you're thinking about doing so for this particularly reason. That's because not every DBMS locks in this way!

Let's repeat the test on Postgres.

Postgres Session 1:

postgres=# BEGIN;
BEGIN
postgres=*# UPDATE parent SET x = 999 WHERE id = 1;
UPDATE 1
postgres=*#

Postgres Session 2:

postgres=# ALTER TABLE child ADD COLUMN new_col INT; -- it completes instantly, no blocking involved
ALTER TABLE
postgres=# \d child -- look, the column was added
                 Table "public.child"
  Column   |  Type   | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
 id        | integer |           | not null |
 parent_id | integer |           |          |
 new_col   | integer |           |          |
Indexes:
    "child_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "child_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES parent(id)

postgres=*# update child set parent_id=null; -- I can even update the child row that is referencing the parent row that is being updated; completes instantly and doesn't hang
UPDATE 1
postgres=*# insert into child (id, parent_id) values (2, 1); -- insert a new child row refencing the parent row being updated; also doesn't hang
INSERT 0 1
postgres=*# create index idx on child(new_col); -- maybe i'll index the new column while i'm at it
CREATE INDEX

So clearly, Postgres does not have this same, particular locking issue as MySQL does. You can go hog wild running DDL on any child table no matter how much the parent is being updated. And no data or table definition will be corrupted or otherwise "bad." 

SQL here != SQL there!

The Benefits of Using Row Constructors! [MySQL]

 

In the world of SQL, sometimes you need to conjure up rows of literal data out of thin air, instead of pulling data from an existing table. The context of this post is when you, the SQL coder, need to define each row of literals individually. Maybe you're populating a table for testing purposes - the precise use case isn't too important. Note I am not referring to the context of generating a series by calling a function (post about how to do that), or otherwise programmatically generating a number of random rows out of thin air. Rather, you want to list the rows out one by one.

That foundation laid out, my claim is that I've generally found it is better to use your DBMS of choice's row constructor syntax than what I've more commonly seen: a bunch of SELECTs containing only literals that are UNION ALL'ed together.  For example:

SELECT 1,2,3
UNION ALL
SELECT 4,5,6
UNION ALL
SELECT 7,8,9
...

Rather than writing the above syntax,  there are benefits to taking a row constructor approach, which looks like the following in MySQL.

VALUES
ROW(1,2,3),
ROW(4,5,6),
ROW(7,8,9),
...

Let me clarify that last bit: this post is specifically about MySQL and the above syntax is MySQL specific. No specific tests or under-the-hood workings demonstrated below should be generalized beyond the scope of MySQL. That said, I've found the principle does tend to apply elsewhere, but I will not try to support my claim for any other DBMS than MySQL in this post. 

Without further ado...

Why use row constructors over a bunch of UNION ALL'ed SELECTs?

1) Performance

The UNION ALL approach takes greater wall clock time to execute. I quickly ran a not-super-scientific benchmark (MySQL 8.0.x, 2024 Macbook Pro, usual YMMV disclaimer applies) of the two approaches on 10,000 rows (the same exact data is constructed in each) and the difference was night and day:

Generating 10000 constant rows...
Generating UNION ALL SQL in union.sql...
Generating VALUES ROW(...) SQL in values.sql...

=== Running UNION ALL ===
real 3.95
user 0.04
sys 0.01

=== Running VALUES ROW ===
real 0.08
user 0.03
sys 0.00

Times were about consistent with repeated trials. The discrepancy would likely be less pronounced with smaller data sets. And of course, if you're cooking up a tiny number of rows, say 5, I doubt you'll notice any tangible difference.