Solved

Make MySql auto delete old records in a table

Posted on 2011-02-21
6
956 Views
Last Modified: 2013-12-24
Hi,

is it possible to make mysql limit the number of records in table to 100 and when a new record is inserted delete the eldest to ensure the table doesn't grow too much?

I am using Coldfusion so could run a bunch of sql queries to achieve it but was hoping there was a built in function or trigger that can handle this
0
Comment
Question by:Lmillard
6 Comments
 
LVL 20

Expert Comment

by:Proculopsis
ID: 34942062

If you always select top 100 ordered descending by age then your result set will always be manageable.  Then from time-to-time you can perform maintenance to remove any surplus rows or you could create an insert trigger to maintain on-the-fly.
0
 

Author Comment

by:Lmillard
ID: 34942172
Hi, this is inserting records into a table that is history orientated so i would like it to be in the background as its not about retrieving the right amount of records, more keeping the size under control. I am unfamiliar with triggers so was hoping there was a setting for the table
0
 
LVL 20

Expert Comment

by:Proculopsis
ID: 34942329

//Try something like this:

CREATE TRIGGER purgeOld
BEFORE INSERT ON myTable
DELETE FROM myTable WHERE ......
0
 
LVL 82

Expert Comment

by:leakim971
ID: 34943490
A trigger will not work with MySQL : http://forums.mysql.com/read.php?99,122354,122505#msg-122505

I think you need to do that with the language used on the server side
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 34945059

After insert, or periodically (nightly batch?) you could do a delete statement like this..


delete from myTable where ID not in (
  select top 100 ID
  from myTable
  order by theDate desc)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
mysql disables rename 4 67
when to use sequences in mysql 4 27
SQL View Syntax case etc 5 34
SQL question - need unique values for one column that is not displayed 2 21
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

775 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question