MySQL Subquery Optimization and using UPDATE with INNER JOINs

Two advanced MySQL tips today:

  1. Never use a subquery inside a WHERE field IN(...) list

  2. How-to: Update values in a table from a calculation that uses the table's own values, in a single query

Let's begin!

  1. Never use a subquery inside a WHERE field IN(...) list

    BAD/SLOW:

    SQL
    SELECT * FORM table1 WHERE datefield IN 
      (SELECT datefield FROM table2 GROUP BY datetype)

    It's tempting to use a subquery inside an IN(...) list, but your query speed will slow exponentially as more rows are added. Don't be tricked by query caching either, since every new INSERT will erase the cache. The solution is to use an INNER JOIN and subquery instead.

    GOOD/OPTIMIZED:

    SQL
    SELECT *
    FROM table1 AS t1
    INNER JOIN (
      SELECT datefield FROM table2 GROUP BY datetype
    ) AS t2 ON t1.datefield=t2.datefield;

     

  2. How-to: Update values within a table using the table's own fields

    The UPDATE command allows you to join other tables. We can use a subquery on the same table to calculate new field values, then join this subquery as a "table."

    For this example, a table contains a date field with values two weeks apart, but we want to change them to one week apart. (A user-defined variable @row is needed to keep track of the row number in order to calculate the new date.)

    SQL
    -- Change dates from bi-monthly to weekly
    SET @row = -1;
    UPDATE u_tabledates AS t1
    INNER JOIN
      (
      SELECT *, DATE_SUB(u_date, INTERVAL (@row := @row + 1) week) AS new_date
      FROM u_tabledates ORDER BY u_date ASC
      ) AS t2 ON (t2.u_id=t1.u_id)
    SET t1.u_date = t2.new_date

Comments

yt tfyt ty 5t

yt tfyt ty 5t

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.
  • You can enable syntax highlighting of source code with the following tags: [code], [blockcode], [bash], [css], [html], [ini], [javascript], [mysql], [php], [sql], [xml]. PHP source code can also be enclosed in <?php ... ?> or <% ... %>.

More information about formatting options