List:General Discussion« Previous MessageNext Message »
From:BS TLC Date:August 5 2009 9:56pm
Subject:Re: How to Detect MySql table update/difference
View as plain text  
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


      

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql 
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1 


      
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