MySQL: Overlay modifications on original data while indexing on a field that itself has modifications

data integrityindexMySQLview

I have a MySQL table of original_data that is important for historical/auditing purposes and should not ever be changed. I want to be able to mark up these data with modifications, e.g. to correct errors in the original data entry, and view the overall composite dataset (changes overlaid on original data), without making any modifications to the table of original_data.

My fields are a mix of int, varchar, and datetime; nothing bizarre.

The system currently accomplishes this with the following:

A table original_data (which is never modified). This is indexed
on a datetime field.

A table modifications, which includes all fields that
original_data has. Each row in modifications references the primary
key of the row in original_data that is modified.

A view mods_overlay that joins original_data to modifications on
a key, displaying modified data (where it exists) in place of original
data. I'm using CASE WHEN statements to return fields from
modifications where they exists for a particular row, else return the
field from original_data.

So far, so good! The problem is, I have millions of rows, and one of the fields I need to modify is the datetime field on which original_data is indexed. As soon as my mods_overlay view overlays this datetime field, I can no longer efficiently select my data on a specific period of time; runtime for select statements increases from a few seconds to 30+ minutes. MySQL does not support indexing for views, nor does it support materialized views, either of which could help here.

One workaround: I could implement mods_overlay as a table that is pre-computed overnight, but then new modifications cannot be seen until the next day, and I'm not sure if this will be acceptable.

Are there other ways to solve this problem without making significant tooling changes?

Stated differently: is there a best practice in MySQL for providing a way to overlay a set of modifications on a set of original data (in a table that is never modified), while indexing on a field that can itself have modifications?

Best Answer

Very few users use VIEWs.

The typical approach is to ALTER the table to make the desired schema changes.

pt-online-schema-change is the tool of choice if ALGORITHM=INPLACE is not available for the desired change.

Related Solutions

Sql-server – How to make a union view execute more efficiently

The comments on the question show that the issue is that the test database the OP was using to develop the query had radically different data characteristics than the production database. It had much fewer rows and the field being used for filtering wasn't selective enough.

When the number of distinct values in a column is too small the index may not be sufficiently selective. In this case a sequential table scan is cheaper than an index seek/row lookup operation. Typically a table scan makes exensive use of sequential I/O, which is much faster than random access reads.

Often, if a query would return more than just a few percent of rows it will be cheaper just to do a table scan than an index seek/row lookup or similar operation that makes heavy use of random I/O.

Improving Performance of Nested-View Joins in PostgreSQL

I am speculating here, but I guess the fact that you LEFT JOIN to the view makes the planner compute the results from the view as a whole before joining to the first part of the query.

Try inlining the query from the view and make it a JOIN instead of a LEFT JOIN, just to see if the planner finds a faster way now:

SELECT d.data_id, d.test_session, d.a, d.b, t.c, d.d, d.e, d.f
     , p.data_id AS p_data_id, p.x2, c.str AS impression, i.h
     , p.x3, p.x3, p.x4
     , s.x5, s.x6, s.x7, s.x8, s.x9, s.x10, s.x11, s.x12, s.x13, s.x14
     , t.x15, t.x16, t.x17, t.x18, t.x19, t.x20, t.x21, t.x22, t.x23
     , i.data AS input
     , s1.data AS stage1, s2.data AS stage2, s3.data AS stage3
FROM   input_data d
JOIN   category1_results        p ON p.data_id = d.data_id
JOIN   input_file               i USING (data_id)
JOIN   stage1_output_file      s1 USING (data_id)
JOIN   stage2_output_file      s2 USING (data_id)
JOIN   stage3_output_file      s3 USING (data_id)
JOIN   category2_results        s USING (data_id)
JOIN   category3_results        t USING (data_id)
JOIN   category1_impression_str c ON p.impression = c.id 
LEFT   JOIN quality_codes       t ON t.id = d.input_quality
WHERE  NOT d.deleted;

I cleaned up your syntax to make it more manageable. Added an alias for the second data_id column, so it can execute.

If that should lead to a considerably faster execution time, you can try and add missing rows due to the INNER JOIN like this:

SELECT DISTINCT ON (1,2,3,4,5,6,7,8) *
FROM (
    <<query>>
    ) x
UNION ALL
SELECT d.data_id, d.test_session, d.a, d.b, t.c, d.d, d.e, d.f
      ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
      ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
      ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
FROM   input_data d
LEFT   JOIN quality_codes t ON t.id = d.input_quality
WHERE  NOT d.deleted;
ORDER  BY 1,2,3,4,5,6,7,8, 9 NULLS LAST; -- p.data_id is otherwise not null

Best Answer

Related Solutions

Sql-server – How to make a union view execute more efficiently

Improving Performance of Nested-View Joins in PostgreSQL

Related Question