I'm building a web portal that has a lot of tables and data is mostly id's (integers) in range 0 -> max, and strings of chars (VARCHARs) and fixed char(1) like 'y' and 'n' s mostly.
I switched (still in my testing) recently to InnoDB from MyISAM because of row-lock (and not table-lock) mode of InnoDB that I found eversince I started to pay more attention to database administration / work / testing / workaround from intensive PHP thinking.
My question is, should I use compression on these tables I have ? My prediction, a lucky one of course, but realistic since it might happen, is that most of my tables will reach at one point 1 million rows and then maybe more, even 10 mil or 20 for some other tables.
Would in that case of reading, INNER JOIN, writing, etc, be good to have compressed data for my tables ? I know they might do a great job while being compressed as the space on the drive (since InnoDB uses pretty much more space than MyISAM) but then, would the CPU handle all the requests for sorting out, querying and decompressing at the same time ?
Also would the RAM be enough for such operations ?
1st option of hosting I will use when launching my portal would be 2 x 100 GB hdd RAID mirroring, 2GB ram, 3Ghz CPU then I will increase to 2 x 250 GB, +1 more GB of ram and 3rd option will be a great 1 x Quad Xeon E5420 4 x 2.5 Ghz, 8/16 Gb Ram, 2 x 73 Gb 10k rpm SAS hdd + 4 x 500 GB 7200 SATA HDD as secondary storage.
Thanks for your oppinions,
