Updating millions of records on inner joined subquery - optimization techniques

Question

I'm looking for some advice on how I might better optimize this query.

For each _piece_detail record that:

1. Contains at least one matching _scan record on (zip, zip_4, zip_delivery_point, serial_number)
2. Belongs to a company from mailing_groups (through a chain of relationships)
3. Has either:
   1. first_scan_date_time that is greater than the MIN(scan_date_time) of the related _scan records
   2. latest_scan_date_time that is less than the MAX(scan_date_time) of the related _scan records

I will need to:

1. Set _piece_detail.first_scan_date_time to MIN(_scan.scan_date_time)
2. Set _piece_detail.latest_scan_date_time to MAX(_scan.scan_date_time)

Since I'm dealing with millions upon millions of records, I am trying to reduce the number of records that I actually have to search through. Here are some facts about the data:

1. The _piece_details table is partitioned by job_id, so it seems to make the most sense to run through these checks in the order of _piece_detail.job_id, _piece_detail.piece_id.
2. The scan records table contains over 100,000,000 records right now and is partitioned by (zip, zip_4, zip_delivery_point, serial_number, scan_date_time), which is the same key that is used to match a _scan with a _piece_detail (aside from scan_date_time).
3. Only about 40% of the _piece_detail records belong to a mailing_group, but we don't know which ones these are until we run through the full relationship of joins.
4. Only about 30% of the _scan records belong to a _piece_detail with a mailing_group.
5. There are typically between 0 and 4 _scan records per _piece_detail.

Now, I am having a hell of a time finding a way to execute this in a decent way. I had originally started with something like this:

UPDATE _piece_detail
    INNER JOIN (
        SELECT _piece_detail.job_id, _piece_detail.piece_id, MIN(_scan.scan_date_time) as first_scan_date_time, MAX(_scan.scan_date_time) as latest_scan_date_time
        FROM _piece_detail
            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 = _container_quantity.job_id
            INNER JOIN _mail_piece_unit 
                ON _container_quantity.mpu_id = _mail_piece_unit.mpu_id 
                AND _container_quantity.job_id = _mail_piece_unit.job_id
            INNER JOIN _header 
                ON _header.job_id = _piece_detail.job_id
            INNER JOIN mailing_groups 
                ON _mail_piece_unit.mpu_company = mailing_groups.mpu_company
            INNER JOIN _scan
                ON _scan.zip = _piece_detail.zip 
                AND _scan.zip_4 = _piece_detail.zip_4 
                AND _scan.zip_delivery_point = _piece_detail.zip_delivery_point 
                AND _scan.serial_number = _piece_detail.serial_number 
        GROUP BY _piece_detail.job_id, _piece_detail.piece_id, _scan.zip, _scan.zip_4, _scan.zip_delivery_point, _scan.serial_number
    ) as t1 ON _piece_detail.job_id = t1.job_id AND _piece_detail.piece_id = t1.piece_id 
SET _piece_detail.first_scan_date_time = t1.first_scan_date_time, _piece_detail.latest_scan_date_time = t1.latest_scan_date_time
WHERE _piece_detail.first_scan_date_time < t1.first_scan_date_time 
    OR _piece_detail.latest_scan_date_time > t1.latest_scan_date_time;

I thought that this may have been trying to load too much into memory at once and might not be using the indexes properly.

Then I thought that I might be able to avoid doing that huge joined subquery and add two leftjoin subqueries to get the min/max like so:

UPDATE _piece_detail
    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 = _container_quantity.job_id
    INNER JOIN _mail_piece_unit 
        ON _container_quantity.mpu_id = _mail_piece_unit.mpu_id 
        AND _container_quantity.job_id = _mail_piece_unit.job_id
    INNER JOIN _header 
        ON _header.job_id = _piece_detail.job_id
    INNER JOIN mailing_groups 
        ON _mail_piece_unit.mpu_company = mailing_groups.mpu_company
    LEFT JOIN _scan fs ON (fs.zip, fs.zip_4, fs.zip_delivery_point, fs.serial_number) = (
        SELECT zip, zip_4, zip_delivery_point, serial_number
        FROM _scan
        WHERE zip = _piece_detail.zip 
            AND zip_4 = _piece_detail.zip_4 
            AND zip_delivery_point = _piece_detail.zip_delivery_point 
            AND serial_number = _piece_detail.serial_number
        ORDER BY scan_date_time ASC
        LIMIT 1
        )
    LEFT JOIN _scan ls ON (ls.zip, ls.zip_4, ls.zip_delivery_point, ls.serial_number) = (
        SELECT zip, zip_4, zip_delivery_point, serial_number
        FROM _scan
        WHERE zip = _piece_detail.zip 
            AND zip_4 = _piece_detail.zip_4 
            AND zip_delivery_point = _piece_detail.zip_delivery_point 
            AND serial_number = _piece_detail.serial_number
        ORDER BY scan_date_time DESC
        LIMIT 1
        )
