> 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. >
If you have a varchar(50) but usually only use 10 in those fields MySQL still counts the unused 40 for the total byte count of the row, so you must count them. An empty varchar(50) field still uses 50 bytes. Also, I believe text and blob fields are always counted as 255 bytes regardless of your settings. Ordered indexes are 10bytes per column (in the index) per row. I believe there is also some paging overhead so generally take your calculated row size and multiply by 1.1. > sujay > > -----Original Message----- > From: Jeff [mailto:[EMAIL PROTECTED] > Sent: Friday, September 23, 2005 1:12 AM > To: mysql@lists.mysql.com > Subject: RE: Avg row length is varying a lot from oracle to MySQL > > > -----Original Message----- > > From: Sujay Koduri [mailto:[EMAIL PROTECTED] > > Sent: Thursday, September 22, 2005 15:23 > > To: mysql@lists.mysql.com > > Subject: Avg row length is varying a lot from oracle to MySQL > > > > > > hi ,, > > > > 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. > > Probably do to the way the two database store data and how > much space they reserve for specific column types. > > For a way to calculate row size see this link. Relize you > must also calculate the size of all indexes. > http://dev.mysql.com/doc/mysql/en/storage-requirements.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
