MySQL Subqueries and INNER JOINs 2009 Apr 3

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 copy to clipboard
   1. SELECT * FORM table1 WHERE datefield IN
   2.   (SELECT datefield FROM table2 GROUP BY datetype)

   
   

   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 copy to clipboard
   1. SELECT *
   2. FROM table1 AS t1
   3. INNER JOIN (
   4.   SELECT datefield FROM table2 GROUP BY datetype
   5. ) AS t2 ON t1.datefield=t2.datefield;

   
   

   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 copy to clipboard
   1. # Change dates from bi-monthly to weekly
   2. SET @row = -1;
   3. UPDATE u_tabledates AS t1
   4. INNER JOIN
   5.   (
   6.   SELECT *, DATE_SUB(u_date, INTERVAL (@row := @row + 1) week) AS new_date
   7.   FROM u_tabledates ORDER BY u_date ASC
   8.   ) AS t2 ON (t2.u_id=t1.u_id)
   9. SET t1.u_date = t2.new_date

   
   

   # 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