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;

Performing Maintenance on Huge Tables

Old faithful has been holding down the fort for 9 years. It's full of juicy data that's both important for reporting and critical for application business logic for end users. It is also missing a ton of helpful index values underneath the columns matter most.

Your reports won't report, and you know in your gut that ALTER TABLE in real time on old faithful will take about 10,000 years to execute.

So, you perform the following:

• Create a new table that contains the index values you require
• Copy the data from old faithful into this clone table with indices
• Rename (or drop) the original old faithful, and rename the clone to become old faithful

BEGIN;

CREATE TABLE IF NOT EXISTS oldFaithfulWithIndices LIKE oldFaithful;

ALTER TABLE oldFaithfulWithIndices
    ADD INDEX `account_id_index`(`account_id`),
    ADD INDEX `email_address_index`(`email_address`),
    ADD INDEX `created_datetime_index`(`created_datetime`),
    ADD INDEX `updated_datetime_index`(`updated_datetime`);

INSERT INTO oldFaithfulWithIndices SELECT * FROM oldFaithful;

RENAME TABLE oldFaithful TO oldFaithful_archive;

RENAME TABLE oldFaithfulWithIndices TO oldFaithful;

COMMIT;