How to figure out if mysql index fits entirely in

Question

Is there a way to determine whether a mysql index fits entirely in available memory? If so, how would I:

• Determine size of mysql indexes
• Determine available memory for the application
• Determine if the indexes fit entirely in memory

Answer 1

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.

Answer 2

To find memory available to MySQL, look in my.cnf, likely located at: /etc/mysql/my.cnf

key_buffer_size = 264M

To find size of indexes for a table: SHOW TABLE status FROM [DBNAME]