Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161

    Unanswered: Delete last row on insertion of new row

    Is there a built-in way such that when a new record gets inserted, the last record gets deleted ?

    I don't want more than 100 rows in a table. So when a new row is being inserted, I would like to flush out the last one from the table.

    Basically, this data is coming from an incoming feed, and I dont want the database filled with all those data constantly.
    MySQL 5.1

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    If you have an auto_increment field on the table you can remove entries that are equal to 100 less than the new id. This could be performed inside a trigger using the last_insert_id() function which gives you the latest auto increment identifier.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    I don't want to have an auto increment field as there are like 1000 rows being inserted every hour, which may result in overflow of max length of the auto_increment ID.

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Is there a date/time field to determine the order in which the records arrive? If there is a date time stamp then the records to remove can be identified with the following select:

    SELECT * FROM tablename ORDER BY datefield DESC LIMIT 100,1;
    Using the date field from this query you can then use this to remove all entries older than this date/time.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by anjanesh View Post
    I don't want to have an auto increment field as there are like 1000 rows being inserted every hour, which may result in overflow of max length of the auto_increment ID.
    using an INTEGER auto_increment column, with 1000 rows arriving per hour, guess how long before the overflow?

    if you do the math, you will discover that it will take 245 years

    if the column is UNSIGNED it will take 490 years

    so how realistic is your concern about overflow? i would say not realistic at all

    there has never, in the history of computer systems on this planet, been an application that has lasted that long

    yours will ~not~ be the first

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2004
    Posts
    480
    and of course, just in case, there's always UNSIGNED BIGINT

  7. #7
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    Im thinking of using SQLite for this instead.
    The data is coming in via Twitter's Streaming API which is a never ending script.
    Is there a date/time field to determine the order in which the records arrive?
    Yes, and the data coming in is date sorted anyway.

    Is it better to write a cron to delete entries above 100 or create a trigger to delete last row on new inserts ?

  8. #8
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    This is something you have to decide. One way you are basically guaranteeing to have only 100 rows in the table at any single time, the other your table may grow as entries arrive.

    One that that has not been mentioned previously in the posts concerns any indexes that you may have on the table where you are going to store the information. Inserts and deletes causes the indexes to become inefficient over time. You should also consider rebuilding these indexes to keep them optimized for access.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •