Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

Is there a performance penalty when column in fact table different size then dimension table?

mikecmikec ContributorInactive User Role Beginner
Have several very large fact tables (100+ million rows).

In doing some maintainence on one of these fact tables, I can
squish the size of several of my dimension keys from int to smallint.

Don't want to touch the other fact tables at this point in time.

Is there a performance penalty to having a dimension key in the fact table be
of size smallint but still be of size int in the dimension table when a join
is done?

Thanks,

Mike

Comments

  • sterinsterin Mentor Inactive User Role Contributor
    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">mikec wrote on Fri, 28 September 2007 04:32</td></tr><tr><td class="quote">

    Is there a performance penalty to having a dimension key in the fact table be
    of size smallint but still be of size int in the dimension table when a join

    </td></tr></table>
    Generally yes, because you are forcing MySQL to perform an implicit data conversion on all records involved in the join.
    The question in this case is if the extra CPU cycles outweigh the smaller table size and resulting less reads from disk.

    My guestimation is that it doesn't so I would stick with same type on both columns involved with the join.
  • mikecmikec Contributor Inactive User Role Beginner
    Thanks, I'll do that
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.