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:bstlc@stripped]
> Sent: Wednesday, August 05, 2009 2:57 PM
> To: Rolando Edwards; mysql@stripped
> 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 <redwards@stripped>
> 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
> redwards@stripped
>
> -----Original Message-----
> From: BS TLC [mailto:bstlc@stripped]
> Sent: Wednesday, August 05, 2009 10:30 AM
> To: mysql@stripped
> 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
