Faster Database Oracle vs Mysql

vivekanandan · #1 07-20-2007, 05:30 AM

Hi,
when executing the same update statement many times , mysql says N rows updated only on first time next it says 0 Rows Updated, but other database like oracle it shows N Rows updated for Each executation time .
is it MYSQL Optimization which makes mysql faster or bug ?

priyan · #2 09-04-2007, 03:10 AM

Hi,

While updating, If you set a column to the value it currently available in the table then MySQL notices this and does not update it. Generally MySQL does't do dummy update.

Go thru the below given example,

Quote:

mysql> create table updt_test
-> (
-> id integer,
-> name varchar(30)
-> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into updt_test values (1, 'philip');
Query OK, 1 row affected (0.00 sec)

mysql> insert into updt_test values (2, 'john');
Query OK, 1 row affected (0.00 sec)

mysql> insert into updt_test values (3, 'smith');
Query OK, 1 row affected (0.00 sec)

mysql> select * from updt_test;
+------+--------+
| id | name |
+------+--------+
| 1 | philip |
| 2 | john |
| 3 | smith |
+------+--------+
3 rows in set (0.00 sec)

mysql> UPDATE updt_test SET name = 'john' WHERE id = 2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
-- HERE THE CONDITION MATCHES BUT THE VALUE AVAILABLE IN THE TABLE IS THE SAME AS IN UPDATE STATEMENTS, here no row will be affected.

mysql> UPDATE updt_test SET name = 'john patrick' WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- HERE THE CONDITION MATCHES BUT THE VALUE AVAILABLE IN THE TABLE IS NOT THE SAME AS IN UPDATE STATEMENTS, so update works here.

mysql> select * from updt_test;
+------+--------------+
| id | name |
+------+--------------+
| 1 | philip |
| 2 | john patrick |
| 3 | smith |
+------+--------------+
3 rows in set (0.00 sec)

mysql> UPDATE updt_test SET name = 'john patrick' WHERE id = 2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

mysql>

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to get sequence number from oracle database using biztalk oracle db adapter	arjkhanna	Server Management	3	06-11-2009 02:30 AM
How can I convert a Access database to a MySQL database?	itbarota	PHP Programming	2	09-13-2007 09:04 AM
Oracle Database Objects	Murali	Database Support	7	09-05-2007 05:09 AM
Advantages to using Oracle’s CREATE DATABASE	vadivelanvaidyanathan	Database Support	1	07-17-2007 01:59 AM
Oracle Database Lite 10g	Karpagarajan	Database Support	3	03-29-2007 03:26 PM