How to figure out if mysql index fits entirely in memory

by

Depends on Storage Engine

MyISAM (Caches Index Pages From .MYI files)

SELECT FLOOR(SUM(index_length)/POWER(1024,2)) IndexSizesMB
FROM information_schema.tables WHERE engine="MyISAM" AND
table_schema NOT IN ('information_schema','performance_schema','mysql');

Subtract that from key_buffer_size. If the answer > 0, then Yes

InnoDB (Caches Data and Index Pages)

SELECT FLOOR(SUM(data_length+index_length)/POWER(1024,2)) InnoDBSizeMB
FROM information_schema.tables WHERE engine="InnoDB";

Subtract that from innodb_buffer_pool_size. If the answer > 0, then Yes

I wrote about this in the DBA StackExchange

On a dedicated DB Server, make sure InnoDBSizeMB+IndexSizesMB does not exceed 75% of RAM.

Leave a Comment

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)