SET _piece_detail.first_scan_date_time = fs.scan_date_time, _piece_detail.latest_scan_date_time = ls.scan_date_time
WHERE _piece_detail.first_scan_date_time < fs.scan_date_time 
    OR _piece_detail.latest_scan_date_time > ls.scan_date_time

These are the explains when I convert them to SELECT statements:

+----+-------------+---------------------+--------+----------------------------------------------------+---------------+---------+------------------------------------------------------------------------------------------------------------------------+--------+----------------------------------------------+
| id | select_type | table               | type   | possible_keys                                      | key           | key_len | ref                                                                                                                    | rows   | Extra                                        |
+----+-------------+---------------------+--------+----------------------------------------------------+---------------+---------+------------------------------------------------------------------------------------------------------------------------+--------+----------------------------------------------+
|  1 | PRIMARY     | <derived2>          | ALL    | NULL                                               | NULL          | NULL    | NULL                                                                                                                   | 844161 | NULL                                         |
|  1 | PRIMARY     | _piece_detail       | eq_ref | PRIMARY,first_scan_date_time,latest_scan_date_time | PRIMARY       | 18      | t1.job_id,t1.piece_id                                                                                                  |      1 | Using where                                  |
|  2 | DERIVED     | _header             | index  | PRIMARY                                            | date_prepared | 3       | NULL                                                                                                                   |     87 | Using index; Using temporary; Using filesort |
|  2 | DERIVED     | _piece_detail       | ref    | PRIMARY,cqt_database_id,zip                        | PRIMARY       | 10      | odms._header.job_id                                                                                                    |   9703 | NULL                                         |
|  2 | DERIVED     | _container_quantity | eq_ref | unique,mpu_id,job_id,job_id_container_quantity     | unique        | 14      | odms._header.job_id,odms._piece_detail.cqt_database_id                                                                 |      1 | NULL                                         |
|  2 | DERIVED     | _mail_piece_unit    | eq_ref | PRIMARY,company,job_id_mail_piece_unit             | PRIMARY       | 14      | odms._container_quantity.mpu_id,odms._header.job_id                                                                    |      1 | Using where                                  |
|  2 | DERIVED     | mailing_groups      | eq_ref | PRIMARY                                            | PRIMARY       | 27      | odms._mail_piece_unit.mpu_company                                                                                      |      1 | Using index                                  |
|  2 | DERIVED     | _container_summary  | eq_ref | unique,container_id,job_id_container_summary       | unique        | 14      | odms._header.job_id,odms._container_quantity.container_id                                                              |      1 | Using index                                  |
|  2 | DERIVED     | _scan               | ref    | PRIMARY                                            | PRIMARY       | 28      | odms._piece_detail.zip,odms._piece_detail.zip_4,odms._piece_detail.zip_delivery_point,odms._piece_detail.serial_number |      1 | Using index                                  |
+----+-------------+---------------------+--------+----------------------------------------------------+---------------+---------+------------------------------------------------------------------------------------------------------------------------+--------+----------------------------------------------+

