This is the third in a series on what’s seriously limiting MySQL in certain circumstances (links: part 1, 2). This post is about subqueries, which in some cases execute outside-in instead of inside-out as users expect.

It’s easy to pick on subqueries in MySQL, so I’ll try to be gentle. The following query will surprise users unpleasantly:

select * from a where a.id in (select id from b);

Users expect the inner query to execute first, then the results to be substituted into the IN() list. But what happens instead is usually a full scan or index scan of table a, followed by N queries to table b. This is because MySQL rewrites the query to make the …

A few weeks ago, we had a query optimization request from one of our customer.

The query was very simple like:

PLAIN TEXT CODE:

1. SELECT * FROM `table` WHERE (col1='A'||col1='B') ORDER BY id DESC LIMIT 20 OFFSET 0

This column in the table is looks like this:

PLAIN TEXT CODE:

1. `col1` enum('A','B','C','CD','DE','F','G','HI') default NULL

The table have 549252 rows and of course, there is an index on the col1. MySQL estimated the cardinality of that index as 87, though what was of course misleading as index cardinality in this case can't be over 9, as there is only 8(+ NULL) different possible values for this column.

PLAIN TEXT CODE:

1. …

Last month at the Boston MySQL User Group, I went through the meanings of INNER, LEFT/RIGHT OUTER, CROSS, NATURAL joins, how to do a FULL OUTER JOIN in MySQL, and what STRAIGHT_JOIN means. I also explained how to recognize when you want those types of joins, and best practices for the semantics of writing joins and design patterns. Subqueries were explained in this session, and some examples of how to think differently so that you end up writing JOINs instead of subqueries. The slides (slightly different from the slides in the video — due to error correction) can be found at http://technocation.org/files/doc/2010_01MySQLJoins.pdf.

Here’s the video:

In my years of teaching MySQL topics, I've noticed that many people write bad queries. It's not necessarily their fault. It could be that they don't understand some aspect of SQL, or that they solved the problem with the first technique that came to mind, or that their query is fine except that MySQL optimizes it poorly. I'll give a few examples.

Correlated vs. Non-Correlated subquery
For those that don't know what a correlated subquery is: it means that the subquery relies on values from the outer query. If the subquery could be executed by itself, then it would be non-correlated. Here's an example of a non-correlated query. Using the `world` database, I want to return all cities that have a population larger than New York's population:

SELECT name FROM City WHERE population > (SELECT population FROM City WHERE name = 'new york');

Since the subquery can be run by itself, it's non-correlated. …

      select
title, from_date, to_date
from titles
where emp_no in
(select emp_no
from employees
where first_name = 'Mary'
      and last_name = 'Sluis'
)

The result, in MySQL 5.1.28 is

+-------+------------+------------+
| title | from_date  | to_date    |
+-------+------------+------------+
| Staff | …

One of my colleagues made a typo in a query today that led to me discovering this issue — I think it’s a bug, though it may just be how the language is defined. But certainly a subquery that cannot run should not act as if it returns TRUE.

mysql> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table foo (fid tinyint unsigned not null primary key); create table bar (bid char(1)
not null default ”);
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> insert into foo (fid) VALUES (1),(2); insert into bar (bid) VALUES (’1′),(’a');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

…