List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:August 5 2009 10:22pm
Subject:RE: How to Detect MySql table update/difference
View as plain text  
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

Thread
How to Detect MySql table update/differenceBS TLC5 Aug
  • RE: How to Detect MySql table update/differenceRolando Edwards5 Aug
    • Re: How to Detect MySql table update/differenceBS TLC5 Aug
      • RE: How to Detect MySql table update/differenceDaevid Vincent6 Aug
  • Re: How to Detect MySql table update/differenceMartijn Tonies6 Aug