Databases
StackOverflow2013
Postsdbo
POHow to optimise change history data for MySQL
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to optimise change history data for MySQL
    primarykey
    data
    text
    <p>The previous table this data was stored in approached 3-4gb, but the data wasn't compressed before/after storage. I'm not a DBA so I'm a little out of my depth with a good strategy.</p> <p>The table is to log changes to a particular model in my application (user profiles), but with one tricky requirement: we should be able to fetch the state of a profile at any given date.</p> <p>Data (single table):</p> <pre><code>id, username, email, first_name, last_name, website, avatar_url, address, city, zip, phone </code></pre> <p>The only two requirements:</p> <ol> <li>be able to fetch a list of changes for a given model</li> <li>be able to fetch state of model on a given date</li> </ol> <p>Previously, <strong>all of the profile data</strong> was stored for a <strong>single change</strong>, even if only one column was changed. But to get a 'snapshot' for a particular date was easy enough.</p> <p>My first couple of solutions in optimising the data structure:</p> <p>(1) only store changed columns. This would drastically reduce data stored, but would make it quite complicated to get a snapshot of data. I'd have to merge all changes up to a given date (could be thousands), then apply that to a model. But that model couldn't be a fresh model (only changed data is stored). To do this, I'd have to first copy over all data from current <code>profiles</code> table, then to get snapshot apply changes to those base models.</p> <p>(2) store whole of data, but convert to a compressed format like gzip or binary or whatnot. This would remove ability to query the data other than to obtain changes. I couldn't, for example, fetch all changes <code>where email = ''</code>. I would essentially have a single column with converted data, storing the whole of the profile.</p> <p>Then, I would want to use relevant MySQL table options, like ARCHIVE to further reduce space. </p> <p>So my question is, are there any other options which you feel are a better approach than 1/2 above, and, if not, which would be better?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USRandomSeed
    1. USDamien Roche
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to optimise change history data for MySQL
      1. This table or related slice is empty.
      1. VTDownMod
    2. VO
      singulars
      1. POHow to optimise change history data for MySQL
      1. USDamien Roche
      1. VTBountyStart
    3. VO
      singulars
      1. POHow to optimise change history data for MySQL
      1. This table or related slice is empty.
      1. VTUpMod
    1. COSaving all of the profile data for a single change uncompressed is the correct strategy. Can you offload the older data (older than a year?) to flat files?
      singulars
      1. POHow to optimise change history data for MySQL
      1. USGilbert Le Blanc
    2. CO@GilbertLeBlanc thanks. I don't think I could offload any data. Need to be able to filter the data based on any date, whether yesterday or 10 years ago. I guess I could partition the table, store this data in separate tables based on date, but I don't think that achieves the same thing.
      singulars
      1. POHow to optimise change history data for MySQL
      1. USDamien Roche
    3. COMySQL has partitioning built in. You have to set up the table for partitioning. http://dev.mysql.com/doc/refman/5.6/en/partitioning.html
      singulars
      1. POHow to optimise change history data for MySQL
      1. USGilbert Le Blanc
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload