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
UPDATEs against a table in bulk, MySQL is pretty non-performant when given a raw
UPDATE statement with a
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.
Leveraging CASE within a JOIN
This one is pretty self-explanatory…