I'm learning how to optimize my database by re choosing the correct data types for the columns and I want to know how much size I will save if I choose MEDIUMINT (3 Bytes) instead of INT(4 Bytes)
AFAIK -and correct me if I'm wrong- I need the database size to be as small as possible to fit in RAM to reduce the hard-desk requests. The size of the database consists of the tables sizes + index sizes
giving that I have an INT column that has 10'000'000 rows and a B-Tree index on it, how much size In MBs I will save if I changed the datatype of the column from INT to MEDIUMINT at
note: I know MySQL will not reduce the actual size on disk unless I OPTIMIZE TABLE
EDIT: My situation is that I will finish my first serious system in my life shortly -it's an ERP system that I plan to sell in the Arab region market - . Plans 1, 2, 3, 4 databases are supposed to be about 2GB, 4GB, 10GB, 40GB respectively, so If I could reduce the size of each database without sacrificing performance/features, why not ? If I could make a 32GB RAM machine serve 4 clients instead of 2, why not ?
Just use INT unless you have a specific, measurable problem. You're only going to make a mess of things if you fret over every single byte in an era where even the most thrifty of smart phones has a billion of them for memory alone.
I need the database size to be as small as possible to fit in RAM to reduce the hard-desk requests.
No you don't. You need the database to be easy to work with and perform adequately. In an era of SSD-backed databases, I/O will not be a problem until you're operating at large scale, and when and if that day comes then you can take measurements and understand the specific problems you're having.
Shaving a single byte off your INT field is unlikely to make anything better since three byte integer values are not something your CPU can directly deal with. These will be converted to four bytes and aligned properly so they can be understood, a process that's messy compared to reading a plain old 32-bit integer.
Remember, MySQL comes from an era where a high-end server had 64 megabytes of memory and a 9 gigabyte hard disk was considered huge. Back then you did have to shave bytes off because you only had a handful of them.
Now we have other concerns, like will you accidentally exhaust your 24-bit integer space like Slashdot did where their site went down because of exactly the sort of "optimizing" you're intending to do here.
Be careful. Optimize when you have a concrete reason to, not just because you think you need to. Avoiding premature optimization is a constant struggle in development, but if you're disciplined you can avoid it.