Members Members   Search Search   Help Help   Register Register   Login Login   Home Home
Home » Performance » MySQL » Is there a performance penalty when column in fact table different size then dimension table?
Is there a performance penalty when column in fact table different size then dimension table? [message #1955] Thu, 27 September 2007 22:32 Go to next message
mikec is currently offline mikec
Messages: 22
Registered: September 2007
Junior Member
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
Report message to a moderator
Re: Is there a performance penalty when column in fact table different size then dimension table? [message #1958 is a reply to message #1955 ] Fri, 28 September 2007 08:41 Go to previous messageGo to next message
sterin is currently offline sterin
Messages: 324
Registered: March 2007
Location: Sweden
Senior Member
mikec wrote on Fri, 28 September 2007 04:32


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


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.
Report message to a moderator
Re: Is there a performance penalty when column in fact table different size then dimension table? [message #1965 is a reply to message #1958 ] Sat, 29 September 2007 12:41 Go to previous message
mikec is currently offline mikec
Messages: 22
Registered: September 2007
Junior Member
Thanks, I'll do that
Report message to a moderator
Previous Topic:Possible Hard Disk Alternative
Next Topic:MySQL Replication: Is it possible to ignore possible errors on slave?
Goto Forum:
  

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [ ]

Current Time: Fri Jul 10 00:10:32 EDT 2009

Total time taken to generate the page: 0.01260 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 2.7.7.
Copyright ©2001-2007 FUD Forum Bulletin Board Software

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.