Some time ago, a customer had a performance issue with an internal process. He was comparing, finding, and reporting the rows that were different between two tables. This is simple if you use a LEFT JOIN and an
comparison over the second table in the WHERE clause, but what if the column could be null? That is why he used UNION, GROUP BY and a HAVING clauses, which resulted in poor performance.
The challenge was to be able to compare each row using a LEFT JOIN over NULL values.
The challenge in more detail
I’m not going to use the customer’s real table. Instead, I will be comparing two sysbench tables with the same structure:
CREATE TABLE `sbtest1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned DEFAULT NULL, `c` char(120) DEFAULT NULL, `pad` char(60) DEFAULT NULL, PRIMARY …[Read more]