I'm a Drupal PHP/MySQL web developer, living and working in Seattle, WA.
My professional interests are optimization, MySQL, front-end usability, efficient coding, and making Drupal do cool things.
Two advanced MySQL tips today:
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.
SELECT * FROM table1 AS t1 INNER JOIN ( SELECT datefield FROM table2 GROUP BY datetype ) AS t2 ON t1.datefield=t2.datefield;
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.)
-- 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