Let the following table with about ~100 000 rows:
CREATE TABLE `product_code` (
`value` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
`type` varchar(16) COLLATE utf8_unicode_ci NOT NULL,
`product_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
I need to query this table very often, so I was thinking to put the result set (as array) in Redis or Memcached.
I did some math (utf-8 is 4 bytes not true, see @Daan comment, 4 bytes could be still a pessimistic guess):
value
: (1 * 4) + (64 * 4) = 388 bytestype
: (1 * 4) + (16 * 4) = 68 bytesproduct_id
: (1 * 4) + (32* 4) = 132 bytes
One row = 588 bytes at max, total should be 58800000 bytes = ~56MB + indexes size. Is this correct?
But it seems too much to me, in fact right now I have ~50 000 rows in that table and the following query shows 6864896 bytes = ~6.5 MB:
SELECT
DATA_LENGTH + INDEX_LENGTH AS size
FROM
information_schema.TABLES
where
TABLE_NAME = 'product_code'