Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 14 4 Older Entries

Displaying posts with tag: subquery (reset)

Employee_Team +1 Vote Up -0Vote Down

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. A form of the WITH CLAUSE, WITH RECURSIVE”, allows to design a recursive query: a query which repeats itself again and again, each time using the results of the previous iteration. This can be quite useful to produce reports based on hierarchical data. And thus is an alternative to Oracle’s CONNECT BY. MySQL does not natively support WITH RECURSIVE, but it is easy to emulate it with a generic, reusable stored

  [Read more...]
Employee +2 Vote Up -0Vote Down
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.
In its simplest form, this feature is a kind of "boosted derived table".

Assume that a table T1 has three columns:

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:


  [Read more...]
3 Simple Patterns for Tighter MySQL Code
+0 Vote Up -0Vote Down

Join 8000 others and follow Sean Hull on twitter @hullsean. SQL is derided by many and for good reason. It’s key to scalability yet terribly difficult to write good code. Here’s a few quick tips to write tighter queries in MySQL 1. Get rid of those Subqueries! Subqueries are a standard part of SQL, unfortunately […]

The post 3 Simple Patterns for Tighter MySQL Code appeared first on Scalable Startups.

Discover MyXplain, another way to learn, search, find and stroll
+4 Vote Up -1Vote Down



I’m very glad to announce you that MyXplain is released today.

This project is the result of many weeks of work for my friend Max and I and we are obviously very proud to present it.

Please take a time to browse it and find all the surprises that awaiting you.

We wanted to make an unique place, a gathering place where you can find all you want to know about the most useful MySQL commands.
We started with the explain command because we consider it as one of the most important.
show processlist and show slave status will be the next proposed commands on

  [Read more...]
Cost-based choice between subquery materialization and EXISTS
Employee +7 Vote Up -1Vote Down
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

  [Read more...]
Faster subqueries with materialization
Employee +3 Vote Up -0Vote Down
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:

count(distinct ps_suppkey) as supplier_cnt
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 (
s_comment like '%Customer%Complaints%'
group by
order by
supplier_cnt desc,

  [Read more...]
What about the subqueries?
+0 Vote Up -0Vote Down

MySQL version 4.1 was quite revolutionary. The main reason for that was support for sub-queries.1

However since then MySQL users were rather discouraged to use that functionality, basically due to the implementation’s poor performance  and forced to build complicated queries based on joins rather than on subqueries.

Of course you can do some effort to optimize your subquery with sometimes very good results2. Not always it’s easy or even possible if you can’t change the code though.

You’d say it’s

  [Read more...]
Understanding the unique_subquery optimization
Employee +3 Vote Up -0Vote Down
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

  [Read more...]
MySQL Limitations Part 3: Subqueries
+4 Vote Up -0Vote Down

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 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

  [Read more...]
When the subselect runs faster
+1 Vote Up -1Vote Down

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

The query was very simple like:

  • 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:

  • `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:  [Read more...]
    Showing entries 1 to 10 of 14 4 Older Entries

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.