I am using the show table status command to find the average length of row in a table. And it reported something about 686 bytes. But as I populated more data, this number has substantially decreased to 484 bytes. But one more thing I also learnt is the average row length returned by oracle is just the avg length of each row in the data files ignoring the space occupied by the indexes. But I think MySQL is giving this values taking the space used by indexes into consideration. So they are almost coming the same. :)
sujay -----Original Message----- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 27, 2005 3:59 AM To: Sujay Koduri Cc: mysql@lists.mysql.com Subject: Re: Avg row length is varying a lot from oracle to MySQL Sujay Koduri wrote: > we are converting our oracle DB to MySQL DB. One problem i see is that > the abg row length in MySQL is much higher compared to that of Oracle. > In oracle it is around 180 bytes and in MySQL it is around 686 bytes. > So as a result, MySQL is taking more space to store the same number of > records. Can someone please explain me if this is the intended > behaviour or i am missing out something. I am also including the o/p > of desc table_name of the same table on both the databases. How are you measuring the size of a row in mysql? What makes you think it is averaging 686 bytes? Sujay Koduri also wrote: > Each row in the table takes around 600 bytes, taking every thing into > consideration and assuming every field is used to its maximum bytes. > But the major portion of this 600 bytes are composed of varchar's (100 > + 150 + 50 + > 16 + 50 + 20 + 9..) > Out of these 400 bytes we generally use only 40 to 50 bytes. Most of > them are reserved for future uses. So strictly speaking even including > the space taken by the indexes, the avg length should not come more than 250 bytes. Umm, using about 50 bytes out of 400 in variable length columns saves about 350 bytes. 600 - 350 = 250, so you should expect about 250 bytes used _before_ indexes. On the other hand, you say it's only 180 in Oracle, so perhaps the estimate is off. OK, looking at your column definitions, I see 118 bytes worth of fixed-width columns, plus 11 to 436 bytes worth of varchar columns, yielding 129 bytes per row with empty varchars, 554 bytes per row with full varchars. With 40 to 50 chars used in the varchars, that would be around 180 bytes per row, just as in Oracle (not including any indexes). Of course, this is assuming you are using 1-byte chars. I can't imagine how that could take 686 bytes per row in mysql. It could just be a failure of my imagination, but you haven't yet shown us how you arrived at that number. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