+----+--------------------+---------------------+--------+--------------------------------------------------------------------+---------------+---------+------------------------------------------------------------------------------------------------------------------------+-----------+-----------------------------------------------------------------+
| id | select_type        | table               | type   | possible_keys                                                      | key           | key_len | ref                                                                                                                    | rows      | Extra                                                           |
+----+--------------------+---------------------+--------+--------------------------------------------------------------------+---------------+---------+------------------------------------------------------------------------------------------------------------------------+-----------+-----------------------------------------------------------------+
|  1 | PRIMARY            | _header             | index  | PRIMARY                                                            | date_prepared | 3       | NULL                                                                                                                   |        87 | Using index                                                     |
|  1 | PRIMARY            | _piece_detail       | ref    | PRIMARY,cqt_database_id,first_scan_date_time,latest_scan_date_time | PRIMARY       | 10      | odms._header.job_id                                                                                                    |      9703 | NULL                                                            |
|  1 | PRIMARY            | _container_quantity | eq_ref | unique,mpu_id,job_id,job_id_container_quantity                     | unique        | 14      | odms._header.job_id,odms._piece_detail.cqt_database_id                                                                 |         1 | NULL                                                            |
|  1 | PRIMARY            | _mail_piece_unit    | eq_ref | PRIMARY,company,job_id_mail_piece_unit                             | PRIMARY       | 14      | odms._container_quantity.mpu_id,odms._header.job_id                                                                    |         1 | Using where                                                     |
|  1 | PRIMARY            | mailing_groups      | eq_ref | PRIMARY                                                            | PRIMARY       | 27      | odms._mail_piece_unit.mpu_company                                                                                      |         1 | Using index                                                     |
|  1 | PRIMARY            | _container_summary  | eq_ref | unique,container_id,job_id_container_summary                       | unique        | 14      | odms._header.job_id,odms._container_quantity.container_id                                                              |         1 | Using index                                                     |
|  1 | PRIMARY            | fs                  | index  | NULL                                                               | updated       | 1       | NULL                                                                                                                   | 102462928 | Using where; Using index; Using join buffer (Block Nested Loop) |
|  1 | PRIMARY            | ls                  | index  | NULL                                                               | updated       | 1       | NULL                                                                                                                   | 102462928 | Using where; Using index; Using join buffer (Block Nested Loop) |
|  3 | DEPENDENT SUBQUERY | _scan               | ref    | PRIMARY                                                            | PRIMARY       | 28      | odms._piece_detail.zip,odms._piece_detail.zip_4,odms._piece_detail.zip_delivery_point,odms._piece_detail.serial_number |         1 | Using where; Using index; Using filesort                        |
|  2 | DEPENDENT SUBQUERY | _scan               | ref    | PRIMARY                                                            | PRIMARY       | 28      | odms._piece_detail.zip,odms._piece_detail.zip_4,odms._piece_detail.zip_delivery_point,odms._piece_detail.serial_number |         1 | Using where; Using index; Using filesort                        |
+----+--------------------+---------------------+--------+--------------------------------------------------------------------+---------------+---------+------------------------------------------------------------------------------------------------------------------------+-----------+-----------------------------------------------------------------+

Now, looking at the explains generated by each, I really can't tell which is giving me the best bang for my buck. The first one shows fewer total rows when multiplying the rows column, but the second appears to execute a bit quicker.

Is there anything that I could do to achieve the same results while increasing performance through modifying the query structure?

Answer 1

Why aren't you using sub-queries for each join? Including the inner joins?

INNER JOIN (SELECT field1, field2, field 3 from _container_quantity order by 1,2,3) 
    ON _piece_detail.cqt_database_id = _container_quantity.cqt_database_id 
    AND _piece_detail.job_id = _container_quantity.job_id
INNER JOIN (SELECT field1, field2, field3 from _container_summary order by 1,2,3)
    ON _container_quantity.container_id = _container_summary.container_id 
    AND _container_summary.job_id = _container_quantity.job_id

You're definitely pulling a lot into memory by not limiting your selects on those inner joins. By using the order by 1,2,3 at the end of each sub-query you create an index on each sub-query. Your only index is on headers and you aren't joining on _headers....

A couple suggestions to optimize this query. Either create the indexes you need on each table, or use the Sub-query join clauses to create manually the indexes you need on the fly.

Also remember that when you do a left join on a "temporary" table full of aggregates you are just asking for performance trouble.

Contains at least one matching _scan record on (zip, zip_4, zip_delivery_point, serial_number)

Umm...this is your first point in what you want to do, but none of these fields are indexed?

Answer 2

From your explain results it seems that the subquery is going through all the rows twice then, how about you keep the MIN/MAX from the first one and use just one left join instead of two?

Answer 3

Disable update of index while doing the bulk updates

ALTER TABLE _piece_detail DISABLE KEYS;

UPDATE ....;

ALTER TABLE _piece_detail ENABLE KEYS;

Refer to the mysql docs : http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

EDIT: After looking at the mysql docs I pointed to, I see the docs specify this for MyISAM table, and is nit clear for other table types. Further solutions here : How to disable index in innodb

Answer 4

There is something I was taught and I strictly follow till today - Create as many temporary table you want while avoiding the usage of derived tables. Especially it in case of UPDATE/DELETE/INSERTs as

1. you cant predict the index on derived tables
2. The derived tables might not be held in memory if the resultset is big
3. The table(MyIsam)/rows(Innodb) may be locked for longer time as each time the derived query is running. I prefer a temp table which has primary key join with parent table.

And most importantly it makes you code look neat and readable.

My approach will be

CREATE table temp xxx(...)
INSERT INTO xxx select q from y inner join z....;
UPDATE _piece_detail INNER JOIN xxx on (...) SET ...;

Always reduce you downtime!!