I'm working with mail data from the USPS, which contains 10 related tables to make up a single mailing, as well as a single table containing all of the scans that each mail piece may receive. Once per night I am running a batch process to perform several tasks - import all new files into the correct tables, collecting the latest scans into a table, using data from those to write a new delivery date for their related mail pieces, and then generating a big summary table based on everything.
To put some perspective, the two largest and most important tables are piece_details and scans, with about 30,000,000 and 100,000,000 rows respectively.
I'm running into 2 major problems.
- Writing to the scans table has gotten INCREDIBLY slow. I'm talking about 200 inserts per second using LOAD DATA LOCAL INFILE with the input data already sorted by the composite PK. Writing to other tables, even the piece_details table, gets me 2k+. Is this simply because my DB is so large and I have a very large PK + additional small indices? When it was smaller, performance improved significantly, and each night I am only adding between 2mm - 5mm records (and removing roughly the same). I ended up switching from an incremental integer pk to a composite based on the unique zipcode + time because that is the only way I can tie a scan to a piece, and I thought the physical layout of the rows in that order would improve performance. This was just a guess, though.
- Setting the updated delivery date based on the latest scan is also extremely slow, but this one's a bit more complex. I basically need to find the scan record with the latest scan date when grouped by zip, zip_4, zip_delivery_point, and serial_number, but only if that record is new from the last insert batch. Then I join it with a the piece_details on those same fields that I'm grouping on and then updating piece_details with the latest delivery date. The resulting query looks like this...
UPDATE piece_detail INNER JOIN ( SELECT scan.zip, scan.zip_4, scan.zip_delivery_point, scan.serial_number, MAX(scan.scan_date_time) as max_scan_date_time, scan.new FROM _scan GROUP BY scan.zip, scan.zip_4, scan.zip_delivery_point, scan.serial_number HAVING scan.new = 1 ) as scan_latest ON piece_detail.zip = scan_latest.zip AND piece_detail.zip_4 = scan_latest.zip_4 AND piece_detail.zip_delivery_point = scan_latest.zip_delivery_point AND piece_detail.serial_number = scan_latest.serial_number INNER JOIN scan ON scan.zip = scan_latest.zip AND scan.zip_4 = scan_latest.zip_4 AND scan.zip_delivery_point = scan_latest.zip_delivery_point AND scan.serial_number = scan_latest.serial_number AND scan.scan_date_time = scan_latest.max_scan_date_time INNER JOIN container_quantity ON piece_detail.cqt_database_id = container_quantity.cqt_database_id AND _piece_detail.job_id = container_quantity.job_id INNER JOIN container_summary ON container_quantity.container_id = container_summary.container_id AND container_summary.job_id = piece_detail.job_id INNER JOIN package_quantity ON package_quantity.cqt_database_id = container_quantity.cqt_database_id AND package_quantity.job_id = container_summary.job_id INNER JOIN mail_piece_unit ON container_quantity.mpu_id = mail_piece_unit.mpu_id AND mail_piece_unit.job_id = piece_detail.job_id INNER JOIN mailing_groups ON mailing_groups.mpu_company = mail_piece_unit.mpu_company INNER JOIN operation_codes ON operation_codes.operation_code = scan.operation_code INNER JOIN delivery_modifications ON delivery_modifications.process_id = operation_codes.process_id AND delivery_modifications.class_id = mail_piece_unit.mpu_class SET piece_detail.est_delivery_date = get_valid_date(scan_latest.max_scan_date_time, delivery_modifications.added_days), scan.new = 0 WHERE piece_detail.est_delivery_date IS NOT NULL;
This is pretty ugly, but I can't think of any other way to do it.
Additional Information: Schemas for the two largest tables (piece_details is partitioned by job_id/piece_id, since the vast majority of my select queries are about sumarizing by job_id).
CREATE TABLE `piece_detail` ( `job_id` varchar(8) NOT NULL, `piece_id` bigint(20) NOT NULL, `cqt_database_id` int(11) NOT NULL, `package_id` int(11) NOT NULL, `barcode_identifier` int(11) DEFAULT NULL, `service_type` int(11) DEFAULT NULL, `mailer_id` varchar(9) DEFAULT NULL, `serial_number` varchar(9) NOT NULL, `zip` varchar(5) NOT NULL, `zip_4` varchar(4) NOT NULL, `zip_delivery_point` varchar(2) NOT NULL, `est_delivery_date` date DEFAULT NULL, PRIMARY KEY (`job_id`,`piece_id`), KEY `cqt_database_id` (`cqt_database_id`), KEY `est_delivery_date` (`est_delivery_date`), KEY `scan_match` (`zip`,`zip_4`,`zip_delivery_point`,`serial_number`), ) ENGINE=InnoDB /*!50100 PARTITION BY KEY (job_id,piece_id) PARTITIONS 10 */; CREATE TABLE `scans` ( `facility_zip` varchar(5) NOT NULL, `operation_code` varchar(3) NOT NULL, `scan_date_time` datetime NOT NULL, `zip` varchar(5) NOT NULL DEFAULT '', `zip_4` varchar(4) NOT NULL, `zip_delivery_point` varchar(2) NOT NULL, `barcode_identifier` varchar(2) NOT NULL, `service_type` varchar(3) NOT NULL, `mailer_id` varchar(9) NOT NULL, `serial_number` varchar(9) NOT NULL, `new` tinyint(1) NOT NULL DEFAULT '1', `first_scan` tinyint(1) NOT NULL DEFAULT '1', PRIMARY KEY (`zip`,`zip_4`,`zip_delivery_point`,`serial_number`,`scan_date_time`), KEY `operation_code` (`operation_code`), KEY `new` (`new`), KEY `first_scan` (`first_scan`) ) ENGINE=InnoDB;