Solved

Make MySql auto delete old records in a table

Posted on 2011-02-21
6
972 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Building an interactive eFuture classroom

Watch and learn how ATEN provided a total control system solution including seamless switching matrix switch, HDBaseT extenders, PDU, lighting control to build an interactive eFuture classroom.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

756 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