StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POBest way to manage row expiration in mysql
text
Body
copied!<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 > $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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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