Re: How to Detect MySql table update/difference
Hi, 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. Although in relational theory, a relation means the table or view and you should be able to modify one relation to another, in SQL databases, it doesn't work that way. In SQL databases, a table doesn't get modified, you modify rows. A table is nothing but a description of a certain structure. So in order to know if any row in a table has been modified, you either need to have some kind of timestamp in each row or keep a copy of the table since it's last modification check. Instead of querying the table, I guess you could write a trigger that updates a metatable that says a row has been added/deleted/modified for a particular table. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
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=redwa...@logicworks.net
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=redwa...@logicworks.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: How to Detect MySql table update/difference
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
