Re: MySQL 4.1.1 Performance

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                                                                                                 
                      <[email protected]>         To:       Brian Wintz <[email protected]>                                                     
                                               cc:       [email protected]                                                          
                      02/24/2004 03:29         Subject:  Re: MySQL 4.1.1 Performance                                                   
                      PM                                                                                                               
                                                                                                                                       
                                                                                                                                       



----- Message from Sergei Golubchik <[email protected]> on Wed, 25 Feb 2004
00:29:17 +0100 -----
                                   
      To: Brian Wintz              
          <[email protected]>            
                                   
      cc: [email protected]     
                                   
 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 <[email protected]>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
       <___/  www.mysql.com

-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]