Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>First of all, I wouldn't worry at all about a 3GB table (unless it grew to this size in a very short period of time). MySQL can take it. Space shouldn't be a concern, keep in mind that a 500 GB hard disk costs about 4 man-hours (in my country).</p> <p>That being said, in order to lower your storage requirements, create one table for each field of the table you want to monitor. Assuming a <code>profile</code> table like this:</p> <pre><code>CREATE TABLE profile ( profile_id INT PRIMARY KEY, username VARCHAR(50), email VARCHAR(50) -- and so on ); </code></pre> <p>... create two history tables:</p> <pre><code>CREATE TABLE profile_history_username ( profile_id INT NOT NULL, username VARCHAR(50) NOT NULL, -- same type as profile.username changedAt DATETIME NOT NULL, PRIMARY KEY (profile_id, changedAt), CONSTRAINT profile_id_username_fk FOREIGN KEY profile_id_fkx (profile_id) REFERENCES profile(profile_id) ); CREATE TABLE profile_history_email ( profile_id INT NOT NULL, email VARCHAR(50) NOT NULL, -- same type as profile.email changedAt DATETIME NOT NULL, PRIMARY KEY (profile_id, changedAt), CONSTRAINT profile_id_fk FOREIGN KEY profile_id_email_fkx (profile_id) REFERENCES profile(profile_id) ); </code></pre> <p>Everytime you change one or more fields in <code>profile</code>, log the change in each relevant history table:</p> <pre><code>START TRANSACTION; -- lock all tables SELECT @now := NOW() FROM profile JOIN profile_history_email USING (profile_id) WHERE profile_id = [a profile_id] FOR UPDATE; -- update main table, log change UPDATE profile SET email = [new email] WHERE profile_id = [a profile_id]; INSERT INTO profile_history_email VALUES ([a profile_id], [new email], @now); COMMIT; </code></pre> <p>You may also want to set appropriate <code>AFTER</code> triggers on <code>profile</code> so as to populate the history tables automatically.</p> <p>Retrieving history information should be straightforward. In order to get the state of a profile at a given point in time, use this query:</p> <pre><code>SELECT ( SELECT username FROM profile_history_username WHERE profile_id = [a profile_id] AND changedAt = ( SELECT MAX(changedAt) FROM profile_history_username WHERE profile_id = [a profile_id] AND changedAt &lt;= [snapshot date] ) ) AS username, ( SELECT email FROM profile_history_email WHERE profile_id = [a profile_id] AND changedAt = ( SELECT MAX(changedAt) FROM profile_history_email WHERE profile_id = [a profile_id] AND changedAt &lt;= [snapshot date] ) ) AS email; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POHow to optimise change history data for MySQL
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USRandomSeed
    1. USRandomSeed
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow to optimise change history data for MySQL
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI think your `select ... for update` ("lock all tables") query is redundant. You did this to prevent deadlock or what? That could only happen if there was another transaction write-locking these tables in the opposite order, i.e. history first and then the main table. [It is enough to keep that order of locking resources](http://stackoverflow.com/q/2332768/684229). And of course, handle the error 1213 by restarting the transaction (but you should do this anyway)
      singulars
      1. PO
      1. USTMS
    2. COYes, on second thought this is certainly superfluous. Not sure what I has in mind at that time :)
      singulars
      1. PO
      1. USRandomSeed
 

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