CODEWITHSUNDEEP
mysqloptimizationtypes
Don
Don

Reputation: 1570

MySQL Storage and Optimization

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.

Upvotes: 0

Views: 51

Answers (2)

Swift
Swift

Reputation: 13188

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.

Upvotes: 1

muffinista
muffinista

Reputation: 6736

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.

Upvotes: 0

Related Questions