Databases
StackOverflow2013
Postsdbo
POBest way to manage row expiration in mysql
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POBest way to manage row expiration in mysql
    primarykey
    data
    text
    <p>An application does the following:</p> <ul> <li>writes a row to a table that has a unique ID</li> <li>read the table and find the unique ID and output the other variables (among which the timestamp).</li> </ul> <p>The question is: the application needs to read only the non-expired rows, which do expire every 2 minutes. There are a few alternatives to accomplish this: which has the best performance?</p> <p>Consider that reading the expired rows doesn't matter as it will be done sporadically. The number of non expired rows will always be below a few thousands, but may expect just a few hundreds.</p> <p>The cron job (run every minute) (or mysql event schedule) to do this can be one of the following (or any other idea?) based on the timestamp:</p> <p>A) add a BOL variable to index the table and then read WHERE is not expired (based on the boll variable of course) B) add a BOL variable to partition the table and then read only the relevant partition (i am new to partitioning so I'm not sure how this may work out) C) read the whole table and delete each row that is expired and then write the same row to another table D) when writing, write two rows contemporarily in two tables and then delete the expired ones on in one table</p> <p>OR </p> <p>E) not use a cron job at all and check the timestamp on every read. (but why would I scan the whole table? Expired row are basically useless to the application itself)</p> <h1>Edit 1</h1> <p>Let me rephrase the question: </p> <p>The objective is to retrieve all columns of a row only if the row was written less than 2 minutes ago.</p> <p>The table has this structure, but can be entirely redefined</p> <pre><code>transactionID CHAR(8) NOT NULL, PRIMARY KEY(transactionID), details VARCHAR(416), tel INT(10), time TIMESTAMP(), address VARCHAR(60), town VARCHAR(30), flat VARCHAR (5), supplier SMALLINT() </code></pre> <p>also <code>supplier</code> is a foreign key</p> <p>Indexes are <code>transactionID</code> and eventually "<code>status</code>", an extra column, data type TO_BE_DEFINED_but_probably_SMALLINT_?()</p> <h3>Solution 1: use an indexed column which indicates the status of the row (expired, active, used)</h3> <p>Which is achieved running a <strong>cron job</strong> to change the field value from 1 (active) to 2 (expired) and the query would be the following:</p> <pre><code>$transaction = //a POST or GET $query = mysqli_query($con,"SELECT * FROM table WHERE transaction = '$transaction' AND status = 1"); if(mysql_num_rows($query)== 0){ echo "Transaction expired"; } else{ // I will show all the fields in the selected row; } mysqli_close($con); </code></pre> <h3>Solution 2: use a partition based on the timestamp of each row</h3> <p>Which is achieved running a <strong>cron job</strong> to partition the column every 2 minutes, but then the query is probably faster:</p> <pre><code>$transaction = //a POST or GET $query = mysqli_query($con,"SELECT * FROM table WHERE transaction = '$transaction' AND PARTITION (active)"); if(mysql_num_rows($query)== 0){ echo "Transaction expired"; } else{ // I will show all the fields in the selected row; } mysqli_close($con); </code></pre> <p>THe cron job would then be similar to this</p> <pre><code>$date = date("Y-m-d H:i:s"); $time = strtotime($date); $check = $time - (2); $query = mysqli_query($con,"PARTITION BY RANGE (timestamp_field) (PARTITION active VALUES LESS THAN ($check)), ((PARTITION expired VALUES MORE THAN ($check));") </code></pre> <h3>Solution 3: forget all of this and copy the rows with a timestamp older than 2 minutes to another table with a cron job</h3> <p>Simple to accomplish although the script would be probably heavy on the write side, altough write efficiency to the "expired" table is irrelevant, I want the "active" query to be fast.</p> <h3>Solution 3B: When adding a row, write it also on another table so that it's only a DELETE function that the cron job had to perform.</h3> <h3>Solution 4: forget all about it and WHERE on the timestamp</h3> <pre><code>$transaction = //a POST or GET $date = date("Y-m-d H:i:s"); $time = strtotime($date); $check = $time - (2); $query = mysqli_query($con,"SELECT * FROM table WHERE transaction = '$transaction' AND timestamp &gt; $check"); if(mysql_num_rows($query)== 0){ echo "Transaction expired"; } else{ // I will show all the fields in the selected row; } mysqli_close($con); </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmarc_s
    1. USsmartcity
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POBest way to manage row expiration in mysql
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POBest way to manage row expiration in mysql
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    3. VO
      singulars
      1. POBest way to manage row expiration in mysql
      1. USMarjeta
      1. VTFavorite
    1. COYour problem statement is not very clear. When you say `BOL`, do you mean boolean or something else? Is your objective to speed up reading of the non-expired rows, or deleting of the expired rows? You talk `WHERE` searching and then in the same paragraph about deleting rows and then again about duplicating rows. Please also mention what is the _current performance_ and the _current table size_ (if available). Finally, if expired rows are useless; why do you even want to go through this exercise since they will only be a few thousand (which is irrelevant).
      singulars
      1. POBest way to manage row expiration in mysql
      1. USBurhan Khalid
    2. COsounds to me you are doing an simple thing very very complex.. you expire every 2 minutes records.. so you also are inserting/updating every 2 minutes records that are not expired right?? if not you should elaborate this more!! Because it sounds like you want to implement an custom `MultiVersion Concurrency Control` (MCC or MVCC)
      singulars
      1. POBest way to manage row expiration in mysql
      1. USRaymond Nijland
    3. CO@BurhanKhalid Objective is to speeding the reading of non-expired rows. By BOL I mean a variable to check whether is expired/non expired, but could also be a tinyINT if the type of status of the row can also be a third one. THe reason why I wrote BOl is that I have little understanding on the performance of indexing based on the variable type, and I guessed with a boolean would be faster. There is no current table size as the app is being developed. As per your last line: why should I make the query run through the whole table, expired rows will be much more than a few thousands.
      singulars
      1. POBest way to manage row expiration in mysql
      1. USsmartcity
 

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