MySQL - Helpful Tidbits

My experiences at Rev360 (RevolutionEHR) and Mercato brought about developing for back-ends that leverage MySQL for data storage solutions. All my other previous experiences were within the context of Oracle, DB2, and SQL Server database technologies, and I found myself constantly Googling to see how to do things better in MySQL. Below are some of those findings.

Perform batch UPDATEs with a temp table

When performing UPDATEs against a table in bulk, MySQL is pretty non-performant when given a raw UPDATE statement with a WHERE clause:

UPDATE A
SET foo = 'bar' WHERE id < 1000;

The best way of handling bulk UPDATEs turns out to be establishing a temp table containing the values that encapsulate the data for the UPDATE, and then performing the UPDATE with a JOIN against the temporary table.

DROP TABLE IF EXISTS `tempFooValues`;

CREATE TABLE `tempFooValues` (
`id` INT(11) UNSIGNED NOT NULL,
`foo` VARCHAR(255) NOT NULL,
PRIMARY KEY(`id`)
);

INSERT INTO tempFooValues (id, foo) VALUES
(1, 'bar'),
(2, 'bar'),
...,
...,
(1000, 'bar');

UPDATE A
INNER JOIN tempFooValues ON tempFooValues.id = A.id
SET
A.foo = tempFooValues.foo;

Leveraging CASE within a JOIN

This one is pretty self-explanatory...

SELECT * FROM A
JOIN B
ON CASE
WHEN B.type IN (1, 3) AND B.a_id = A.id THEN 1
WHEN B.type IN (2) AND B.other_id = A.other_id THEN 1
ELSE 0
END = 1;

Converting Timezones from UTC

The arguments for the "from" and "to" timezones in CONVERT_TZ are the offsets from UTC. Therefore, +00:00 is itself UTC, and -07:00 would equate to PT in the U.S.

SELECT CONVERT_TZ(created, '+00:00','-07:00') FROM users;