SELECT f.*,IFNULL(in_buffer,'OFF') AS in_buffer FROM
(SELECT b.TABLE_CATALOG,b.TABLE_SCHEMA, b.TABLE_NAME,ENGINE,b.INDEX_NAME
FROM `information_schema`.`TABLES` a
JOIN `information_schema`.`STATISTICS` b USING(TABLE_CATALOG,TABLE_SCHEMA, TABLE_NAME)
WHERE ENGINE = 'InnoDB'
GROUP BY 1,2,3,4,5) AS f
LEFT JOIN (SELECT TABLE_NAME , INDEX_NAME, IF(COUNT(*)>0,'ON','OFF') AS 'in_buffer' FROM `information_schema`.`INNODB_BUFFER_PAGE` WHERE PAGE_TYPE = 'INDEX' GROUP BY 1,2) AS c
ON(c.TABLE_NAME = CONCAT('`',f.TABLE_SCHEMA,'`.`',f.TABLE_NAME,'`') AND c.INDEX_NAME = f.INDEX_NAME)
ORDER BY 1,2,3,4,5
;
MyISAM 은 방법 없는 듯
