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]
