Memory occupied by a quite large array MySQL result set in PHP?

155 views Asked by At

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 bytes
  • type: (1 * 4) + (16 * 4) = 68 bytes
  • product_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'
0

There are 0 answers