Re: MySQL 4.1.1 Performance

--0__=07BBE4D6DF9317088f9e8a93df938690918c07BBE4D6DF931708
Content-type: text/plain; charset=us-ascii


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
To: Brian Wintz
cc: bugs@lists.mysql.com
02/24/2004 03:29 Subject: Re: MySQL 4.1.1 Performance
PM





----- Message from Sergei Golubchik on Wed, 25 Feb 2004
00:29:17 +0100 -----

To: Brian Wintz


cc: bugs@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

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/ www.mysql.com




--0__=07BBE4D6DF9317088f9e8a93df938690918c07BBE4D6DF931708
Content-Type: text/plain; charset=us-ascii

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org
--0__=07BBE4D6DF9317088f9e8a93df938690918c07BBE4D6DF931708--