0

I'm looking at a db schema for a project I'm inheriting. There are many instances of binary answers being stored as INT(11) rather than TinyInt(1), which is the way I've normally handled this type or storage.

I've checked the data and everything is either "1" or "0". Is there any reason to or not to change the datatype to TinyInt(1) Unsigned for all of these instances?

Similarly, if something like "last_name" if the current column allows varchar(255), would switching to varchar(100) create any gains? I'm more interested in performance/efficiency than in just limiting data storage at this point.

Thanks, D.

Don
  • 1,570
  • 4
  • 22
  • 38

2 Answers2

1

I would say definitely go ahead with the changes to the boolean columns. (Note: Actually if you're using MySQL 5+, I would use the bit datatype instead of tinyint).

As far as the varchar columns, it doesn't actually make a difference changing 255 to 100 length.

From The SQL Docs:

A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.

So as long as its under 255, you're really not gaining much in terms of memory storage.

That being said, by limiting the size of the names, less data needs to be transferred between your SQL server and your application.

Swift
  • 13,118
  • 5
  • 56
  • 80
  • 1
    It makes no difference for the varchar columns **provided that they are not indexed**. – Hammerite Jun 09 '11 at 18:22
  • I would imagine this has more to do with transfer speed rather than memory allocation, correct? – Swift Jun 09 '11 at 18:27
  • I don't see anything in the question that indicates that transfer speed is the priority consideration. But besides that, it is my impression that keeping indices small yields many benefits (though yes, the principal benefits are in memory footprint). – Hammerite Jun 09 '11 at 20:01
  • I didn't mention it, but my main goal would be to improve performance for searches like "where active=1" and the active column is currently an INT(11) vs. Boolean or TinyInt(1). Memory would be a close second and storage a third for optimization goals. – Don Jun 09 '11 at 20:09
  • Definitely use bit then (an make it an index) for the active column. As far as the varchars, its up to your preference / usage needs. – Swift Jun 09 '11 at 20:43
0

Switching to TINYINT would save you 3 bytes I believe, which doesn't seem like a lot to me, although it's certainly a little more efficient.

I always try and make VARCHAR columns as small as I can get away with. I would personally focus on any gains you can get from that.

The main reason I can think of to avoid any of these changes is if you have so much data that running an ALTER TABLE would cause significant downtime.

Whether any of this will help your app perform better is open to debate. In theory, with VARCHARs, MySQL will only send the actual data over the wire, so if all your last names are 40 bytes long, it's only sending 40 bytes. If the column isn't being used in lookups, it shouldn't really have any impact on your perfomance. There's a couple relevant questions like this one on SO covering this issue already.

Community
  • 1
  • 1
muffinista
  • 6,676
  • 2
  • 30
  • 23
  • For sure, it's a lot if you need to transfer a million rows over the wire, but not much on a per-record basis. – muffinista Jun 09 '11 at 18:28
  • Even on a hard drive. 3 bytes per row adds up to 3MB/3GB if you've a million/billion rows... I realize storage is cheap, but it's not a reason for storing a boolean in anything other than a `bit`. – Denis de Bernardy Jun 09 '11 at 18:30