The amount of a positive change does using tinyint or smallint (when relevant) rather than just int do? Or restricting a char area towards the minimum figures needed?

Do these options affect performance or simply allotted space?

With an Indexed area having a considerably large table how big your area can produce a large impact on performance. On the nonindexed area it is not as important bit still it needs to write the additional data.

Nevertheless, the down time of the re-size of a big table could be several minutes or several hrs even, so don't make sure they are more compact than you'd imagine ever requiring.

Yes it affects performance too.

When the indexes are bigger, it requires longer to see them from disk, and fewer could be cached in memory.

I have frequently seen these three schema design defects leading to problems:

  1. A varchar(n) area was produced with n only large enough for that sample of information the designer had drawn in, not the worldwide population: fine in unit tests, quiet truncations within the real life.
  2. A varchar(n) used in which the information is fixed size. This masks data bugs.
  3. A char(n) employed for variable length data. This gives performance enhancements (by enabling the information to sit down in-line within the row on disc, but all of the client code (as well as other saved procs/sights etc) need to handle whitespace padding issues (and frequently they do not). Whitespace padding can be challenging to find, because spaces do not show up too well, as well as other libraries/sql clients suppress them.

I have never witnessed a highly intentioned (i.e. not only using varchar(255) for those cols) but conservative choice of the incorrect data size result in a significant performance problems. By significant, I am talking about factor of 10. I regularly see algorithmic design defects (missing indexes, delivering an excessive amount of data within the wire etc.) leading to larger performance hits.

Both, in some instances. But imo, it's much more of an issue of design than performance and storage factors. The main reason you do not make everything varchar(...) happens because that does not precisely reflect what kind of data ought to be saved there, also it reduces your data's integrity and kind-safety.