From: Jorge Bastos
Date: November 4 2005 11:56pm
Subject: Fw: MySql4.1.15 Vs MySql5.0
List-Archive: http://lists.mysql.com/win32/17732
Message-Id: <005b01c5e19b$6b8b02e0$0201a8c0@pcjorge>
MIME-Version: 1.0
Content-Type: text/plain;
	format=flowed;
	charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 7bit

Does the table has promary keys and index's ?


----- Original Message ----- 
From: "Ilavajuthy Palanisamy" <ipalanisamy@stripped>
To: <win32@stripped>
Sent: Friday, November 04, 2005 11:47 PM
Subject: MySql4.1.15 Vs MySql5.0


Hi,

 

We are evaluating MySql database for our future project. We found that
the performance of MySql5.0 is slower compared to MySql4.1.15 in terms
of inserts and updates.

 

My PC configuration

Windows XP Professional version

1GB RAM

Pentium(r) 4 CPU 3.2 GHz

My-huge.ini setup

 

What I have tried.

Created a table and inserted 2 million records. In 4.1.15 it took ~7
mins and in 5.0 it took ~6 mins. This insertion is happened through java
program.

 

After inserting we tried update on the above table. In 4.1.15 it took
~10 mins and in 5.0 it took ~46 mins. 

 

We need some of the features of 5.0 like view, auto increment, etc,
however due to this performance issues, we are confused which version to
go for or to continue to stay with our current database that we are
using?

 

Would like to know is this a real performance issue in 5.0 or do I need
to change any settings?

 

The schema of the table that I used is;

String stmt = "INSERT INTO " + tableName + " (id," +

            "userId," +

            "applicationInstanceId," +

            "startTime," +

            "endTime," +

            "bytesIn," +

            "bytesOut," +

            "packetsIn," +

            "packetsOut," +

            "errorPackets," +

            "droppedPackets," +

            "flowCount," +

            "lastUpdated," +

            "entryStatus," +

            "ageOut," +

            "deviceId," +

            "deviceSpecificId," +

            "sourcePort)" +

            " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

 

The update output of 4.1.15 Vs 5.0

 

MySql5.0 default setup

----------------------

mysql> update mytable set entrystatus=1 where userid=12 and
entrystatus=0;

Query OK, 105263 rows affected (50.25 sec)

Rows matched: 105263  Changed: 105263  Warnings: 0

 

mysql> update mytable set entrystatus=1 where userid=13 and
entrystatus=0;

Query OK, 105263 rows affected (42.45 sec)

Rows matched: 105263  Changed: 105263  Warnings: 0

 

mysql> update mytable set entrystatus=1 where userid=13 and
entrystatus=0;

Query OK, 0 rows affected (0.55 sec)

Rows matched: 0  Changed: 0  Warnings: 0

 

mysql> update mytable set entrystatus=1 where userid=14 and
entrystatus=0;

Query OK, 105263 rows affected (44.30 sec)

Rows matched: 105263  Changed: 105263  Warnings: 0

 

 

MySql5.0 huge setup

--------------------

mysql> update mytable set entrystatus=1 where userid=1 and
entrystatus=0;

Query OK, 105264 rows affected (40.94 sec)

Rows matched: 105264  Changed: 105264  Warnings: 0

 

mysql>

mysql> update mytable set entrystatus=1 where userid=2 and
entrystatus=0;

Query OK, 105264 rows affected (36.48 sec)

Rows matched: 105264  Changed: 105264  Warnings: 0

 

mysql>

mysql> update mytable set entrystatus=1 where userid=3 and
entrystatus=0;

Query OK, 105264 rows affected (37.97 sec)

Rows matched: 105264  Changed: 105264  Warnings: 0

 

 

MySql4.1.15 default setup

-------------------------

mysql> update mytable set entrystatus=1 where userid=1 and
entrystatus=0;

Query OK, 105264 rows affected (11.08 sec)

Rows matched: 105264  Changed: 105264  Warnings: 0

 

mysql>

mysql> update mytable set entrystatus=1 where userid=2 and
entrystatus=0;

Query OK, 105264 rows affected (11.28 sec)

Rows matched: 105264  Changed: 105264  Warnings: 0

 

mysql> update mytable set entrystatus=1 where userid=3 and
entrystatus=0;

Query OK, 105264 rows affected (11.50 sec)

Rows matched: 105264  Changed: 105264  Warnings: 0

 

mysql> update mytable set entrystatus=1 where userid=4 and
entrystatus=0;

Query OK, 105263 rows affected (11.30 sec)

Rows matched: 105263  Changed: 105263  Warnings: 0

 

 

MySql4.1.15 huge setup

----------------------

mysql>

mysql> update mytable set entrystatus=1 where userid=1 and
entrystatus=0;

Query OK, 105264 rows affected (10.72 sec)

Rows matched: 105264  Changed: 105264  Warnings: 0

 

mysql>

mysql> update mytable set entrystatus=1 where userid=2 and
entrystatus=0;

Query OK, 105264 rows affected (10.77 sec)

Rows matched: 105264  Changed: 105264  Warnings: 0

 

mysql> update mytable set entrystatus=1 where userid=3 and
entrystatus=0;

Query OK, 105264 rows affected (10.80 sec)

Rows matched: 105264  Changed: 105264  Warnings: 0

 

 

I would appreciate your response in this regard.

 

Ila.