ansaurus

Question

Limiting mySQL data as 5 column 2 entries per column 1 entry.

Answer 1

A:

The easiest approach might be with a trigger on INSERT.

This would let the application logic only deals with sending INSERTS when needed.

On mySQL side, the trigger logic would delete all but 4 most recent (or whatever criteria you desire) records prior to inserting the new record.

It could be something like that: (note: untested + concerns about LIMIT clasue in the nested query, could find alternative trick if need be.)

This snippet assumes that an id field would be available. Such field not shown in the OP's write-up but would make much sense, as such a PK makes CRUD operations so much easier (rather than spelling out composite keys, here would be all 3 fields...). The trigger could be made AFTER INSERT instead, and this would avoid the possibility of deleting some record unnecessarily if the new record was a duplicate).

CREATE TRIGGER LimitTweetUpdates BEFORE INSERT ON myTweeterTable
  FOR EACH ROW BEGIN
    DELETE FROM myTweeterTable
    WHERE person = NEW.person
    AND id NOT IN 
      (SELECT id FROM myTweeterTable  
       WHERE person = NEW.person
       ORDER BY id DESC
       LIMIT 5)
  END;

mjv 2009-09-21 05:07:15

Thanks so much! I actually went to learn about triggers after your comment and just before you edited, I came up with everything you had up to "AND id NOT IN."Is this second half necessary? Without it, would the trigger simply delete everything from the database then run the new query?

Joshua Cody 2009-09-21 05:41:08

@Joshua : indeed if you were to remove the "AND id NOT IN ..." the trigger would delete _all_ rows of myTweeterTable for this person. Not a good thing ;-)

mjv 2009-09-21 05:53:51

Any I do not have a PK of ID as it makes my INSERT IGNORE ineffective. I'm not sure if I can focus on a specific column for a match with INSERT IGNORE, but trying with a PK on both the id and another column cause the ignore to fail.

Joshua Cody 2009-09-21 05:54:09

The only thing I'm using this function for is inserting 5 updates at a time, so if it deletes all the updates and then re-inserts them, is the only problem I'm looking at load time? I'm not worried about caching their past updates—it's more of an advertisement/suggestion engine for readers of a site. Or would it even make a difference in load time?

Joshua Cody 2009-09-21 05:59:29

Trust me you are better off _with_ a pk than none, even if that prompts you to rethink the INSERT IGNORE. One way to do this is to slightly complicate your INSERT with an IF () statement. Say IF (NOT EXISTS (SELECT * FROM tbl WHERE person= x and picture = y and update = z) followed by orignal insert statement.

mjv 2009-09-21 06:01:49

Beware, the trigger will be called for each row being INSERTed, so even if you insert 5 rows "at once", you'll end up with only 1 row if you delete all during trigger.

mjv 2009-09-21 06:03:54

Ah, that makes perfect sense. Looks like if I am going to make this step forward, I'll have to take a few back first. So now I'm off to add a PK column, rework my SQL query and add a trigger to delete the previous entries Thanks mjv!

Joshua Cody 2009-09-21 06:12:38

ansaurus

tags:

views:

answers:

Limiting mySQL data as 5 column 2 entries per column 1 entry.

related questions