Re: MySQL 4.1.1 Performance - Brian Wintz - com.mysql.lists.bugs

2 messages in com.mysql.lists.bugsRe: MySQL 4.1.1 Performance

From	Sent On	Attachments
Sergei Golubchik	24 Feb 2004 15:28
Brian Wintz	24 Feb 2004 16:13

Subject:	Re: MySQL 4.1.1 Performance
From:	Brian Wintz (bk...@qad.com)
Date:	02/24/2004 04:13:52 PM
List:	com.mysql.lists.bugs

Sergei,

Attached below is a tables.sql file containing the CREATE TABLE statements for each of the nine tables represented in the problematic query. If you need representative data please let me know. The alter_table.sql file contains the ALTER TABLE statements that I ran against the database to shrink the column sizes down in order to make the query performant. The problematic query is contained in the attached query.sql file (note that nested ${...} values get replaced at runtime).

Please let me know if you find out anything.

-Brian (See attached file: tables.sql)(See attached file: query.sql)(See attached file: alter_table.sql)

Sergei Golubchik <se...@mysql.com> To: Brian Wintz
<bk...@qad.com> cc: bu...@lists.mysql.com 02/24/2004 03:29 Subject: Re: MySQL 4.1.1
Performance PM

----- Message from Sergei Golubchik <se...@mysql.com> on Wed, 25 Feb 2004 00:29:17 +0100 -----

To: Brian Wintz <bk...@qad.com>

cc: bu...@lists.mysql.com

Subject: Re: MySQL 4.1.1 Performance

Hi!

On Feb 20, Brian Wintz wrote:

Sergei,

Thank you for your response. I wanted to provide some additional information. We create our MySQL schema based on a UML representation of our system. Since UML is ignorant of SQL data types it is necessary for us to map all string values to a default SQL data type - to be on the safe side we use VARCHAR(128). I've performed ALTER TABLE statements against my unicode 4.1 database to change these default VARCHAR(128) columns to reflect the actual usage. The result is the time to perform the problematic query (a join between 12 tables) went from 11 seconds to 0.2 second.

I believe this will be a reasonable solution to my problem; however, I'm still a little curious why a 4.0 database and 4.1 utf8 database with the same schema and data would perform differently? Clearly the unicode database needs to do more work, but I was surprised at the magnitude (almost 100 times slower). If you feel it would still be helpful I am willing to create a sample database and query to illustrate the problem - please let me know the most convenient way to submit this.

Could show first the original and new table structure ? The one reason I can think of is that 128 characters in latin1 is 128 bytes, while 128 characters in utf8 is 384 bytes, and VARCHAR(384) is automatically converted to TEXT. But I doubt it could explain 10-fold difference. (if it is really the reason - then it should be a bug)

Regards, Sergei