You need a timestamp column that autoupdates upon insert. http://dev.mysql.com/doc/refman/5.0/en/datetime.html
Then use the DATE_SUB function for x seconds. http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function _date-sub http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function _date-add Not sure what all this "count" stuff is about as that's not what the OP asked for. He wanted to know what NEW rows there was, not a count of them, at least that's how I read it. > -----Original Message----- > From: BS TLC [mailto:bs...@ymail.com] > Sent: Wednesday, August 05, 2009 2:57 PM > To: Rolando Edwards; mysql@lists.mysql.com > Subject: Re: How to Detect MySql table update/difference > > > Ok, but in this way I can only detect if it's done ONE type > of operation, for example if I add a row and I remove an > another one with this query I detect "no difference". > The principal point of the query that I want is to say > "which" rows are changed (added or deleted). I think it's not > a easy query (or set of queries), but I want to create one > for this goal. > > However thanks for the hint about the difference between > COUNT(1) and COUNT(*)! > > Thanks. > > > -----Original Message----- > From: Rolando Edwards <redwa...@logicworks.net> > Sent: Wednesday, August 05, 2009 23:12:09 > Subject: RE: How to Detect MySql table update/difference > > I have good news and bad news for you when it comes to MySQL 5.x. > > Good News if you are counting against MyISAM > Bad News if you are counting against InnoDB > > Good News > --------- > For MyISAM > Just use either > SELECT table_rows FROM information_schema.tables WHERE > table_schema='<given db name>' and table_name='<given table name>'; > Or > SELECT COUNT(1) FROM <db-name>.<tbl-name>; (This will work in > MySQL 4.x as well) > > Bad News > -------- > For InnoDB > SELECT COUNT(1) FROM <db-name>.<tbl-name>, even though it can > run faster than SELECT COUNT(*), > will still count every row in the table anyway. > > If you are using InnoDB, you are on your own !!!! > If you are using MyISAM, have fun !!!! > > Rolando A. Edwards > MySQL DBA (CMDBA) > > 155 Avenue of the Americas, Fifth Floor > New York, NY 10013 > 212-625-5307 (Work) > 201-660-3221 (Cell) > AIM & Skype : RolandoLogicWorx > redwa...@logicworks.net > > -----Original Message----- > From: BS TLC [mailto:bs...@ymail.com] > Sent: Wednesday, August 05, 2009 10:30 AM > To: mysql@lists.mysql.com > Subject: How to Detect MySql table update/difference > > > Hi, I'm a novel developer of MySql and now I am trying to > create a mysql query to detect table updates. > > I query a database table every X seconds, and i want to get > only the different rows in the table. The result that I want > to have is simply > TABLE (t = now) - TABLE (t = X second ago) > > Every time that i query the database I store the table > situation in another table, called TABLE_TEMP, so the > operation of difference detection is > TABLE_DIFERENCE = TABLE - TABLE_TEMP. > > How can I do this query? The problem that I have is that I > don't know the structure of the table and I want to create a > program with can be used for all types of data and tables. > > Please help me. > > Best regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
